Conditional report-dependant on Day of week.... 1

[herbal]

I have a report that requires the user to enter the date of a membership meeting. As it is currently, the report takes the date entered and subtracts 3 days and uses the two dates for the range.

I'd like the report to make it subtract 3 days only if the date entered by the user is a Monday, and to subtract 2 days if the day of the week is Wednesday or Friday. If the date entered is any other day of the week an error message stating that would be nice, but is not a requirement.

The SQL for the query looks like this:

SELECT 'Expired' As [Type], ExpiredMembers.FirstName, ExpiredMembers.MiddleName, ExpiredMembers.LastName, ExpiredMembers.DateUpdated, ExpiredMembers.MemberID, #1/1/1900# As DateJoined, #1/1/1900# AS DateExpired, ExpiredMembers.DL AS [TDL\DL]
FROM ExpiredMembers
WHERE (((ExpiredMembers.DateUpdated)>=DateAdd("d", -3, [Retrieve members since what date?]) And (ExpiredMembers.DateUpdated)<= [Retrieve members since what date?]))
UNION SELECT 'Added' AS [Type], Members.FirstName, Members.MiddleName, Members.LastName, NULL As DateUpdate, Members.MemberID, Members.DateJoined, Members.DateExpired, Members.TDL AS [TDL\DL]
FROM Members
WHERE (((Members.DateJoined)>=DateAdd("d", -3, [Retrieve members since what date?]) And (Members.DateJoined)<=[Retrieve members since what date?]))
ORDER BY Type DESC , MemberID;

Thanks,
Herbal

 

[scriverb]

You must determine what date to be used when not Monday, Wednesday, or Friday. Can't just leave it hanging.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[herbal]

I'm having a difficult time understanding what you said. If the date that the user enters does not fall on a Monday, Wednesday, or Friday then I don't want the report to run.

Basically, since I don't need it to run then I guess you could use any date.

The important part is making the query programatically change the date range to 3 days if the date entered falls on Monday, and only 2 days if the date entered falls on Wednesday or Friday.

Thanks,
Herbal

 

[scriverb]

That's okay. If not on those three days then cancel the report. This will require a modification to the WHERE clause statement and then some code in your report to not run if there are no records in the supplied recordset:

WHERE (((Members.DateJoined)>=[red]IIF(DatePart("w",[Retrieve members since what date?])=2,DateAdd("d", -3, [Retrieve members since what date?]), IIF(DatePart("w",[Retrieve members since what date?])=4 or DatePart("w",[Retrieve members since what date?])=6,DateAdd("d", -2, [Retrieve members since what date?]), #12/31/2099#))[/red]  And (Members.DateJoined)<=[blue]IIF(DatePart("w",[Retrieve members since what date?])=2,DateAdd("d", -3, [Retrieve members since what date?]), IIF(DatePart("w",[Retrieve members since what date?])=4 or DatePart("w",[Retrieve members since what date?])=6,DateAdd("d", -2, [Retrieve members since what date?]), #12/31/2099#))[/blue]))

Since I cannot test this I have color coded the two halves of this expression to help you understand what I am doing. Nest we want to use the reports event procedure On NoData.

MsgBox "No Data for report"
DoCmd.CancelEvent

Post back with questions.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[herbal]

Its asking me to input the value of Members.DateJoined. Any ideas?

 

[scriverb]

Okay, I am sorry but I didn't really dig deep into your other than grab the last WHERE clause. There are two WHERE clauses and both need to use the same technique as this one. Is the field Members.DateJoined legitimate or is it misspelled? First of all let's try splitting the UNION Selects into individual selects. Just use the first one and let's get that one to work. Modify the WHERE statement as I provided and just change the tables and field name in the where statement.

Or, you can just work with the second one first to get the where statement working and then transfer it to the first one within the UNION query.

Try this: Members.[DateJoined] and see if that helps. If it is a legitimate field then it should work as you had it. I can't really say from this as to why it isn't recognizing it.

Post back with results.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[herbal]

Actually Bob, I went ahead and created a new query before my last post and separated the two select statements. Thats when the problem occured.

I tried [Members.DateJoined] and got invalid bracketing error, and I just tried your suggestion(Members.[DateJoined]), and it still asked for the value of Members.DateJoined.

 

[herbal]

Oh...the name is legit BTW.

 

[herbal]

Nevermind, I got the first part working....was pulling from wrong table.

I know....I'm retarded!

 

[herbal]

Sweet! Looks like I got them both working. Thanks for your assistance! It is much appreciated!

Herbal

 

[herbal]

I have just one more question...its not really technical...I'm just a bit braindead this morning...

If I subtract 3 days from the date entered....its a 3 day range or 4?

 

[herbal]

Something is a little screwy.

First I noticed the darn thing skipped leap day(odd).

Second, if I enter 3/1/04 as the date, the query doesn't return any member added on 3/1/04 of which there are 4.

It seems as if the date range isn't there, but rather its just selecting the one day 2 or 3 days prior....

I tried to remove the conditional statements on the latter part of the selects, but got errors.

Perhaps this was an oversight?

 

[herbal]

nevermind...I think I corrected it.

 

[scriverb]

Sorry, I was away from my office for a while. Yes, the logic of your WHERE is a little strange. You are going to be looking for a datejoined that <= and >= a single date. You really don't have a date range.

Why don't you tell me just exactly what you want to select. Not in an equation but just describe what you want to use a selection criteria.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[herbal]

Bob, I got it fixed on my own. I just had to remove the dateadd portion of the where clause after the AND so that it was just [Retrieve members since what date?]. Now it works flawlessly! Thanks a bunch!

 

[scriverb]

That makes sense. Now you have a date range to compare to. I am glad to have been able to assist you with this problem. Thanks for the star as it is appreciated.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]