convert text field to date

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

Guest

I have a report pulling data from a table that has the date column defined as
text. Is there a way to convert this field to a date format on the report?
As it is now, all sorting is incorrect because it views the field as text.

Thanks in advance.
 
Doug said:
I have a report pulling data from a table that has the date column defined as
text. Is there a way to convert this field to a date format on the report?
As it is now, all sorting is incorrect because it views the field as text.
Click to expand...


You should probably use a query for the report's record
source. Then you can use a claculated field in the query to
convert the field:

Datefield: CDate(textfield)

OTOH, if all you want is to get the report sorted by the
date and you don't use the text date for anything other than
displaying it, you could just use the expression
=CDate(textfield) in Sorting and Grouping.
 
Thanks for all the suggestions. However I am still having a problem. I used
cdate(fieldname) in the query suppling the data to the report. The report is
also sorting based on this field. I think it is still sorting it like a text
field. My results come back with a date order of 1/5/2005, 1/6/2005,
12/27/2004, 12/28/2004, 2/1/2004, etc.

I also tried using cdate(fieldname) in the sort criteria of the report, but
get the same result.

Any other ideas?

Thanks for your help.
 
Either CDate or DateValue should convert your text date to a
real date value.

If you do this in the query, make sure the the query doesn't
have something in the field's format property. Also make
sure the report's Sorting and Grouping uses the calculated
field name.

I don't know what you mean by "sort criteria of the report",
but if you use the CDate in the report's Sorting and
Grouping field/expression, make sure you put an = sign in
front of it:
=CDate(textfieldname)

Note that any approach is going to cause problems if you
have any records where the text date can not be converted to
a real date value.
 
Back
Top