Solved MS Access Query IIF Function (I think)

[Dr Peter Klomp]

Hi Team,
This is my first post, so bear with me if I am not following protocol.
I am trying to write an MS Access query (not VBA) whereby, in the criteria box, I want records returned according to certain criteria.
The table is debtors and one field is InvoiceNow which is a true/false field. If any record has been checked true (which I think is -1), I want the query to run with the criteria being true. If no records have been checked true (0), then I want query criteria to be false.
So far I have this in the criteria box of the query:
Status: IIf(Sum(InvoiceNow) < 0,-1, 0)
But, you guessed it MS Access tells me the expression is invalid.
Can anyone help, please? Thank you in advance
Regards
Peter

 

[theDBguy]

Hi Peter. If InvoiceNow is a Yes/No field, why are you summing it? What happens if you just put the criteria under the InvoiceNow column?

 

[Dr Peter Klomp]

Thank you for your quick reply.
If there is more than one record with InvoiceNow = true, then I want those records listed in the query, but if no records have InvoiceNow = True then I want all the records listed. I can get the user to input criteria of -1 or 0 (true or false) but I am trying to automate things slightly.

 

[The_Doc_Man]

I am not clear on the verbal description of what you want. You are correct that when something has a YES/NO field, -1 is counted as YES or TRUE. But it is not at all obvious what you are trying to do with this information because your words seem to lead to an ambiguous intent. Often, we like to see example data of what you have, show us your intended query, and show us what you might wish to see in return. I know it might take a while to fake a bunch of records, but that is usually enough to get us going.

 

[Dr Peter Klomp]

Thanks, Doc
Ultimately, the end result is the number of invoices we send out. If one record with the field "InvoiceNow" is checked true, then we want the query to extract that record only and we will issue an invoice to that person. However, if no records have been checked true for the field "InvoiceNow" (in other words false or no) then we want the query to identify all the records because we will do bulk invoicing to everyone.
Regards Peter

 

[The_Doc_Man]

Our posts (your #3 and my #4) crossed.

First, " get the user to input criteria of -1 or 0 (true or false)" is accomplished with an ordinary check box bound to a Yes/No field, so that's easy.

Second, you have a dichotomy here (either only TRUE records if any are true, or all records if none are TRUE) but I think I have a sneaky way to do this.

SELECT patientID, patientname, ... (other fields)
FROM debtors
WHERE ( InvoiceNow = DMin( "[InvoiceNow]", "debtors" ) ) AND .... (other criteria?)  ;

The DMin will return 0 if every record is FALSE, but -1 if any record is TRUE. Then the search for matching records will do what you want, I think.

 

[Dr Peter Klomp]

Doc Man,
You are the man! the DMin function was very sneaky, and worked perfectly. I didn't even think of it. So, problem solved.
After a few hours of scatching my head, I do wonder what was wrong with my iif function. But, who cares, now? Thank you again. Cheers

 

[Pat Hartman]

Since the query should be the RecordSource for a report, I'm not sure where slick comes into play. Why not simply use:

Where InvoiceNow = True as the criteria?

Also, There is something convoluted about your processing. Rather than running some process to update records that need to be invoiced, why not just use that criteria instead of testing a flag for true? Updating records with temporal values (values that are valid only at the point in time they are set), is surely a violation of normal forms.

 

[Isaac]

After a few hours of scatching my head, I do wonder what was wrong with my iif function. But, who cares, now?

I don't normally put an expression as the source of literal criteria in the QBE, so I was curious on this. While it definitely allowed me to do that generally, whenever I used a Sum() function, I got this message "Cannot have aggregate function in WHERE clause ([my aggregate function]) "

So I guess that was the reason.

 

[Dr Peter Klomp]

Thank you everyone for your assistance and comments. My problem is solved, however, I will examine all the comments to see if there are improvements I can make to the way I query the data. Best regards.

 

[The_Doc_Man]

Concur with Isaac - Aggregate functions normally have to appear in the SELECT clause (although a HAVING clause can also use them).