Formating a Date

  • Thread starter Thread starter Ron Bajda
  • Start date Start date
R

Ron Bajda

Our in-house ordering program uses dates in a "20040414"
(For April 14, 2004) format. I have linked Access 2000 to
the tables

Is there an easy way to reformat this type of date into a
more "normal" format so that I can write "User Friendly"
date queries against the data?

Thanks

Ron
 
Assuming that you'll want to use various queries to read your data, create a
query that returns all fields from the table, plus include a calculated
field in that query:
RealDate: CDate(Mid([DateField], 5, 2) & "/" & Right([DateField],
2) & "/" & Left([DateField], 4)

Then always use this query as the source of the data from the table instead
of using the table itself. You then can use the RealDate field as your
date/time data for sorting/criteria/etc.
 
Sorry to contradict, Ken, but not everyone lives in the US. If the user's
regional setting is set to dd/mm/yyyy, your suggestion won't work, as the
CDate function respects the user's regional settings.

Much safer is to use the DateSerial function.

RealDate: DateSerial(Left([DateField], 4), Mid([DateField], 5, 2),
Right([DateField],2))



--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)


Ken Snell said:
Assuming that you'll want to use various queries to read your data, create a
query that returns all fields from the table, plus include a calculated
field in that query:
RealDate: CDate(Mid([DateField], 5, 2) & "/" & Right([DateField],
2) & "/" & Left([DateField], 4)

Then always use this query as the source of the data from the table instead
of using the table itself. You then can use the RealDate field as your
date/time data for sorting/criteria/etc.

--
Ken Snell
<MS ACCESS MVP>

Ron Bajda said:
Our in-house ordering program uses dates in a "20040414"
(For April 14, 2004) format. I have linked Access 2000 to
the tables

Is there an easy way to reformat this type of date into a
more "normal" format so that I can write "User Friendly"
date queries against the data?

Thanks

Ron
Click to expand...
Click to expand...
 
Talk about regional influences! Thanks, Doug....!

--
Ken Snell
<MS ACCESS MVP>

Douglas J. Steele said:
Sorry to contradict, Ken, but not everyone lives in the US. If the user's
regional setting is set to dd/mm/yyyy, your suggestion won't work, as the
CDate function respects the user's regional settings.

Much safer is to use the DateSerial function.

RealDate: DateSerial(Left([DateField], 4), Mid([DateField], 5, 2),
Right([DateField],2))



--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)


Ken Snell said:
Assuming that you'll want to use various queries to read your data,
Click to expand...
create
a
query that returns all fields from the table, plus include a calculated
field in that query:
RealDate: CDate(Mid([DateField], 5, 2) & "/" & Right([DateField],
2) & "/" & Left([DateField], 4)

Then always use this query as the source of the data from the table instead
of using the table itself. You then can use the RealDate field as your
date/time data for sorting/criteria/etc.

--
Ken Snell
<MS ACCESS MVP>

Ron Bajda said:
Our in-house ordering program uses dates in a "20040414"
(For April 14, 2004) format. I have linked Access 2000 to
the tables

Is there an easy way to reformat this type of date into a
more "normal" format so that I can write "User Friendly"
date queries against the data?

Thanks

Ron
Click to expand...
Click to expand...
Click to expand...
 
Thanks for your thoughts.

I have now done that, but now I get an error message when
I attempt to use a "Between [FirstDate] and [LastDate]"
Criterion in the second query

"ODBC--call failed"

Any thoughts?

Thanks

Ron

-----Original Message-----
Sorry to contradict, Ken, but not everyone lives in the US. If the user's
regional setting is set to dd/mm/yyyy, your suggestion won't work, as the
CDate function respects the user's regional settings.

Much safer is to use the DateSerial function.

RealDate: DateSerial(Left([DateField], 4), Mid ([DateField], 5, 2),
Right([DateField],2))



--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)


Assuming that you'll want to use various queries to
Click to expand...
read your data, create
a
query that returns all fields from the table, plus include a calculated
field in that query:
RealDate: CDate(Mid([DateField], 5, 2) & "/" & Right([DateField],
2) & "/" & Left([DateField], 4)

Then always use this query as the source of the data
Click to expand...
from the table
instead
of using the table itself. You then can use the RealDate field as your
date/time data for sorting/criteria/etc.
Click to expand...


.
Click to expand...
 
Back
Top