Subtract Date

[sandylt]

I am trying to open up a form depending on the expiration date. I was opening expired record and due date that was [expiration] <date(). What I need to do now is give the expiration a month early. Ex: If the due date is 07/01/04 then I want the record to show on 06/01/04 to prompt payment one month in advance. Like [expiration]<date() -1 . I have searched, but the methods were not working out in the command line. Any takers???

 

[RichO]

Use the DateAdd function. For example:

DateAdd("m", -1, Date())

will give you 5-14-2004 (today being 6-14-2004)

 

[sandylt]

I dont know where to put that. I have two textbox. They are both controlled by expiration. One shows the expiration date and I want the other to show the expiration date minus one month. I found a whole bunch of different examples, but I dont know where to put the code in the textbox.

 

[RichO]

In the Control Source for the 2nd textbox (the one you want to hold the expiration date minus 1 month) enter:

=DateAdd("m", -1, [ExpirationDate])

...you'll need to substitute the actual name of the your expiration date field in the [brackets].

 

[sandylt]

that works great. Is there anyway to run that in a query?

 

[RichO]

You can do this in a query. It all depends on how you want to set it up. For instance if you want to show all expiration dates that are less than a month away you can set a criteria field to:

=DateAdd("m", -1, [ExpirationDate])

and then in the criteria for that field:

<date()

 

[sandylt]

Rich I appreciate all your help, but I cant seem to quite get what I am trying to do just right. The textbox idea worked, but I cant filter the form to show me just the records that I want. The query worked, but it wont respond the way I need it to. I have the [expiration] box then the text box that performs the calcualtion =DateAdd("m", -1, [Expiration]). Based on that calucation [noticedate] I need filter to show the records where the calculation is <date(). I tried to write the code open form where [noticedate]<date() but the form comes up blank. I tried to write code for the OnOpen event, but I keep coming up short.

 

[RichO]

If you want to filter your form based on this, put this code in the On Load event:

Me.Filter = "DateAdd('m', -1 , [Expiration]) < Date()"
Me.FilterOn = True
Me.Requery

If you want to exclude all records that are beyond their expiration date then the first line should be:

Me.Filter = "DateAdd('m', -1 , [Expiration]) < Date() and [Expiration] >= Date()"