Changing date in number format to dd/mm/yyyy?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

The database I use stores dates as numbers in the form yyyymmdd. Is there an
easy way to convert that to a date format, mm/dd/yyyy? Thanks.
 
if the date is split between 3 fields (month/day/year) you can create a date
with DATESERIAL(YearValue,MonthValue,DayValue), if it's a string that looks
like a date, if it's in a format that Access recognizes you can use
CDate(Value to Convert).
 
CDate(Format(MyNumber, "0000-00-00"))

That will convert the number to a date, which will be displayed in whatever
format you've set as your default under Regional Settings in the Control
Panel.

If you want to force the display to be mm/dd/yyyy, regardless of what the
Short Date format has been set to in Regional Settings, use

Format(CDate(Format(MyNumber, "0000-00-00")), "mm\/dd\/yyyy")

Note that using the Format function will actually convert the date into a
string.
 
I have the same issue, but I have tried this along with the CVDate
recommendations and neither one works...I keep getting a data type mismatch
error. Does the original value have to be text for this to work (my value is
numeric yyyymmdd). PLEASE HELP!!!!!!!

Eric
 
I have the same issue, but I have tried this along with the CVDate
recommendations and neither one works...I keep getting a data type mismatch
error. Does the original value have to be text for this to work (my value is
numeric yyyymmdd). PLEASE HELP!!!!!!!
Click to expand...

CVdate cannot handle a bare six-digit number to a date. It needs the
slashes, or dashes, or some sort of punctuation to make it look like a
date. Try

CVDate(Format([numberdate], "@@@@/@@/@@"))


John W. Vinson[MVP]
 
Back
Top