Date compare

T

Tisha

Hello,
I'm trying to select a group of records with an effective date >= Jan 1 of
the prior year forward to date. So I created a compare date of date - 1
year. Next, I want to compare this date to the EFFCYMD so I created a ccyy
date field but the selection criteria fails. The date fields look
different. How do I compare these fields? Thanks!

SELECT tbl_ZCH.GRP, tbl_ZCH.IDNBR, tbl_ZCH.COMPCD, tbl_ZCH.EFFCYMD,
tbl_ZCH.CARCD, Year(Date())-1 AS Datecmp, (Mid([effcymd],1,4)) AS Effccyy,
Right([datecmp],4) AS [Year]
FROM tbl_ZCH;
 
P

pietlinden

Hello,
I'm trying to select a group of records with an effective date >= Jan 1 of
the prior year forward to date. So I created a compare date of date - 1
year. Next, I want to compare this date to the EFFCYMD so I created a ccyy
date field but the selection criteria fails. The date fields look
different. How do I compare these fields? Thanks!

SELECT tbl_ZCH.GRP,
Click to expand...
tbl_ZCH.IDNBR,
tbl_ZCH.COMPCD,
tbl_ZCH.EFFCYMD,
tbl_ZCH.CARCD,
Year(Date())-1 AS Datecmp, (Mid([effcymd],1,4)) AS Effccyy,
Right([datecmp],4) AS [Year]
FROM tbl_ZCH;
Click to expand...

Wait a minute... why don't you just use DateAdd() or DateSerial for
this? Looks like you're making things ten times more difficult than
they should be.
 
J

John W. Vinson

Hello,
I'm trying to select a group of records with an effective date >= Jan 1 of
the prior year forward to date. So I created a compare date of date - 1
year. Next, I want to compare this date to the EFFCYMD so I created a ccyy
date field but the selection criteria fails. The date fields look
different. How do I compare these fields? Thanks!

SELECT tbl_ZCH.GRP, tbl_ZCH.IDNBR, tbl_ZCH.COMPCD, tbl_ZCH.EFFCYMD,
tbl_ZCH.CARCD, Year(Date())-1 AS Datecmp, (Mid([effcymd],1,4)) AS Effccyy,
Right([datecmp],4) AS [Year]
FROM tbl_ZCH;
Click to expand...

You're treating the date as a string. IT ISN'T. A Date/Time field is actually
stored as a number, a count of days and fractions of a day since a start
point. The format does NOT change what's stored, just how it's displayed.

To get all records since January 1 of the previous year use a criterion on the
date field
= DateSerial(Year(Date()) - 1, 1, 1)
Click to expand...

John W. Vinson [MVP]
 
T

Tisha

The effective date that I need to compare is in ccyymmdd format. The result
date from the function >=DateSerial(Year(Date())-1,1,1) is 1/1/2006 so no
matching records. How do I deal with the differing date formats?
Thanks. I've got a lot too learn.

John W. Vinson said:
Hello,
I'm trying to select a group of records with an effective date >= Jan 1 of
the prior year forward to date. So I created a compare date of date - 1
year. Next, I want to compare this date to the EFFCYMD so I created a ccyy
date field but the selection criteria fails. The date fields look
different. How do I compare these fields? Thanks!

SELECT tbl_ZCH.GRP, tbl_ZCH.IDNBR, tbl_ZCH.COMPCD, tbl_ZCH.EFFCYMD,
tbl_ZCH.CARCD, Year(Date())-1 AS Datecmp, (Mid([effcymd],1,4)) AS Effccyy,
Right([datecmp],4) AS [Year]
FROM tbl_ZCH;
Click to expand...

You're treating the date as a string. IT ISN'T. A Date/Time field is actually
stored as a number, a count of days and fractions of a day since a start
point. The format does NOT change what's stored, just how it's displayed.

To get all records since January 1 of the previous year use a criterion on the
date field
= c>
Click to expand...
John W. Vinson [MVP]
Click to expand...
 
J

John W. Vinson

The effective date that I need to compare is in ccyymmdd format.
Click to expand...

Is this a Text field? a Number field? a Date/Time field?
The result
date from the function >=DateSerial(Year(Date())-1,1,1) is 1/1/2006 so no
matching records. How do I deal with the differing date formats?
Thanks. I've got a lot too learn.
Click to expand...

If you store the data in a a Date/Time type field, the format is *completely
irrelevant* to searching - it stores it as a Double Float count of days and
fractions of a day since midnight, December 30, 1899, and searches it exactly
the same way no matter how it's formatted. If you like that format, simply
store the date in a date/time field and use a Format of

yyyymmdd

to see 20070709 for today's date.

If you have other reasons to store the value in a text or number field, you
can use a criterion
= Format((Year(Date())-1,1,1), "yyyymmdd")
Click to expand...

to explicitly cast january 1 of last year into a text string, and search on
that text string.

John W. Vinson [MVP]
 
T

Tisha

I'm getting a syntax error (comma) in query expression
=Format((Year(Date())-1,1,1),"yyyymmdd")
Click to expand...

Btw, the date comes from an IBM AS/400 database.

You've been most helpful.
 
Top