date format and access 1

[SharonMee]

Hi all:

All the dates in my table are in the medium date format e.g. 04-Jun-05. In my access query, among other things, I am grouping by the MAX of a particular date. When I run the value, the date format changes to 06/04/2005.
How do I keep my original date format.

Thanks for helping me.

SharonMee

 

[RoyVidar]

The dates are stored as numbers, so you shouldnt worry about neither how they look in the table nor the query, you shouldn't let your users anywhere near those anyway.

When displaying dates on forms/reports, then consider setting a dateformat in the control bound to the date/time field.

For some reason, there is a way to format dates in queries too (in addition to using the format function in the SQL) by entering the properties of the field and set the desired format.

Roy-Vidar

 

[SharonMee]

Thanks,
I need the date formated in a particular format because my macro works only with that format.
Question: how do I use a format function in my sql?

Thanks.

 

[RoyVidar]

[tt]select Format(somedatefield,"dd-mmm-yyyy") as mydate from mytable[/tt]

But be aware, this might now be a string.

No - or more correct, I doubt your macro needs a specific display format of the date, it would normally work with the number stored in the table, which when displayed is formatted either according to your regional settings or the format you specify.

Only times you really must format a date, is when using a vba sql string with a date criterion, then Jet needs the date in a US recognizable format, so if your regional settins date format differs from a US recognizable format, then you must format, for instance:

[tt]dim rs as dao.recordset
dim sSql as string
sSql="select * from mytable where somedate = #" & _
format(me!txtDate,"yyyy-mm-dd") & "#"
set rs=currentdb.openrecordset(sSql)[/tt]

Would be interesting, even if I don't have the foggiest on macros, to see what you mean by "because my macro works only with that format."

Roy-Vidar

 

[SharonMee]

Thanks Roy,

The "format" worked.

thanks again.

SharonMee