Converting number to date?

[eyost]

Hi all,

I am creating a report from a database that stores the date as a numeric format (i.e. 20010619 for today's date). I need to convert this to a date such as June 19, 2001.

Can anyone help?

Thanks,

Ed

 

[Robert Saye]

Allrighty here goes,

This is the way I would do it. Create a new make table query, add all your fields * is okay, then in a new field put this expression.
NewDate: ((Mid([YourNumericFieldNameHere],5,2)) & "/" & (Right([YourNumericFieldNameHere],2)) & "/" & (Left([YourNumericFieldNameHere],4)))

This will create a new table with all your original fields and a new field in the format 6/19/2001(You will need to go into the table design and change the data type to date/time). Then you can delete the number formatted field (Unless you update this table from another place that stores dates like this then save the query and reuse as needed.)To format it like you want, just go to the textbox where the date is stored on your report, and under the format property put mmmm dd", "yyyy which will give you June 19, 2001.

Robert

[This message has been edited by Robert Saye (edited 06-19-2001).]

 

[chrisk]

Rather than changing the table design, you can put an invisible text box [YourDate] on your report bound to your date and a visible text box with

Control Source = DateValue(Str(Mid([YourDate],7,2)) & "/" & Str(Mid([YourDate],5,2)) & "/" & Str(Left([YourDate],4)))

and format = Long Date.

HTH
Chris.