return record with blank in one of its field

[jer]

I have imported an excel file on which I am trying to perform a query. There
are some blank fields (cells) and when I run the query the result is not
picking up the rows with the blank fields. The query is as follows

SELECT Test.DATE, Test.CCY, Test.fcyd, Test.fcyc, Test.TXN, Test.REF,
Test.RATE, IIf([fcyd]<>0,[fcyd]+0,[fcyc]+0) AS [Check]
FROM Test INNER JOIN [Daily Rates] ON Test.DATE=[Daily Rates].DATE
WHERE (((Test.DATE)=[Enter Date]) And ((Test.CCY)=1) And ((Test.TXN)<>8762
And (Test.TXN)<>9465) And
((Test.REF)=IIf(Len([REF])<>10,[REF],IIf(Len([REF])=10 And
Left([REF],2)<>"10",[REF]))) And ((Test.RATE)=[Daily Rates]!MID Or
(Test.RATE)=[Daily Rates]!ROUND Or (Test.RATE)=[Daily Rates]!PMID Or
(Test.RATE)=[Daily Rates]!PROUND))
ORDER BY IIf([fcyd]<>0,[fcyd]+0,[fcyc]+0);

The problem is with ((Test.REF)=IIf(Len([REF])<>10,[REF],IIf(Len([REF])=10
And Left([REF],2)<>"10",[REF]))). I would like the query to return records
were test ref is blank as well. I have tried changing the "" to 0 but still
without success. Any help suggestion would be greatly appreciated
TIA
jer

 

[Guest]

somewhere in the where clause(at the end) you need to add:
OR IS NULL

-----Original Message-----
I have imported an excel file on which I am trying to perform a query. There
are some blank fields (cells) and when I run the query the result is not
picking up the rows with the blank fields. The query is as follows

SELECT Test.DATE, Test.CCY, Test.fcyd, Test.fcyc, Test.TXN, Test.REF,
Test.RATE, IIf([fcyd]<>0,[fcyd]+0,[fcyc]+0) AS [Check]
FROM Test INNER JOIN [Daily Rates] ON Test.DATE=[Daily Rates].DATE
WHERE (((Test.DATE)=[Enter Date]) And ((Test.CCY)=1) And

And (Test.TXN)<>9465) And
((Test.REF)=IIf(Len([REF])<>10,[REF],IIf(Len([REF])=10 And
Left([REF],2)<>"10",[REF]))) And ((Test.RATE)=[Daily Rates]!MID Or
(Test.RATE)=[Daily Rates]!ROUND Or (Test.RATE)=[Daily Rates]!PMID Or
(Test.RATE)=[Daily Rates]!PROUND))
ORDER BY IIf([fcyd]<>0,[fcyd]+0,[fcyc]+0);

The problem is with ((Test.REF)=IIf(Len([REF])<>10, [REF],IIf(Len([REF])=10
And Left([REF],2)<>"10",[REF]))). I would like the query to return records
were test ref is blank as well. I have tried changing the "" to 0 but still
without success. Any help suggestion would be greatly appreciated
TIA
jer
.