Dates in Text fields

[Guest]

Hi all,
I am date stamping records in access with month and year eg JAN05 to denote
which reporting period a record belongs to. I have an unbound text box the
user enters this value in to update to all chosen records, the default value
of this is
=Format(Date(),"mmmyy")
The user has now requested it defaults to the month before the current
month. Since this is not a real date field I can not put -1 in the
calculation to achieve the month previous, can anyone tell me a way round
this please?

Thanks in advance for any help.
Sue

 

[Guest]

=Format(DateAdd("m",-1,Date),"mmmyy")

should do the trick.

 

[Guest]

Thanks worked a treat
Sue

 

[Guest]

One more thing sorry... in a report how would I sort this text in month order
as opposed to alphabetically with it being a text field (storing JAN05 etc)?

Thanks in advance for any help.
Sue

 

[Rick Brandt]

One more thing sorry... in a report how would I sort this text in
month order as opposed to alphabetically with it being a text field
(storing JAN05 etc)?

You want a formatted date then you get a string sort. Leave it as an actual
date and you get a date sort. You'll need to include both so you can
display one and sort on the other.

 

[Guest]

Not sure what you meant by that... but I found a similar posting earlier that
you answered. I have created a new field in the query ReportingDate:
=Format(CDate(Left([C/B Report_Date],3) & "-01-20" & Right([C/B
Report_Date],2)),"yyyy-mm")

This sorted it thanks

Sue