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;

On Jul 6, 6:36 pm, Tisha <T...@discussions.microsoft.com> wrote:
> 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;

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.

On Fri, 6 Jul 2007 16:36:02 -0700, Tisha <(E-Mail Removed)>
wrote:

>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;
>

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)

John W. Vinson [MVP]

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" wrote:

> On Fri, 6 Jul 2007 16:36:02 -0700, Tisha <(E-Mail Removed)>
> wrote:
>
> >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;
> >
>
> 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>
> John W. Vinson [MVP]
>

On Mon, 9 Jul 2007 10:22:02 -0700, Tisha <(E-Mail Removed)>
wrote:

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

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.

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")

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

John W. Vinson [MVP]

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

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

You've been most helpful.


"John W. Vinson" wrote:

> On Mon, 9 Jul 2007 10:22:02 -0700, Tisha <(E-Mail Removed)>
> wrote:
>
> >The effective date that I need to compare is in ccyymmdd format.
>
> 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.
>
> 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")
>
> to explicitly cast january 1 of last year into a text string, and search on
> that text string.
>
> John W. Vinson [MVP]
>

On Mon, 9 Jul 2007 12:02:02 -0700, Tisha <(E-Mail Removed)>
wrote:

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

sorry... my typo! Actually a missing function name:
>= Format(DateSerial(Year(Date()) - 1, 1, 1), "yyyymmdd")

John W. Vinson [MVP]

Perfect. This solved my problem - Thanks!

"John W. Vinson" wrote:

> On Mon, 9 Jul 2007 12:02:02 -0700, Tisha <(E-Mail Removed)>
> wrote:
>
> >I'm getting a syntax error (comma) in query expression
> >>=Format((Year(Date())-1,1,1),"yyyymmdd")
>
> sorry... my typo! Actually a missing function name:
> >= Format(DateSerial(Year(Date()) - 1, 1, 1), "yyyymmdd")
>
> John W. Vinson [MVP]
>