Query with 2 different critieria on same field 3

[netrusher]

Below is the SQL of a Query I am working on. The Pending Date Field has
criteria for is null and I also want to do a date range on the same
field. I cannot get this to work. If I use the Date Range and the criteria
I do not get the results. If I take out the date range the query works.
If I take out the is null critera the query works except of course
I get records for the Pending Date Field that I don't want because I only
want the record if that field is null.

HELP! This is driving me crazy.

SELECT ECNBCNVIPtbl.[ECN Number], ECNDetailtbl.[ECN Description], ECNBCNVIPtbl.[Release Date], ECNBCNVIPtbl.[ECN Analyst]
FROM ECNBCNVIPtbl INNER JOIN ECNDetailtbl ON ECNBCNVIPtbl.[ECNBCNVIP ID] = ECNDetailtbl.[ECNBCNVIP ID]
WHERE (((ECNDetailtbl.[Pending Date]) Between [Forms]![EcnVisualStatusFRM]![StartDateTxt] And [Forms]![EcnVisualStatusFRM]![EndDateTxt]) AND ((ECNDetailtbl.[Pending Date]) Is Null) AND ((ECNBCNVIPtbl.[Do Not Process])="yes"))
ORDER BY ECNBCNVIPtbl.[ECN Number], ECNBCNVIPtbl.[Release Date], ECNBCNVIPtbl.[ECN Analyst];

 

[KenReay]

WHERE (ECNDetailtbl.[Pending Date] Between [Forms]![EcnVisualStatusFRM]![StartDateTxt] And [Forms]![EcnVisualStatusFRM]![EndDateTxt] OR ECNDetailtbl.[Pending Date] Is Null) AND ECNBCNVIPtbl.[Do Not Process]="yes"

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[RivetHed]

The field can't be null AND between two dates, it's either or.

 

[PHV]

WHERE (ECNDetailtbl.[Pending Date]) Between [Forms]![EcnVisualStatusFRM]![StartDateTxt] And [Forms]![EcnVisualStatusFRM]![EndDateTxt] OR ECNDetailtbl.[Pending Date] Is Null) AND ECNBCNVIPtbl.[Do Not Process]="yes"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[netrusher]

Thanks for your help!

 

[PHV]

OOps, my suggestion had a typo ...