Pick Dates Based on Day of week

[Matt]

Is there a way to pick a date range for a query based on the day of the
week. I am able to do this for one day, but not a range.

Example:

IIf(Format([forms]![Fill Daily
Data]![FromDate],"dddd")="Monday",[forms]![Fill Daily Data]![FromDate])

This works for me (I know there does not really need to be an IIF
statement for this, I am using it for an example). But when I try
something like the following I get no results:

IIf(Format([forms]![Fill Daily
Data]![FromDate],"dddd")="Monday",Between ([forms]![Fill Daily
Data]![FromDate]-2) and [forms]![Fill Daily Data]![FromDate])

As you can see, on Mondays I must also pull in The weekend data ...
this will eventually have an else to only pick up today's data for any
day buy Monday, but the problem is this second statement returns 0
rows, where as the first one returns the correct data.

Does anyone know how I could do this (without changing the SQL via VBA
..... I want one SQL statement that is smart enough to recognize when it
must pull in multiple days ... making the statement smarter than me!)

 

[Douglas J. Steele]

Try this:

Between IIf(Weekday([forms]![Fill Daily Data]![FromDate]) = 2,
([forms]![Fill Daily Data]![FromDate]-2), [forms]![Fill Daily
Data]![FromDate]) And [forms]![Fill Daily Data]![FromDate])