bauerm
asked on
Date field
I have imported over 20,000 records into an access database and the format on one of the date fields reads year/month/day. Is there any way to code or format that field to read month/day/year?
Can it be done by grabbing the first 4 digits (say "1999") and assigning those digits to a variable (say "y") and the next two digits to "m" and the last 2 digits to "d"?
Any help with this would be appreciated.
Mike
Can it be done by grabbing the first 4 digits (say "1999") and assigning those digits to a variable (say "y") and the next two digits to "m" and the last 2 digits to "d"?
Any help with this would be appreciated.
Mike
just use Format(DateValue,"mm/dd/yy
This will take any date field or valid date formatted string and convert it to a string in the appropriate format.
This will take any date field or valid date formatted string and convert it to a string in the appropriate format.
It depends on the format of the field in the database.
If the field is a number, I would do the following:
Assume MyDate is the result you need and TheDate is the field in the database now.
MyDate = mid(TheDate, 5,2) & "/" & right(TheDate,2) & "/" & left(TheDate,4)
That will produce MyMonth = MM/DD/YYYY
(I have to do this with our accounting software - this was their solution to the Y2K issue rather than actually have a date formatted field - Macola!)
Otherwise, if the field is a date format, just not the one you need, you can do the following:
MyDate = format(TheDate, "MM/DD/YYYY")
That should do it for you!
Hope that helps.
If the field is a number, I would do the following:
Assume MyDate is the result you need and TheDate is the field in the database now.
MyDate = mid(TheDate, 5,2) & "/" & right(TheDate,2) & "/" & left(TheDate,4)
That will produce MyMonth = MM/DD/YYYY
(I have to do this with our accounting software - this was their solution to the Y2K issue rather than actually have a date formatted field - Macola!)
Otherwise, if the field is a date format, just not the one you need, you can do the following:
MyDate = format(TheDate, "MM/DD/YYYY")
That should do it for you!
Hope that helps.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
That worked, thanks for the help
This will take any date field or valid date formatted string and convert it to a string in the appropriate format.