BETWEEN Problems 2

[eatwork]

Hi everyone,
I was wondering if anyone can tell me what the difference or how to solve this problem:
DB: ACCESS
VIEW: SELECT QUERY
Expression: Expr1: Format([formLoadTime],"mm/dd/yyyy") Between Format([equipmentStartDate],"mm/dd/yyyy") And Format(IIf(nz([equipmentEndDate],"")="", DateAdd('d',365,Format(Now(),"mm/dd/yyyy")), [equipmentEndDate]),"mm/dd/yyyy")

But expression like this works:
Format([formLoadTime],"mm/dd/yyyy") Between #11/01/2006# And #01/01/2007#

 

[traingamer]

Why not just:

 (((formLoadTime) Between [equipmentStartDate] And IIf(nz([equipmentEndDate],"")="",DateAdd('yyyy',1,[equipmentStartDate]),[equipmentEndDate])))

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[SkipVought]

Hi,

In the first instance you are coll ating a STRING representation of DATE starting with MONTH then DAY then YEAR...

when you really want to collate by YEAR then MONTH then DAY

The ## delimiters CONVERT your STRING representation of a date to REAL DATE which collates just fine since a REAL DATE is just a NUMBER, like today, which is 39016.

Skip,

_[red][/red]

 

[traingamer]

Sorry. Use Now() or Date() in place of second instance of [equipmentStartDate].

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[eatwork]

Hi traingamer and skivought,
thank you for your posts.

traingamer,
I will try your example

skipvought and traingamer,
is there a way to incorporate the '#' delimiters into my existing sql or into traingamers example? and does collating by year, month, day improve performance? thank you

 

[SkipVought]

Did you NOT understand the explanation?

Loose the Format

Expression: Expr1: Format([formLoadTime],"mm/dd/yyyy") Between [equipmentStartDate] And IIf(nz([equipmentEndDate],"")="", DateAdd('d',365,Format(Now(),"mm/dd/yyyy")), [equipmentEndDate])

or

Expression: Expr1: Format([formLoadTime],"yyyy/mm/dd") Between Format([equipmentStartDate],"yyyy/mm/dd") And Format(IIf(nz([equipmentEndDate],"")="", DateAdd('d',365,Format(Now(),"yyyy/mm/dd")), [equipmentEndDate]),"yyyy/mm/dd")

change the format

Skip,

_[red][/red]

 

[eatwork]

Hi traingamer,
I tried your suggestion and it returns the same result.
I have 44 records in my result set. All of the records have a start date of 01/01/2005, and one of the records has an end date of 08/15/2006, while the rest of the end dates are null. When I run the query, the IIf(nz([equipmentEndDate],"")="", DateAdd('yyyy',1,[equipmentStartDate]),[equipmentEndDate]))) portion runs correctly, placing the 10/26/2007 in all of the fields that are null, and 08/15/2006 for the one record with an end date. And the formLoadDate has a value of 10/28/06
The problem still lies in the between. It returns False for all of the records still.

 

[eatwork]

Hi SkipVought,
Thank you for your post, sorry I didn't completely understand the explanation. After further review I think I understand what you're laying down. Thanks for your help.
Your second solution worked perfectly, and now I understand why my solution did not work. Thanks again.