How CDate() determines century

[kdbarrett]

I'm having problem with the Make Table Query below. I have a table
with month, day and year stored as three separate, 2-character string
fields (I did NOT design this database). I need to write queries that
compare dates so I came up with this query convert these string fields
into usable dates. It works fine EXCEPT the year fields with a value
of 29 and lower are converted to 2029 instead of 1929. Is there a
setting somewhere to change this? Do I need to force a "19" into the
year field?

Being overly paranoid about posting client data structure, I've
replaced table names with Table1 and Table2, the location of the
database with <database>, removed keywords in the fieldnames and added
bogus selection criteria.

SELECT Table1.Number, Table2.Code, IIF(Table2.1Name<>"",
cdate(Table2.1MM & "/" & Table2.1DD & "/" & Table2.YY), date()) AS
1DOB, IIF(Table2.2Name<>"", cdate(Table2.2MM & "/" & Table2.2DD & "/" &
Table2.2YY), date()) AS 2DOB, IIF(Table2.3Name<>"", cdate(Table2.3MM &
"/" & Table2.3DD & "/" & Table2.3YY), date()) AS 3DOB,
IIF(Table2.4Name<>"", cdate(Table2.4MM & "/" & Table2.4DD & "/" &
Table2.4YY), date()) AS 4DOB INTO Tbl_MadeTable IN '<database>.mdb'
FROM Table1 INNER JOIN Table2 ON Table1.Number = Table2.Number
WHERE (((Table1.Code)="AA") AND ((Table2.Code)<>"-"));

Thanks for your help
Kevin Barrett

 

[MGFoster]

I'm having problem with the Make Table Query below. I have a table
with month, day and year stored as three separate, 2-character string
fields (I did NOT design this database). I need to write queries that
compare dates so I came up with this query convert these string fields
into usable dates. It works fine EXCEPT the year fields with a value
of 29 and lower are converted to 2029 instead of 1929. Is there a
setting somewhere to change this? Do I need to force a "19" into the
year field?

Being overly paranoid about posting client data structure, I've
replaced table names with Table1 and Table2, the location of the
database with <database>, removed keywords in the fieldnames and added
bogus selection criteria.

SELECT Table1.Number, Table2.Code, IIF(Table2.1Name<>"",
cdate(Table2.1MM & "/" & Table2.1DD & "/" & Table2.YY), date()) AS
1DOB, IIF(Table2.2Name<>"", cdate(Table2.2MM & "/" & Table2.2DD & "/" &
Table2.2YY), date()) AS 2DOB, IIF(Table2.3Name<>"", cdate(Table2.3MM &
"/" & Table2.3DD & "/" & Table2.3YY), date()) AS 3DOB,
IIF(Table2.4Name<>"", cdate(Table2.4MM & "/" & Table2.4DD & "/" &
Table2.4YY), date()) AS 4DOB INTO Tbl_MadeTable IN '<database>.mdb'
FROM Table1 INNER JOIN Table2 ON Table1.Number = Table2.Number
WHERE (((Table1.Code)="AA") AND ((Table2.Code)<>"-"));

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

How do you determine which century the 2 numbers represent?

The Windows O/S has a default: anything after 00 and before 30 is
considered 2000's. If you want to explicitly indicate the century you
have to use the 4-digit year. Since the year column in your table only
has 2 digits you need to change them to 4-digit numbers.

This was a standard Y2K test and correction procedure that was carried
out by responsible/competent companies in 1999. Why didn't your company
do this update/correction then? If this DB didn't exist then, the DB
designer should have known about this problem and designed for it, or,
did the DB specifications indicated that only 2000 dates would be used?

If the last is the situation, all you have to do is prefix 20 to the
year numbers when creating the date.

BTW, use DateSerial(year value (4-digits), month value, day value)
instead of string concatentation to create the date. See the VBA Help
for a description of DateSerial() function.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQzrSsYechKqOuFEgEQLXyQCgmuFtBvrjvkpGgWgult/nbKP9SgAAn1ri
2jiKgJm3dEAYxDT+BLjoet3V
=3VRH
-----END PGP SIGNATURE-----

 

[kdbarrett]

Okay, thanks for the info! I had the feeling it was something like
that.

As for how the database got that way, I have no idea. I'm a contractor
who just came on board about a month ago and I was just handed this
database and asked to pull some data from it. I can't change it, I
just have to deal with it.

Thanks again!