I have a table that I've linked to a J.D. Edwards database using an ODBC
driver. The table or data that I'm pulling from Edwards is our Item Master
table; containing all of our company products. There are a couple of date
fields in the table. First of all, there is the effective date which is the
date the item was added to the system. And secondly, there is the expired
date field which is the date the item's existing price will expire.

The data in the date fields come through as Access Table Data Type, Text.
The dates look like this 12/30/30 (i.e. the item's price in the system will
expire 30-Dec-2030.)

However, & this is the problem; when I run a query using the data mentioned
above, instead of the year being 2030 it's 1930. How do I get it to recognize
the century?

Any ideas or suggestions would be greatly appreciated.

Regards,
Jason

On Mon, 27 Apr 2009 06:33:01 -0700, JK <jasonk at necoffeeco dot com> wrote:

>I have a table that I've linked to a J.D. Edwards database using an ODBC
>driver. The table or data that I'm pulling from Edwards is our Item Master
>table; containing all of our company products. There are a couple of date
>fields in the table. First of all, there is the effective date which is the
>date the item was added to the system. And secondly, there is the expired
>date field which is the date the item's existing price will expire.
>
>The data in the date fields come through as Access Table Data Type, Text.
>The dates look like this 12/30/30 (i.e. the item's price in the system will
>expire 30-Dec-2030.)
>
>However, & this is the problem; when I run a query using the data mentioned
>above, instead of the year being 2030 it's 1930. How do I get it to recognize
>the century?
>
>Any ideas or suggestions would be greatly appreciated.
>
>Regards,
>Jason
>

By default, two digit dates between 00 and 29 are interpreted as being in the
21st century, those between 30 and 99 as being in the 20th. Why J.D.Edwards is
using two digit dates for investment products (which could span decades!) is
beyond me, and why they're using a Text field for dates suggests database
incompetence!

If you can count on this date as certainly being in the future (or in the 21st
century at any rate) you can use

IIF(Year([datefield]) < 2000, DateAdd("yyyy", 100, [datefield]), [datefield])

to calculate the corrected date.
--

John W. Vinson [MVP]

On Mon, 27 Apr 2009 06:33:01 -0700, JK <jasonk at necoffeeco dot com>
wrote:

>I have a table that I've linked to a J.D. Edwards database using an ODBC
>driver. The table or data that I'm pulling from Edwards is our Item Master
>table; containing all of our company products. There are a couple of date
>fields in the table. First of all, there is the effective date which is the
>date the item was added to the system. And secondly, there is the expired
>date field which is the date the item's existing price will expire.
>
>The data in the date fields come through as Access Table Data Type, Text.
>The dates look like this 12/30/30 (i.e. the item's price in the system will
>expire 30-Dec-2030.)
>
>However, & this is the problem; when I run a query using the data mentioned
>above, instead of the year being 2030 it's 1930. How do I get it to recognize
>the century?
>
>Any ideas or suggestions would be greatly appreciated.

Hi Jason,

Access is assuming the century correctly according to its rules. To
override them in your query, you can use a calculated column:

CorrectedExpDate:
IIf(CDate([MyExpDate])<#1/1/1980#,DateAdd('yyyy',100,CDate([MyExpDate])),CDate([MyExpDate]))

The above technique assumes that any year lower than '80' will be
considered to be in the future, that is, 20xx - you can change the
value to whatever works for you.

Armen Stein
Microsoft Access MVP
www.JStreetTech.com