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

BrogrimInformation Systems Development ManagerAsked:
Who is Participating?
 
NorieConnect With a Mentor VBA ExpertCommented:
If it's just the time you want to remove you can use INT.

JustDate:INT([DateTimeField])

You can then group by that.
0
 
NorieConnect With a Mentor VBA ExpertCommented:
What's the problem with the grouping and what are you trying to group by?

What database are you using?

There are various ways to deal with this but it does kind of depend on that.

0
 
BrogrimInformation Systems Development ManagerAuthor Commented:
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
0
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

 
dqmqCommented:
Format([YourDateField],"dd/mm/yyyy")
0
 
BrogrimInformation Systems Development ManagerAuthor Commented:
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
0
 
BrogrimInformation Systems Development ManagerAuthor Commented:
found the solution

UPDATE TableName SET TableName.DateTimeField =
DateValue([TableName].[DateTimeField]);
0
 
dqmqCommented:
Try:

DateValue([YourDate])
0
 
Patrick MatthewsCommented:
>>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))
0
 
NorieVBA ExpertCommented:
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.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.