output format of date/time

[KesLongwood]

Hey guys, been away from Access and SQL for some time. I have a simple 2 column table with one thats set to Date/Time that displays the date only. Unfortunaly, when I run my query, the date comes out 7/9/2005 and I want it to come out as 7/09/2005. I know I am going to have to force it out with a format command, but its been so long that I have no idea how to. Right now my query, in SQL format, looks like this.

SELECT * FROM Table1 ORDER BY Date DESC

I just need the format command to force the date. If anyone can help, thx.

 

[mresann]

One of three ways:

SQL Method:

Instead of using the "all" (*) indicator, you can select every field individually. However, replace the date field you want to format to an expression. Say your field is called "MyDate", then the expression would be:

SELECT Format([MyDate], "m/dd/yyyy") AS NewMyDate

...in addition to all the other fields in the SELECT statement.

DESIGN GRID METHOD:

In the Design Grid, remove the * field, and add all the fields individually to the grid. Replace the date field MyDate with this expression:

NewMyDate: Format([MyDate], "m/dd/yyyy")

When you switch to SQL view, you will note it's identical to the SQL method above.

THIRD METHOD (Not recommended, but would work): In the design grid, replace the * field with all the individual fields. Select the MyDate field. In the property box, set the format to "m/dd/yyyy". NOTE: the SQL statement will not change, but the change is reflected in the query's internal information storage capacity, which is why it's not recommended.