Converting Text to Date

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

First, let me say I love this discussion group. It has saved me hours of work.

Here's my dilemma. In a query, I'm trying to convert a text (date) field to
an actual date field. I used the following IIF statement:

IIf(Year(DateValue([MEMBER_DATE_OF_BIRTH]))>2000,DateAdd("yyyy",-1000,DateValue([MEMBER_DATE_OF_BIRTH]))

The problem: Any year above 2000 comes out 1001 and anything below 1929
comes out 1029 (eg., 01-JAN-01 yields 1/1/1001; 26-MAY-29 yields 5/26/1029).

What am I doing wrong? Please help.
 
Since you didn't post any example of the strings (unless your e.g.,
01-JAN-01 yields ... was such) it is hard to say. DateValue will correctly
convert the dates for you.

IIF([MEMBER_DATE_OF_BIRTH] is Null,Null,DateValue([MEMBER_DATE_OF_BIRTH]))

That will convert the DOB to the 21st century (20xx) for all dates where the
2-digit year is between 0 and 29 and to the 20th century (19xx)for dates
from 30 to 99. IF you want them to all be in the 20th century (19xx), then
you should subtract 100 not 1000.
 
I like using the CDate function which changes text strings to dates. It's
problem is bombing out if it can't figure out how to convert the text
properly. Something like "13/13/2005" for example. Therefore I first check
with IsDate and convert the date to some bogus date so that I can fix out the
problem later. This little IIf statement works for me:

IIf(IsDate([YourDateField])=True,CDate([YourDateField]),#1/1/1950#)
 
The query works for anyone for the 21st century; however, it still yields the
string 1/1/2029 for anyone born before 1930. Can I use separate IIF
statements for each century?

--
If you never attempt anything, you will never make any mistakes...never
enjoying the fruits of accomplishment.


John Spencer said:
Since you didn't post any example of the strings (unless your e.g.,
01-JAN-01 yields ... was such) it is hard to say. DateValue will correctly
convert the dates for you.

IIF([MEMBER_DATE_OF_BIRTH] is Null,Null,DateValue([MEMBER_DATE_OF_BIRTH]))

That will convert the DOB to the 21st century (20xx) for all dates where the
2-digit year is between 0 and 29 and to the 20th century (19xx)for dates
from 30 to 99. IF you want them to all be in the 20th century (19xx), then
you should subtract 100 not 1000.

Ess said:
First, let me say I love this discussion group. It has saved me hours of
work.

Here's my dilemma. In a query, I'm trying to convert a text (date) field
to
an actual date field. I used the following IIF statement:

IIf(Year(DateValue([MEMBER_DATE_OF_BIRTH]))>2000,DateAdd("yyyy",-1000,DateValue([MEMBER_DATE_OF_BIRTH]))

The problem: Any year above 2000 comes out 1001 and anything below 1929
comes out 1029 (eg., 01-JAN-01 yields 1/1/1001; 26-MAY-29 yields
5/26/1029).

What am I doing wrong? Please help.
Click to expand...
Click to expand...
 
If ALL the dates should be in the 19xx range then you can adjust it by
testing for the year and subtracting 100 if it is in 2000 or later. This
always gets tricky, but try the following. I hope I have all the parens and
commas correctly placed

IIF(IsDate(MEMBER_DATE_OF_BIRTH)=False,Null,IIF(Year(DateValue(MEMBER_DATE_OF_BIRTH))<2000,DateValue(Member_Date_of_Birth),DateAdd("yyyy",-100,DateValue(Member_Date_of_Birth))))

That should work in a query. It will fail in VBA if Member_Date_Of_Birth is
not a valid date. IIF gets handled slightly different in a query vice in
VBA.

Ess said:
The query works for anyone for the 21st century; however, it still yields
the
string 1/1/2029 for anyone born before 1930. Can I use separate IIF
statements for each century?

--
If you never attempt anything, you will never make any mistakes...never
enjoying the fruits of accomplishment.


John Spencer said:
Since you didn't post any example of the strings (unless your e.g.,
01-JAN-01 yields ... was such) it is hard to say. DateValue will
correctly
convert the dates for you.

IIF([MEMBER_DATE_OF_BIRTH] is
Null,Null,DateValue([MEMBER_DATE_OF_BIRTH]))

That will convert the DOB to the 21st century (20xx) for all dates where
the
2-digit year is between 0 and 29 and to the 20th century (19xx)for dates
from 30 to 99. IF you want them to all be in the 20th century (19xx),
then
you should subtract 100 not 1000.

Ess said:
First, let me say I love this discussion group. It has saved me hours
of
work.

Here's my dilemma. In a query, I'm trying to convert a text (date)
field
to
an actual date field. I used the following IIF statement:

IIf(Year(DateValue([MEMBER_DATE_OF_BIRTH]))>2000,DateAdd("yyyy",-1000,DateValue([MEMBER_DATE_OF_BIRTH]))

The problem: Any year above 2000 comes out 1001 and anything below 1929
comes out 1029 (eg., 01-JAN-01 yields 1/1/1001; 26-MAY-29 yields
5/26/1029).

What am I doing wrong? Please help.
Click to expand...
Click to expand...
Click to expand...
 
Just using DateValue([MEMBER_DATE_OF_BIRTH]) works!!! Imagine that. Thanks
for your help.
--
If you never attempt anything, you will never make any mistakes...thus never
enjoying the fruits of accomplishment.


John Spencer said:
If ALL the dates should be in the 19xx range then you can adjust it by
testing for the year and subtracting 100 if it is in 2000 or later. This
always gets tricky, but try the following. I hope I have all the parens and
commas correctly placed

IIF(IsDate(MEMBER_DATE_OF_BIRTH)=False,Null,IIF(Year(DateValue(MEMBER_DATE_OF_BIRTH))<2000,DateValue(Member_Date_of_Birth),DateAdd("yyyy",-100,DateValue(Member_Date_of_Birth))))

That should work in a query. It will fail in VBA if Member_Date_Of_Birth is
not a valid date. IIF gets handled slightly different in a query vice in
VBA.

Ess said:
The query works for anyone for the 21st century; however, it still yields
the
string 1/1/2029 for anyone born before 1930. Can I use separate IIF
statements for each century?

--
If you never attempt anything, you will never make any mistakes...thus never
enjoying the fruits of accomplishment.


John Spencer said:
Since you didn't post any example of the strings (unless your e.g.,
01-JAN-01 yields ... was such) it is hard to say. DateValue will
correctly
convert the dates for you.

IIF([MEMBER_DATE_OF_BIRTH] is
Null,Null,DateValue([MEMBER_DATE_OF_BIRTH]))

That will convert the DOB to the 21st century (20xx) for all dates where
the
2-digit year is between 0 and 29 and to the 20th century (19xx)for dates
from 30 to 99. IF you want them to all be in the 20th century (19xx),
then
you should subtract 100 not 1000.

First, let me say I love this discussion group. It has saved me hours
of
work.

Here's my dilemma. In a query, I'm trying to convert a text (date)
field
to
an actual date field. I used the following IIF statement:

IIf(Year(DateValue([MEMBER_DATE_OF_BIRTH]))>2000,DateAdd("yyyy",-1000,DateValue([MEMBER_DATE_OF_BIRTH]))

The problem: Any year above 2000 comes out 1001 and anything below 1929
comes out 1029 (eg., 01-JAN-01 yields 1/1/1001; 26-MAY-29 yields
5/26/1029).

What am I doing wrong? Please help.
Click to expand...
Click to expand...
Click to expand...
 
Changing the regional time settings on my computer helped it to work.
--
If you never attempt anything, you will never make any mistakes...never
enjoying the fruits of accomplishment.


John Spencer said:
If ALL the dates should be in the 19xx range then you can adjust it by
testing for the year and subtracting 100 if it is in 2000 or later. This
always gets tricky, but try the following. I hope I have all the parens and
commas correctly placed

IIF(IsDate(MEMBER_DATE_OF_BIRTH)=False,Null,IIF(Year(DateValue(MEMBER_DATE_OF_BIRTH))<2000,DateValue(Member_Date_of_Birth),DateAdd("yyyy",-100,DateValue(Member_Date_of_Birth))))

That should work in a query. It will fail in VBA if Member_Date_Of_Birth is
not a valid date. IIF gets handled slightly different in a query vice in
VBA.

Ess said:
The query works for anyone for the 21st century; however, it still yields
the
string 1/1/2029 for anyone born before 1930. Can I use separate IIF
statements for each century?

--
If you never attempt anything, you will never make any mistakes...never
enjoying the fruits of accomplishment.


John Spencer said:
Since you didn't post any example of the strings (unless your e.g.,
01-JAN-01 yields ... was such) it is hard to say. DateValue will
correctly
convert the dates for you.

IIF([MEMBER_DATE_OF_BIRTH] is
Null,Null,DateValue([MEMBER_DATE_OF_BIRTH]))

That will convert the DOB to the 21st century (20xx) for all dates where
the
2-digit year is between 0 and 29 and to the 20th century (19xx)for dates
from 30 to 99. IF you want them to all be in the 20th century (19xx),
then
you should subtract 100 not 1000.

First, let me say I love this discussion group. It has saved me hours
of
work.

Here's my dilemma. In a query, I'm trying to convert a text (date)
field
to
an actual date field. I used the following IIF statement:

IIf(Year(DateValue([MEMBER_DATE_OF_BIRTH]))>2000,DateAdd("yyyy",-1000,DateValue([MEMBER_DATE_OF_BIRTH]))

The problem: Any year above 2000 comes out 1001 and anything below 1929
comes out 1029 (eg., 01-JAN-01 yields 1/1/1001; 26-MAY-29 yields
5/26/1029).

What am I doing wrong? Please help.
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Text to Date conversion 3
#Error while trying to create DateValue in Access 0
converting text to date 1
Is there a Date Time Conversion from text? 2
Convert text to date 4
How Convert Date to Text 7
Convert Numbers to Dates 15
Obtain Month & Year from a Text formatted Date field using Left Fu 2

Back
Top