Display Date on form 6 months ago

[Lloyd]

If I key in to the control source of a text box "=Date()-184" I will get the date 6 months ago but if I tried the same of 06/10/05 I will lose a day because of the 30/31 day months. Does anybody know another way around this?

 

[Brianwarnock]

And what result do you want on 31/10/2005 there is no 31/4/2005, what you got was a pure accident, try 01/07/2005. What are you trying to do?

 

[Lloyd]

I am trying to pull back the corresponding date 6 months ago i.e 6th March from today or 6th April when its 6th October. I see what you mean though, any ideas?

 

[Brianwarnock]

Ok if you are not going to run into end of month problems eg its always the 6th of the month then
=DateSerial(Year(Date()),Month(Date()-6),Day(Date()))

will give you what you want

Brian

 

[Bat17]

use the DateAdd function
DateAdd("m",-6,[Date - Time])

But this will give you some rounding to allow for for variation in month lengths.
so 28/29/30/31 Aug will all give you 28 Feb

HTH

Peter

 

[Lloyd]

No I am going to have this rolling forward and queries are going to be referenced to the text box so I can run reports. Bascially I just need a formula that can just stay there so that reports can be run without changing anything.

 

[Brianwarnock]

Sorry typing error
=DateSerial(Year(Date()),Month(Date())-6,Day(Date()))

but BAT17 gives you a better solution.

Brian