Convert date type to text type

  • Thread starter Thread starter Sirocco
  • Start date Start date
S

Sirocco

Anyone know a way to convert a date field to text? For example, I want to
convert 11/22/04 to text.

Thanks in advance.
 
Use the Format function.

--
John Viescas, author
"Building Microsoft Access Applications" (Coming Soon!)
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
 
Sirocco said:
Anyone know a way to convert a date field to text? For example, I want
to
convert 11/22/04 to text.
Click to expand...

Sirocco, try the Format function as specified below. The results are at
right, and the Date Field has been initialized with today's date or
#12/2/2004#):

Format([Date Field], "General Date") 12/2/2004
Format([Date Field], "Long Date") Thursday, December 02, 2004
Format([Date Field], "Medium Date") 02-Dec-04
Format([Date Field], "Short Date") 12/2/2004
Format([Date Field], "dddd, mmm d yyyy") Thursday, Dec 2 2004

GENERAL DATE
Display a date and/or time. For real numbers, display a date and time, for
example, 4/3/93 05:34 PM. If there is no fractional part, display only a
date, for example, 4/3/93. If there is no integer part, display time only,
for example, 05:34 PM. Date display is determined by your system settings.

LONG DATE
Display a date according to your system's long date format.

MEDIUM DATE
Display a date using the medium date format appropriate for the language
version of the host application.

SHORT DATE
Display a date using your system's short date format.

USER DEFINED
There are many characters (/ : c d yy AM ...) you can use to create
user-defined date/time formats. For more details/examples consult the
Access' help)

-Randy
 
Sirocco said:
Anyone know a way to convert a date field to text? For example, I want to
convert 11/22/04 to text.
Click to expand...

Take your pick...

CStr([YourField])

Format([YourField],"mm/dd/yy")

[YourField] & ""
 
Thanks guys. The Format function doesn't actually convert date to text
type, as evidenced by the type conversion error when comparing the resulting
field with an actual 'date type' date. In contrast, CStr actually
converted the type.
 
Au contraire. Format returns a string. What were you trying to compare the
result to? The problem with CStr is it will return the date in the default
Short Date format defined for the machine -- which could be anything from
mm/dd/yyyy to dd/mm/yyyy to yyyy/mm/dd or anything else.

--
John Viescas, author
"Building Microsoft Access Applications" (Coming Soon!)
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
 
Actually, CStr wouldn't work as intended either. I'm trying to compare
12/2/04 to 12/2/04 10:00:00 AM, from 2 different date fields, but which
can't be compared as is (can't compare short date with long date).
Solution: first, I used the format function to convert the long date to the
short date, but that also turns it into a text field (as evidenced by the
type mismatch error), so I put all the values into a temp table, then
queried that, but used the CStr function to convert the remaining date to
text, so I ended up comparing text to text, and got the results I wanted.
I suppose the question remains is this the best way to compare a short date
with a long date?
 
Sirocco said:
Actually, CStr wouldn't work as intended either. I'm trying to compare
12/2/04 to 12/2/04 10:00:00 AM, from 2 different date fields, but which
can't be compared as is (can't compare short date with long date).
Solution: first, I used the format function to convert the long date to
the
short date, but that also turns it into a text field (as evidenced by the
type mismatch error), so I put all the values into a temp table, then
queried that, but used the CStr function to convert the remaining date to
text, so I ended up comparing text to text, and got the results I wanted.
I suppose the question remains is this the best way to compare a short
date
with a long date?
Click to expand...

Sirocco, thanks for clarifying your issue. To compare two date fields
(excepting their time) just use either of the following expressions:

Fix([Date Field])

or

Format([Date Field], "mm/dd/yyyy")

-Randy
 
To compare a date-only field that has date and time, use:

WHERE (DateOnly <= DateTime) And ((DateOnly + 1) > DateTime)

Or

WHERE DateOnly = DateValue(DateTime)

--
John Viescas, author
"Building Microsoft Access Applications" (Coming Soon!)
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
 
Back
Top