asked on

Convert a Long Date to a Short Date

I have a columm in my table set as a "short date"

it is displayed as 23/08/2011

but when I click in to the table it comes out as 23/08/2011 09:18:00.

can I use a function to convert it to 23/08/2011

I ma trying to use it in a group query but keep geeting the wrong answer as all the times are shown

SOLUTION

Norie

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Brogrim

ASKER

I ma using ms access 2003

Whn i group them it is grouping the time in the date so instead of getting 20 results for a date I am getting 5 because it is grouping the time

dqmq

Format([YourDateField],"dd/mm/yyyy")

ASKER CERTIFIED SOLUTION

Norie

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Brogrim

ASKER

Format([YourDateField],"dd/mm/yyyy")

the above is not working, getting incorrect results

JustDate:INT([DateTimeField])

is returning numbers

I want to make a new table with the value only holdig a short date

Brogrim

ASKER

found the solution

UPDATE TableName SET TableName.DateTimeField =
DateValue([TableName].[DateTimeField]);

dqmq

Try:

DateValue([YourDate])

Patrick Matthews

>>I have a columm in my table set as a "short date"

That is a DISPLAY format, and has no bearing on how Access stores the value.

dqmq's suggestion of Format should work; please explain what you mean by "getting incorrect results".

In any event, Format returns a string. If you need to return a true date value:

JustDate: CDate(Int(DateTimeField))

Norie

Those numbers are dates, they might just need formatting as dates, but don't use Format unless you want to convert them to text.

You can format fields in a query by right clicking, selecting Properties and setting the Format property appropriately.

What exactly did you try anyway?

When I try it with a date/time field I get dates.