DATE (A2K)

Topic

New Reply

Afternoon Everyone!!

How can I do the following in 1 simple step?

Current DATE field appears like this:
20070720

I want the DATE field to appear like this:
07/20/2007

Reply | Quote

Replies

Is it (a) a text field or ( a number field?
If you convert the value, the field will still be a text or number field, not a Date/Time field, so you cannot use it directly for date comparisons and calculations.

Reply | Quote

This is a DOB field and when the data comes via ftp from the government, it comes in as a text field and appears 20072007. Since it is the DOB for the member it does appear on the form. This helps the user verify they have selected the correct member. Because of it’s appearance, others have problems reading it. So I want to have it appear as a DATE and in the correct order.

It won’t need it for any comparisons/calculations as we use the members ID or MedcaidID not the DOB.

Reply | Quote

If you keep it as a text field and change 20070720 to 07/20/2007, it will liik like a date, but it will not be a date.
The advantage of the 20070720 format is that you can sort the dates easily. If you try to sort values such as 07/20/2007 in a text field, the result will be nonsense.
But if you still want to go ahead, you can create a query based on the table.
Add the DOB field to the query grid.
Set the Criteria for this field to Is Not Null
Select Query | Update Query to change the query to an update query.
Enter the following expression in the Update to line, replacing DOB with the actual name of the field:

Mid([DOB],5,2) & "/" & Right([DOB],2) & "/" & Left([DOB],4)

Select Query | Run or click the Run button on the toolbar.
Warnings:
1) Make sure that the “DOB” field in the table has length at least 10.
2) Test the query on a copy of the table first.
3) You can run the query only once – if you run it again when the values have already been changed, you’ll get meaningless results.

Reply | Quote

Thanks Hans,

FYI: This is government data and can’t be modified. Basically I’m making a psuedo Date field (modDOB) in the table. I’m, only updating the modDOB to show the (DOB) in [DATE] view for the form.

Reply | Quote

In that case, I would make the modDOB field a real date/time field, and set it to

DateValue(Mid([DOB],5,2) & “/” & Right([DOB],2) & “/” & Left([DOB],4))

Reply | Quote

Again thanks Hans, works beautifully.

Reply | Quote

Could you also use the DateSerial function?

Reply | Quote

Of course:

DateSerial(Left([DOB],4),Mid([DOB],5,2),Right([DOB],2))

This has the advantage of being independent of regional settings. The DateSerial expression assumed US date format.

Reply | Quote