Calculated date display in query result

[telephoto]

Hi guys

I have a table with a "Month" field which holds data as mmm (text).
This feeds a combo box which is used as the search criteria for a report. So far no problems.

The report is based on a query, and I am trying to calculate an expression to take the search "mmm" and display it as mmmm/yy.

The search string is "Apr" and what I want in the report is "April 03" Is there a Date wizard out there who can tell me what to put in the top line of the query design?

TIA

Telephoto

 

[jrbarnett]

Use

Format (Month, "mmmm/yy&quot

John

 

[JonoB]

Have a look at the Dateserial function.

If I understand you correctly, you are looking for something like :
=dateserial(year(date(),month([Month]),5)

I have set day to an arbitary 5, as it doesnt look that important to you. If you are looking for the end of the month, then do =dateserial(year(date(),month([Month])+1,0)

Hope this helps

 

[telephoto]

JonoB
I think you are on right track, I am using Access 97 and this expression in the top line of the query design returns #Error


Expr1: DateSerial(Year(Date()),Month([Month]),5)

I am not interested in the day at all, so Apr gets shown as April 03 in the report textbox.

Telephoto

 

[JonoB]

If I understand you correctly, you dont need the dateserial function in the query...it sounds like you only need it on your report.

If this is the case, create an unbound control on your report and put the daterderial function into this control.