How Do I Convert a Date in Access to a Serial Number

[eightbo]

I'm using a text data type (for numerous reasons, using date data-type is not an option) for my Dates columns and they were all running fine but after changing data types and changing them back, they all became serial numbers. Can't figure out how to convert them back to dates whilst still in the text data-type.

Tried using "dd/mm/yyyy/" (works in Excel but not Access?) in the format field for my dates however they stay put as Serial numbers.

Need this fixed as soon as possible.

Using Access 2007.
Simple explanations appreciated,
Thanks

[ADezii]

The CDate() Function should do the trick:

Debug.Print CDate("40827") ==> 10/11/2011

[NeoPa]

It only works in Excel because you haven't tested it properly. Strings don't change when put through any Formatting. Excel converted them to dates (or numbers depending on your test procedure) as soon as you entered them in.

As ADezii says, CDate() is what you need to convert a string in date format into a date. Dates are stored as numbers anyway, so how you use that is down to you (in your code).

I would say at this point, that you should probably reconsider your use of text fields to store your dates. I'm frequently instructing people that dates should only ever be converted from dates into strings at the very last point. IE. When displaying for a human. Up until that point they can have formats assocated with them, but should be stored in numeric form. I don't know your exact situation, so it's hard for me to be categorical about it, but I can say that trying to work with dates that aren't stored as dates is like trying to work with one hand tied behind your back. Converting values backwards and forwards as you go is simply poor coding practice and should be avoided where possible (Anyone with any experience looking at such code will be immediately unimpressed).