Date format

  • Thread starter Thread starter Rob
  • Start date Start date
R

Rob

I have a field on a form asking for a date ([Date]) and Im
trying to find a way to make a hidden field ([Previous])
automatically contain the first date of the previous
month. For Example, if [Date]="8/1/03", then [Previous]
= "7/1/03", what is the calculation for that?

Secondly, when they display on a report, I want it to be
formatted "mm/yy" but still maintain calendar order.

Any help would be greatly appreciated.
 
I have a field on a form asking for a date ([Date]) and Im
trying to find a way to make a hidden field ([Previous])
automatically contain the first date of the previous
month. For Example, if [Date]="8/1/03", then [Previous]
= "7/1/03", what is the calculation for that?
Click to expand...

DateSerial(Year([Date]), Month([Date]) - 1, 1)

and do reconsider using the word Date as a fieldname - it's a reserved
word for the Date() function, and Access will very likely get
confused.
Secondly, when they display on a report, I want it to be
formatted "mm/yy" but still maintain calendar order.
Click to expand...

Bind a textbox to the date field; set its Format property to "mm/dd";
and use the date field itself in the Report's sorting and grouping
dialog.
 
Me.[Previous] = DateSerial(Year(CDate(Me.[CurrDate])),
Month(CDate(Me.[CurrDate])) - 1, 1)

Note that I've renamed your field [Date]: Date is a reserved word, and you
should never name anything Date.

(And yes, that will work even in January.)
 
I tried both suggestions and get the same results. When I
use "8/1/03" as the current date, it returns "7/27/03".
What am I doing wrong?

By the way, I was using [Date] only for an example, that
was not the real name of my field.
Click to expand...

Your use of quotemarks makes me suspicious: Is this actually a
Date/Time field, or is it a Text field containing dates? Could you
post your actual expression?
 
Back
Top