Dates

[bob bisquick]

I am having a problem that has been addressed here before, but I am looking for a more elegent solution. I have a Query that generates a list of dated events. I want to limit that list to events falling between certain dates, NOT USING THE [ENTER START DATE] method. I have a nice form where the user enters the start and end dates which go into a table. Now I want to use that table to limit my query. I think I am simply missing some syntax issue. I have been persuing some sort of greater than, less than expression with no luck.

 

[Autoeng]

Bob:

I think that this will do what you want. It uses a form with Start and Stop Date fields. Put a command button on the form that starts and Event procedure to run the macro and as the last step closes the form. That way you don't have to pass the date to a table. This query assumes that the dates are in the same table and is a 5 day work week. It does include holidays. It looks at all records that the stop date falls in between the start and stop date you enter.

SELECT tblName.[Start Date], tblName.[Stop Date], DateDiff("d",[tblName].[Start Date],[ltblName].[Stop Date],2) AS DaysDiff, Int([daysdiff]/7) AS Weeks, [daysdiff]-[weeks]*2+IIf(Weekday([tblName].[Stop Date],2)>5,5-Weekday([ltblName].[Stop Date],2),0)+IIf(Weekday([tblName].[Start Date],2)=6,1,0) )-IIf(Weekday([tblName].[Start Date],2)>Weekday([tblName].[Stop Date],2),2,0)+IIf([tblName].[Start Date]=[tblName].[Stop Date],1,0) AS weekdays
WHERE (((tblName.[Stop Date]) Between [Forms]![frmDates]![Start Date] And [Forms]![frmDates]![Stop Date])));

Updated this (11/11) as left out a line of code and added so that if start and stop are same day then returns 1 instead of 0.

Autoeng