Change Format of Date Field

  • Thread starter Thread starter Jim
  • Start date Start date
J

Jim

I have a table where the date displays as "20080618". I would like to change
it to display "Jun 18, 2008". Is there a way to do this? I looked at the
properties for the field, and don't see an option to change the format.
 
Jim said:
I have a table where the date displays as "20080618". I would like to
change it to display "Jun 18, 2008". Is there a way to do this? I
looked at the properties for the field, and don't see an option to
change the format.
Click to expand...

Then it must either be a Date/Time field whose format is set to yyyymmdd or
it is a Text or Number field in which users have manually entered dates in
that format. Which is it?

I will assume the first possibility is not true because you would probably
have found the Format property for the field in Design view by now. That
leaves the other possibility, about which i cannot be specific without
answers to the following questions:

1. What is the datatype of the field?
2. Where do you want it to be displayed in that format? In a form? In a
report? In a query result? In the datasheet itself?
 
In the table, what type of field is the date? Is it a text field or is it a
datetime field or perhaps a number field?

I suspect it may be the former.

Are you trying to change the display in a form, on a report, in a query? If
on a form, do you need to be able to edit the value?

In a query, you can use the following expression to force the return of a date
type field instead of a text type field.
IIF([Date Field] is Null, Null, CDate(Format([Date Field],"@@@@-@@-@@")))

You can then format the display or if you want to do it in the query
IIF([Date Field] is Null, Null, Format(CDate(Format([Date
Field],"@@@@-@@-@@"),"mmm dd, yyyy")))

Be aware that this change the data type back to a string.

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
 
You will not be able to do that. Numbers cannot be displayed in that format
without converting them to another data type, which is typically done in a
query, form or report.

Your have two recourses if it needs to be displayed that way in the
datasheet

1. Create a calculated column that uses VBA to convert the number to either
a string or a Date/Time value with the proper format
2. Create a new column with a data type of Date/Time or Text, and run an
Update query to calculate the value to go into the new field.
The decision between Date/Time and Text depends on whether you need to use
date arithmetic on the resulting values, or if you need to be able to sort
the results as dates rather than strings.

In order to properly handle errors, I would create a VBA function in a
module to do this conversion. Here is how it would look if you wanted it to
return a string (I am going to assume you are only using AD dates later than
1900 - you will need to modify the following to handle BC dates):

Function CNumberToDateFormattedString(n as long) _
as String
dim d as date, s as string
if n is null then
ConvNumberToDateFormattedString=""
else
If n between 19000101 and 99991231 then
s=cstr(n)
on error resume next
d=dateserial(CInt(Left(s,4)),CInt(Mid(s,5,2)), _
CInt(Right(s,2)))
if err<>0 then
CNumberToDateFormattedString="invalid date"
else
on error goto 0
s=Format(d,"mmm d, yyyy")
CNumberToDateFormattedString=s
end if
else
CNumberToDateFormattedString="invalid date"
end if
end if
End Function

To use it in an update query, you would do this:
update table
set newtextfield =
CNumberToDateFormattedString(orginalnumberfield)
 
Back
Top