two null fields

  • Thread starter Thread starter Access WannaBe
  • Start date Start date
A

Access WannaBe

I have a query that has two fields, [sales] and [value]. I want to exclude
records if both these fields are null. what is the best way?

I did create a field

filter: IIf(IsNull([Extended LCost]) And IsNull([Extended Sales]),0,1)

that yields what I want, but if I put criteria in for the field access
gives me a rude message that I've screwed something up.

Any help is appreciated very much,
Chad
 
but if I put criteria in for the field access gives me a rude message that
I've screwed something up.
What criteria did you use and what was the 'rude' message'?

In your IIF statement the results would be numeric values and if you put
quotes around a number for criteria that made it text and therefore a data
mismatch.
 
:

In your IIF statement the results would be numeric values and if you put
quotes around a number for criteria that made it text and therefore a data
mismatch.
Click to expand...

I've tried ' >0 ' and 'Not 0'.

The message I get says

You tried to execute a query that does not include the specified expression
' IIf(IsNull([Extended LCost]) And IsNull([Extended Sales]),"0",1)' as part
of the aggregate function.

The message is not rude....I felt it was rude when it pointed out my
ignorance. LOL
 
Ok, three things --
It seems you have totals query and will need to include the IIF statement in
the GROUP BY.

Here is corrected syntax --
IIf(IsNull([Extended LCost]) And IsNull([Extended Sales]),0,1)

Criteria --
1
no quotes or any other logic symbols.

--
Build a little, test a little.


Access WannaBe said:
:

In your IIF statement the results would be numeric values and if you put
quotes around a number for criteria that made it text and therefore a data
mismatch.
Click to expand...

I've tried ' >0 ' and 'Not 0'.

The message I get says

You tried to execute a query that does not include the specified expression
' IIf(IsNull([Extended LCost]) And IsNull([Extended Sales]),"0",1)' as part
of the aggregate function.

The message is not rude....I felt it was rude when it pointed out my
ignorance. LOL
Click to expand...
 
Back
Top