Microsoft Access Convert Numbers to Dates

[Grinder]

Someone just gave me an old database to work on, and the dates are all stored
in text fields with values like 990921. How can I convert these over to
regular Access dates?

 

[Amicron]

If you've inherited a database that has dates in a text field, with a format
like 990102 for Jan-2-1999, you need to convert those to regular Access date
fields.

Add a new blank DATE field to your table to store your new value. My old
date is in the text field CustomerDOB, so I'll add a new one called
NewCustomerDOB.

Now I'll create an UPDATE QUERY using the LEFT, RIGHT, and MID functions to
put the date together:

Update to: Right([CustomerDOB],2) & "/" & Mid([CustomerDOB],3,2) & "/" &
Left([CustomerDOB],2)

Be sure to back up your database before running any update queries as there
is always the possibility of data loss!

I actually have a video tutorial on my web site that shows how to do this:

<a
href="http://599cd.com/tips/access/AC309-convert-numbers-to-dates.asp?key=MSForum">Microsoft Access Convert Numbers To Dates</a>

http://599cd.com/tips/access/AC309-convert-numbers-to-dates.asp


Hope this helps.

Richard

 

[Pieter Wijnen]

Safer is
DateSerial(Left([CustomerDOB],2), Mid([CustomerDOB],3,2),
Right([CustomerDOB],2))
As you don't have to worry about the regional settings

Pieter

 

[Bunky]

Obviously, I am not following the DateSerial command.
I have a date that is stored on the table in text. It's format is ymmdd. I
have made it yymmdd. Then I tried to
RESERVATION DATE: Format(DateSerial(Right([Res Dateyy],2),Mid([Res
Dateyy],3,2),Left([Res Dateyy],2))) and it does not format correctly. I have
played with the positions and I can't seem to figure out the correlations.
Help.

Safer is
DateSerial(Left([CustomerDOB],2), Mid([CustomerDOB],3,2),
Right([CustomerDOB],2))
As you don't have to worry about the regional settings

Pieter

If you've inherited a database that has dates in a text field, with a
format
like 990102 for Jan-2-1999, you need to convert those to regular Access
date
fields.

Add a new blank DATE field to your table to store your new value. My old
date is in the text field CustomerDOB, so I'll add a new one called
NewCustomerDOB.

Now I'll create an UPDATE QUERY using the LEFT, RIGHT, and MID functions
to
put the date together:

Update to: Right([CustomerDOB],2) & "/" & Mid([CustomerDOB],3,2) & "/" &
Left([CustomerDOB],2)

Be sure to back up your database before running any update queries as
there
is always the possibility of data loss!

I actually have a video tutorial on my web site that shows how to do this:

<a
href="http://599cd.com/tips/access/AC309-convert-numbers-to-dates.asp?key=MSForum">Microsoft
Access Convert Numbers To Dates</a>

http://599cd.com/tips/access/AC309-convert-numbers-to-dates.asp


Hope this helps.

Richard

 

[phleduc]

try format function format([fieldname],"dd/mm/yyyy")
if that still not works there must be a function to convert the resulting
text to dates

 

[Pieter Wijnen]

DateSerial(YearPart,MonthPart,DayPart)

ie

RESERVATIONDATE: DateSerial(Left([Res Dateyy],2),Mid([Res
Dateyy],3,2),Right([Res Dateyy],2)

HTH

Pieter

Obviously, I am not following the DateSerial command.
I have a date that is stored on the table in text. It's format is ymmdd.
I
have made it yymmdd. Then I tried to
RESERVATION DATE: Format(DateSerial(Right([Res Dateyy],2),Mid([Res
Dateyy],3,2),Left([Res Dateyy],2))) and it does not format correctly. I
have
played with the positions and I can't seem to figure out the correlations.
Help.

Safer is
DateSerial(Left([CustomerDOB],2), Mid([CustomerDOB],3,2),
Right([CustomerDOB],2))
As you don't have to worry about the regional settings

Pieter

If you've inherited a database that has dates in a text field, with a
format
like 990102 for Jan-2-1999, you need to convert those to regular Access
date
fields.

Add a new blank DATE field to your table to store your new value. My
old
date is in the text field CustomerDOB, so I'll add a new one called
NewCustomerDOB.

Now I'll create an UPDATE QUERY using the LEFT, RIGHT, and MID
functions
to
put the date together:

Update to: Right([CustomerDOB],2) & "/" & Mid([CustomerDOB],3,2) & "/"
&
Left([CustomerDOB],2)

Be sure to back up your database before running any update queries as
there
is always the possibility of data loss!

I actually have a video tutorial on my web site that shows how to do
this:

<a
href="http://599cd.com/tips/access/AC309-convert-numbers-to-dates.asp?key=MSForum">Microsoft
Access Convert Numbers To Dates</a>

http://599cd.com/tips/access/AC309-convert-numbers-to-dates.asp


Hope this helps.

Richard

 

[Bunky]

Just when I thought I understood...
I am comparing the reservationdate and the checkindate (done the same way as
what you showed below). I add 5 months to the reservationdate and then do a
compare against the checkindate. If the checkindate is any month other than
9, the compare is working fine but when the checkindate month is 9, it is
comparing higher than the reservationdate +5 months

Reservation Date Reservation Date + 5 Checkindate Result
9/17/2007 2/17/2008 10/5/2007 low
9/17/2007 2/17/2008 9/26/2007 high

It should show a low comparison against the Reservation Date + 5 months.
Any idea why it is showing incorrectly?

DateSerial(YearPart,MonthPart,DayPart)

ie

RESERVATIONDATE: DateSerial(Left([Res Dateyy],2),Mid([Res
Dateyy],3,2),Right([Res Dateyy],2)

HTH

Pieter

Obviously, I am not following the DateSerial command.
I have a date that is stored on the table in text. It's format is ymmdd.
I
have made it yymmdd. Then I tried to
RESERVATION DATE: Format(DateSerial(Right([Res Dateyy],2),Mid([Res
Dateyy],3,2),Left([Res Dateyy],2))) and it does not format correctly. I
have
played with the positions and I can't seem to figure out the correlations.
Help.

Safer is
DateSerial(Left([CustomerDOB],2), Mid([CustomerDOB],3,2),
Right([CustomerDOB],2))
As you don't have to worry about the regional settings

Pieter


If you've inherited a database that has dates in a text field, with a
format
like 990102 for Jan-2-1999, you need to convert those to regular Access
date
fields.

Add a new blank DATE field to your table to store your new value. My
old
date is in the text field CustomerDOB, so I'll add a new one called
NewCustomerDOB.

Now I'll create an UPDATE QUERY using the LEFT, RIGHT, and MID
functions
to
put the date together:

Update to: Right([CustomerDOB],2) & "/" & Mid([CustomerDOB],3,2) & "/"
&
Left([CustomerDOB],2)

Be sure to back up your database before running any update queries as
there
is always the possibility of data loss!

I actually have a video tutorial on my web site that shows how to do
this:

<a
href="http://599cd.com/tips/access/AC309-convert-numbers-to-dates.asp?key=MSForum">Microsoft
Access Convert Numbers To Dates</a>

http://599cd.com/tips/access/AC309-convert-numbers-to-dates.asp


Hope this helps.

Richard

 

[Pieter Wijnen]

You should also have a look at the DateAdd & DateDiff Functions

Pieter

Just when I thought I understood...
I am comparing the reservationdate and the checkindate (done the same way
as
what you showed below). I add 5 months to the reservationdate and then do
a
compare against the checkindate. If the checkindate is any month other
than
9, the compare is working fine but when the checkindate month is 9, it is
comparing higher than the reservationdate +5 months

Reservation Date Reservation Date + 5 Checkindate Result
9/17/2007 2/17/2008 10/5/2007
low
9/17/2007 2/17/2008 9/26/2007
high

It should show a low comparison against the Reservation Date + 5 months.
Any idea why it is showing incorrectly?

DateSerial(YearPart,MonthPart,DayPart)

ie

RESERVATIONDATE: DateSerial(Left([Res Dateyy],2),Mid([Res
Dateyy],3,2),Right([Res Dateyy],2)

HTH

Pieter

Obviously, I am not following the DateSerial command.
I have a date that is stored on the table in text. It's format is
ymmdd.
I
have made it yymmdd. Then I tried to
RESERVATION DATE: Format(DateSerial(Right([Res Dateyy],2),Mid([Res
Dateyy],3,2),Left([Res Dateyy],2))) and it does not format correctly.
I
have
played with the positions and I can't seem to figure out the
correlations.
Help.

:

Safer is
DateSerial(Left([CustomerDOB],2), Mid([CustomerDOB],3,2),
Right([CustomerDOB],2))
As you don't have to worry about the regional settings

Pieter


If you've inherited a database that has dates in a text field, with
a
format
like 990102 for Jan-2-1999, you need to convert those to regular
Access
date
fields.

Add a new blank DATE field to your table to store your new value. My
old
date is in the text field CustomerDOB, so I'll add a new one called
NewCustomerDOB.

Now I'll create an UPDATE QUERY using the LEFT, RIGHT, and MID
functions
to
put the date together:

Update to: Right([CustomerDOB],2) & "/" & Mid([CustomerDOB],3,2) &
"/"
&
Left([CustomerDOB],2)

Be sure to back up your database before running any update queries
as
there
is always the possibility of data loss!

I actually have a video tutorial on my web site that shows how to do
this:

<a
href="http://599cd.com/tips/access/AC309-convert-numbers-to-dates.asp?key=MSForum">Microsoft
Access Convert Numbers To Dates</a>

http://599cd.com/tips/access/AC309-convert-numbers-to-dates.asp


Hope this helps.

Richard

 

[Bunky]

I used the DateAdd to add the 5 months but did a regular iif stmt for the
comparison. Thanks a bunch. I will look into the DateDiff.

You should also have a look at the DateAdd & DateDiff Functions

Pieter

Just when I thought I understood...
I am comparing the reservationdate and the checkindate (done the same way
as
what you showed below). I add 5 months to the reservationdate and then do
a
compare against the checkindate. If the checkindate is any month other
than
9, the compare is working fine but when the checkindate month is 9, it is
comparing higher than the reservationdate +5 months

Reservation Date Reservation Date + 5 Checkindate Result
9/17/2007 2/17/2008 10/5/2007
low
9/17/2007 2/17/2008 9/26/2007
high

It should show a low comparison against the Reservation Date + 5 months.
Any idea why it is showing incorrectly?

DateSerial(YearPart,MonthPart,DayPart)

ie

RESERVATIONDATE: DateSerial(Left([Res Dateyy],2),Mid([Res
Dateyy],3,2),Right([Res Dateyy],2)

HTH

Pieter

Obviously, I am not following the DateSerial command.
I have a date that is stored on the table in text. It's format is
ymmdd.
I
have made it yymmdd. Then I tried to
RESERVATION DATE: Format(DateSerial(Right([Res Dateyy],2),Mid([Res
Dateyy],3,2),Left([Res Dateyy],2))) and it does not format correctly.
I
have
played with the positions and I can't seem to figure out the
correlations.
Help.

:

Safer is
DateSerial(Left([CustomerDOB],2), Mid([CustomerDOB],3,2),
Right([CustomerDOB],2))
As you don't have to worry about the regional settings

Pieter


If you've inherited a database that has dates in a text field, with
a
format
like 990102 for Jan-2-1999, you need to convert those to regular
Access
date
fields.

Add a new blank DATE field to your table to store your new value. My
old
date is in the text field CustomerDOB, so I'll add a new one called
NewCustomerDOB.

Now I'll create an UPDATE QUERY using the LEFT, RIGHT, and MID
functions
to
put the date together:

Update to: Right([CustomerDOB],2) & "/" & Mid([CustomerDOB],3,2) &
"/"
&
Left([CustomerDOB],2)

Be sure to back up your database before running any update queries
as
there
is always the possibility of data loss!

I actually have a video tutorial on my web site that shows how to do
this:

<a
href="http://599cd.com/tips/access/AC309-convert-numbers-to-dates.asp?key=MSForum">Microsoft
Access Convert Numbers To Dates</a>

http://599cd.com/tips/access/AC309-convert-numbers-to-dates.asp


Hope this helps.

Richard

 

[Bunky]

Pieter,

I looked into several Date functions but did not see anything like what I
need.
I need to look at the reservation date and compare it to the check in date.
If the difference is greater than 5 months and the number of nights staying
is less than 7, it is an error. One problem is if the reservation date is
9/17/07 and to get the 5 months out does not actually compute to 5 months.
Our MainFrame adds 5 to the month field, subtracts 12 if it goes over, and
tells everyone 5 months from 9/17/07 is 2/17/08. So I want to see if the
difference between the reservation date and the check in date is greater than
5 months and the number of nights is less than 7. Is there an easy way to do
this?

Thanks for your help!

I used the DateAdd to add the 5 months but did a regular iif stmt for the
comparison. Thanks a bunch. I will look into the DateDiff.

You should also have a look at the DateAdd & DateDiff Functions

Pieter

Just when I thought I understood...
I am comparing the reservationdate and the checkindate (done the same way
as
what you showed below). I add 5 months to the reservationdate and then do
a
compare against the checkindate. If the checkindate is any month other
than
9, the compare is working fine but when the checkindate month is 9, it is
comparing higher than the reservationdate +5 months

Reservation Date Reservation Date + 5 Checkindate Result
9/17/2007 2/17/2008 10/5/2007
low
9/17/2007 2/17/2008 9/26/2007
high

It should show a low comparison against the Reservation Date + 5 months.
Any idea why it is showing incorrectly?
:

DateSerial(YearPart,MonthPart,DayPart)

ie

RESERVATIONDATE: DateSerial(Left([Res Dateyy],2),Mid([Res
Dateyy],3,2),Right([Res Dateyy],2)

HTH

Pieter

Obviously, I am not following the DateSerial command.
I have a date that is stored on the table in text. It's format is
ymmdd.
I
have made it yymmdd. Then I tried to
RESERVATION DATE: Format(DateSerial(Right([Res Dateyy],2),Mid([Res
Dateyy],3,2),Left([Res Dateyy],2))) and it does not format correctly.
I
have
played with the positions and I can't seem to figure out the
correlations.
Help.

:

Safer is
DateSerial(Left([CustomerDOB],2), Mid([CustomerDOB],3,2),
Right([CustomerDOB],2))
As you don't have to worry about the regional settings

Pieter


If you've inherited a database that has dates in a text field, with
a
format
like 990102 for Jan-2-1999, you need to convert those to regular
Access
date
fields.

Add a new blank DATE field to your table to store your new value. My
old
date is in the text field CustomerDOB, so I'll add a new one called
NewCustomerDOB.

Now I'll create an UPDATE QUERY using the LEFT, RIGHT, and MID
functions
to
put the date together:

Update to: Right([CustomerDOB],2) & "/" & Mid([CustomerDOB],3,2) &
"/"
&
Left([CustomerDOB],2)

Be sure to back up your database before running any update queries
as
there
is always the possibility of data loss!

I actually have a video tutorial on my web site that shows how to do
this:

<a
href="http://599cd.com/tips/access/AC309-convert-numbers-to-dates.asp?key=MSForum">Microsoft
Access Convert Numbers To Dates</a>

http://599cd.com/tips/access/AC309-convert-numbers-to-dates.asp


Hope this helps.

Richard