Using Text Box to filter Report

[adrienne_r30]

I have been on the simplest thing for so long and I am going crazy!

I have a text box in a report that was calculated,

=IIf([RemainingGrantFunds]<([Grant_Amount]*0.2),"1","0")

So, if certain criteria are met, either a 1 or a 0 will be shown.

Then in my OpenReport VBA I have,

DoCmd.OpenReport stDocName, acPreview, , "ExpiryTag = 1"

'ExpiryTag' is the name of the text box from above.

When it equals 1, I want the report to show those records. I have done this for other reports and it worked so easily, and for some reason it just doesn't want to work on this report. I believe the difference is that the text box is calculated in the report and not in the query (for this report I can not calculate it in the query). I think it might have something to do with the output of the 1 and 0

ie. "1" versus '1' or just 1.

I have also tried every iteration of "ExpiryTag = 1"

ie. "ExpiryTag = '1'" or "[ExpiryTag] = 1" etc.

Can someone please help, I am going crazy!

 

[plog]

You're correct in why you can't do it. Well, on one level at any rate. ExpiryTag needs to be part of the recordsource the Report is built on to use it as criteria.

I think that you are incorrect in thinking that you can't make ExpiryTag part of the query. Why do you think that?

 

[pbaldy]

Move that calculated field to the report's query and it should work. It won't work on a textbox like that.

 

[adrienne_r30]

AHHH, I got it. Thanks so much guys. I figured out how to add it to the query and it worked perfectly! Awesome.

 

[pbaldy]

Plog and slow-fingers-Baldy were happy to help.