Date Compares

[DKnight via AccessMonster.com]

Hello,
I am trying to find accts connected between 2 dates. First, I converted the
field to a date using:
newDate: Format(DateSerial(Left([Conndate],2),Mid([conndate],3,2),Right(
[conndate],2)),"mmddyyyy")

Then I tried to compare it using
Between Format(DateAdd("d",-7,Date()),"mmddyyyy") And Format(DateAdd("d",-2,
Date()),"mmddyyyy").

It works only on the month and day part. The month and day fall between the
2 dates, but it displays all the years.

What am I doing wrong?

Thanks in advance!!

 

[Marshall Barton]

I am trying to find accts connected between 2 dates. First, I converted the
field to a date using:
newDate: Format(DateSerial(Left([Conndate],2),Mid([conndate],3,2),Right(
[conndate],2)),"mmddyyyy")

Then I tried to compare it using
Between Format(DateAdd("d",-7,Date()),"mmddyyyy") And Format(DateAdd("d",-2,
Date()),"mmddyyyy").

It works only on the month and day part. The month and day fall between the
2 dates, but it displays all the years.

Don't format the dates. The Format function always returns
a string and string comparisons are different from date
comparisons (dictionary vs. numeric). The DateSerial
function returns a date value and will compare properly with
other date values:

newDate: DateSerial(Left(Conndate,2), Mid(conndate,3,2),
Right(conndate,2)

and

Between DateAdd("d",-7,Date()) And DateAdd("d",-2,Date())

 

[DKnight via AccessMonster.com]

That worked great!!
Thank you

I am trying to find accts connected between 2 dates. First, I converted the
field to a date using:

[quoted text clipped - 7 lines]

It works only on the month and day part. The month and day fall between the
2 dates, but it displays all the years.

Don't format the dates. The Format function always returns
a string and string comparisons are different from date
comparisons (dictionary vs. numeric). The DateSerial
function returns a date value and will compare properly with
other date values:

newDate: DateSerial(Left(Conndate,2), Mid(conndate,3,2),
Right(conndate,2)

and

Between DateAdd("d",-7,Date()) And DateAdd("d",-2,Date())