Null date parameters 2000

[SwEdIsH_OfFiCe_UsEr]

I'm making a query including setting two dates as parameters, and I want to
return all values if the space is left blank (i.e. if start value left blank
all up to end values, and if both are blank I want all posts). How do I do
this?

I found another post which supplied a solution, but I'm running Access 2000,
and the "Where" function doesn't seem to work for me.

Thankful for replies

 

[SwEdIsH_OfFiCe_UsEr]

Thankz, that took care of that problem. But now I have another:
Two criterias from different columns interfere with one another. If I remove
on of them, the other one works, but when I have both of them, only the
second works. Why?


WHERE (((dateField) Between Nz([StartDate],#1/1/1900#) And
Nz([EndDate],#1/1/2100#)) AND ((someField) Is Null)) OR (((someField) Like
"*" & [someParam] & "*"));

 

[Stefan Hoffmann]

hi,

Two criterias from different columns interfere with one another. If I remove
on of them, the other one works, but when I have both of them, only the
second works. Why?

Check the parentheses around your new conditions. I assume you need to
embed them into an extra level.

WHERE (((dateField) Between Nz([StartDate],#1/1/1900#) And
Nz([EndDate],#1/1/2100#)) AND ((someField) Is Null)) OR (((someField) Like
"*" & [someParam] & "*"));

E.g.

WHERE ...
AND
(
IsNull(someField)
OR
someField LIKE "*" & someParam & "*"
)


mfG
--> stefan <--