Query that changes criteria based on current date

[RogerC]

I have a query that includes a Date field. The criteria for 'Date' is a range of 28 days (for example 'Between 7/27/2003 And 8/23/2003'). Periodically, I need the Date's criteria to automatically change based on the current date. For example; if today's date is within 7/27/2003 and 8/23/2003 I want the 'Date' field to use the criteria 'Between 7/27/2003 and 8/23/2003'. On 8/24/2003 I need the criteria to automatically change to 'Between 8/24/2003 and 9/20/2003'. On 9/21/2003 it would change again, etc. Can this be done in Access?

 

[SydneyGeek]

You can use the Date() function to achieve this. But watch out with your field naming -- calling a field Date can confuse Access because it may think you are referring to its internal function -- best to call it SalesDate or something appropriate. Anyway...
In the criteria line for your field, put this:

Between Date()-28 And Date()

That will give you the last 4 weeks' data, updating as the date changes.

HTH
Denis

 

[bat17]

If you want what I think you want then try this for your criteria.

>Date()-(((Date()/28)-Int(Date()/28))*28) And <Date()-(((Date()/28)-Int(Date()/28))*28)+29

Not very elegant but it should work

 

[RogerC]

Oops. I'm afraid I wasn't clear enough in describing my problem. 28 days was just an example of the time period for one partictular month. The length of any particular time period varies. The next month may be 35 days, etc. These repesent the the 'financial closing calendar' for our company. I have a list of the start and stop dates for each month. Can I build the start and stop dates for all 12 months into a formula for the Date criteria?

Thanks for the tip SydneyGeek. I will change the name of the Date field to MonthCloseDate.

 

[bat17]

You will probably be best building a UDF to check the dates. it will be easier to maintain than trying to manage a large iif statement.



Peter

 

[RogerC]

thanks bat17. Unfortunately, I'm new to Access and wouldn't know where to begin with a UDF. Can you give me any ideas on how to get started?

 

[bat17]

Paste this code into a new module and save the module with any name that is not the same as the functions (DateFilter).

Function DateFilter(fStart As Boolean) As Date
'True will return Start Date, False will return End Date
Dim StartDate As Date
Dim endDate As Date

Select Case DATE
Case #1/1/2003# To #1/31/2003#
   StartDate = #1/1/2003#
   endDate = #1/31/2003#
Case #2/1/2003# To #2/28/2003#
   StartDate = #2/1/2003#
   endDate = #2/28/2003#
Case #3/1/2003# To #3/31/2003#
   StartDate = #3/1/2003#
   endDate = #3/31/2003#
Case #4/1/2003# To #4/30/2003#
   StartDate = #4/1/2003#
   endDate = #4/30/2003#
Case #5/1/2003# To #5/31/2003#
   StartDate = #5/1/2003#
   endDate = #5/31/2003#
Case #6/1/2003# To #6/30/2003#
   StartDate = #6/1/2003#
   endDate = #6/30/2003#
Case #7/1/2003# To #7/31/2003#
   StartDate = #7/1/2003#
   endDate = #7/31/2003#
Case #8/1/2003# To #7/31/2003#
   StartDate = #8/1/2003#
   endDate = #7/31/2003#
Case #9/1/2003# To #9/30/2003#
   StartDate = #9/1/2003#
   endDate = #9/30/2003#
Case #10/1/2003# To #10/31/2003#
   StartDate = #10/1/2003#
   endDate = #10/31/2003#
Case #11/1/2003# To #11/30/2003#
   StartDate = #11/1/2003#
   endDate = #11/30/2003#
Case #12/1/2003# To #12/31/2003#
   StartDate = #12/1/2003#
   endDate = #12/31/2003#
End Select
If fStart = True Then
   DateFilter = StartDate
Else
   DateFilter = endDate
End If
End Function

In the criteria for the date field in the query put

>=DateFilter(True) And <=DateFilter(False)

I have set it up to filter against the months of the year but you can change the dates to suit your needs. I have not added any error trapping for things like invalid dates!

HTH

Peter

 

[RogerC]

bat17 - That works perfectly! ...and now I know how to create a UDF! Thanks for your help!