Query criteria based on a function

[latex88]

I have a query that has a criteria that is based on a function [PrintedFunc ()]. The field (Printed) is filtered by "Yes" or "No". The report filters fine when the function returns "Yes" or "No". What I need sometimes is to print all the records, whether the record is "Yes" or "No". Apparently "*" & PrintedFunc () does not do the trick. Can anyone help with either the syntax or somehow make the function return something so that the query will pull all the records?

 

[pbaldy]

See if having the function return Null and using this technique works:

http://access.mvps.org/access/queries/qry0001.htm

 

[latex88]

Thanks for replying, Paul. The idea in the link you provided is sound, but for my function, it did not work. Perhaps it's the syntax? I tried below but received no data.

PrintedFunc() Or PrintedFunc() Is Null

 

[DavidAtWork]

How are you getting the record source for the report, does the PrintedFunc() have to be applied

David

 

[latex88]

How are you getting the record source for the report, does the PrintedFunc() have to be applied

David

Yes, PrintedFunc() is in the criteria. The current returned value is either "Yes" or "No". What I need now is "either" as an option so all will print.

 

[pbaldy]

Did you have the function return Null for all? Can you post the db here to play with?

 

[latex88]

My db is too big, so I created a simple one. As you can see in Form1, the two left buttons work fine. I just don't know, based on the query that is already written, provide all the records so that the yellow button would work. I tried to save it as .mdb but received "...cannot save...The specific database sort order, General, is not supported for the target database format.", so here is the .accdb version.

 

[pbaldy]

Worked fine for me. I had to change the variable to Variant so it would take a Null.

 

[latex88]

So, in the code of Command2 event, did you set GstrPrinted = Null? If so, then I'm baffled cuz it didn't work for me.
Did you change the datatype of the field to "Yes/No" as well?

 

[pbaldy]

Yes, the code for that button is:

Private Sub Command2_Click()
    GstrPrinted = Null
    DoCmd.OpenReport "Report1", acViewReport, "", "", acNormal
End Sub

and as I said I changed the variable declaration:

Public GstrPrinted As Variant

 

[latex88]

Ah, it works great now after I insert the criteria you had suggested from that the above thread to below. Thank you so much for the help.

PrintedFunc() Or PrintedFunc() Is Null

 

[pbaldy]

Happy to help! I had the feeling it would work, but had never tried with a function.