Can I convert a date to text?

[Tony Williams]

This may sound an odd request but I have a good reason for it. I have a field
which holds a date like this 26/02/08 ie dd/mm/yy. I want to convert the data
held in this field and populate another field with the date as a text string
like this 02262008 ie mmddyyyy but as a text field not a date field. When I
know what the conversion is I want to update this second field with the text
equivalent using an update query.

Is this at all possible?

many thanks
Tony

 

[Jerry Whittle]

It's almost always a bad idea to store something that can be derived from
another field. For instance if you have a Birthdate field, you should not
have an Age field. Instead you should calculate the age as needed from the
birthdate.

Same goes for your date field. So the question is how to display 26/02/08 as
02262008 when needed. First off: is the field holding the 26/02/08 a text
field or a date field? Also what country are you in? I as because Access
works best with the American mm/dd/yyyy format. It's an ANSI standard and
guess what the A stands for!

Something like this should work when you need it in a query, form, or report
if your DateField is a date datatype. If it's text you may need to swap the
month and day parts around.

Format(Month([DateField]),"00") & Format(Day([DateField]),"00") &
Year([DateField])

 

[Gina Whipp]

Tony,

As long as the field that holds the date is a date format I see no reason
why you couldn't use:

Format([YourDateField],"mmddyyyy")

One question... Why do you need to store the field? It isn't noraml to
store redundant information. You could just use the Format() function when
presenting the data.

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

 

[Tony Williams]

Thanks for your suggestions.
I realise this is unusual but the reason I need to do it (I think!) is
because I'm comparing the records of a text imported file against the table
in my database and the date in the text file is in text format and the date
in my table is date format. I thought if I converted the date in the table to
a text string and then populated a second field with that text string I could
compare the two values. The are 220,000+records in my main table and about
3000 records per week in the text import.
Hope that makes sense?
Thanks again
Tony

 

[Douglas J. Steele]

There is no reason to have the second field.

Create a query and add a calculated field to that query that uses the Format
function to convert the date to text.

Use the query wherever you would otherwise have used the table.

 

[Rob Parker]

Hi Tony,

You can do this by using a field in your query which applies a format to a
date field; this will convert it to a text datatype. For example the
following expression in a query will give a text field in the format you
described:
TextDate: Format([YourDate Field],"mmddyyyy")
You can use this expression in an update query, if necessary. However,
since you can always do this conversion in a query and use the result in a
comparison, I don't see any compelling reason to update fields in a table.

Or, alternatively, you can convert text fields to a date/time datatype using
the CDate function. That may be another approach to solving your problem.

HTH,

Rob

 

[Tony Williams]

Thanks Douglas have taken your advice and works fine
Tony