formatting a number to a date

[ahajee]

I'm trying to convert a number to a date in access. If i open the table in design view and change the data type from number to date, it doesnt format it correctly. So in my query, whats the formula for formatting a date.

Format([Date],"mm,dd,yyyy") ------doesnt work.

 

[Norie]

convert a number to a date in access

What are the numbers like? And how did you end up with numbers instead of date?

Access will usually recognise a number representing a data when importing data.

 

[ahajee]

i extracted some data from a database and i need to format the date in access. I've already changed the data type from number to date. Now i need to format in my query.

ex. 20041101, 20042804

 

[Norie]

Try this

RealDate:DateSerial(Left([Date], 4), Right([Date], 2), Mid([Date], 5, 2))

 

[ahajee]

If my field name is "Due Date", what would the formula read?

 

[Norie]

I take it you mean that Due Date contains 20041101 etc?

Something like this:

RealDate:DateSerial(Left([Due Date], 4), Right([Due Date], 2), Mid([Due Date], 5, 2))