date criteria

[swmorin]

I have a query that has two date fields start date, and end date.

I need to write criteria to see if either of the dates, and any day in between these dates are part of the current week/month

 

[John Big Booty]

Use the following Criteria;

Between [Forms]![YourFormName]![StartDate] And [Forms]![YourFormName]![EndDate]

If you have a form that hold the start and end date criteria.
IF you wish to prompt the user to input start and end dates use

Between [Start Date] And [End Date]

 

[swmorin]

I think I might have misexplained or I misunderstand your answer.

What I need to do is see if any of the days involved. I.E. start date, end date and every day in between are part of the current week/month/quarter

 

[John Big Booty]

Have a look at the DatePart() function, that should do what you are after.

 

[swmorin]

I was looking at the datepart(). And it is fairly simple for me to evaluate whether or not the start date or the end date are part of the current week, but what about the days inbetween those days.

 

[pr2-eugin]

Give us a snippet of your code that you have implemented. If you have used the Between keyword it looks the [StartDate] and [EndDate] and all dates inclusive of the two. Hope it makes sense.

 

[bob fitz]

swmorin
If you cut and paste the code below, to the end of your query’s SQL statement, I think it will give the result that you require.

WHERE (((IIf(DateAdd("d",-(Weekday(Date(),1)-1),Date()) Between [start date] And [end date],"Yes",IIf(DateAdd("d",(Weekday(Date(),1)+1),Date()) Between [start date] And [end date],"Yes","No")))="Yes")) OR (((IIf(Month([start date])=Month(Date()),"Yes",IIf(Month([end date])=Month(Date()),"Yes","No")))="Yes"));

The code above was adapted from some code written by Thomas M. Brittel in 1998 within a database (Access 97 file) that has examples of date functions. I can’t remember how I came by it, but have attached it in a zip file in case you or anyone else that is following this thread has an interest in viewing it.

BTW: It would be better not to have spaces in field names. (e.g. “StartDate” instead of “start date”.)