Formula for a Text box

[gothung]

I am looking for a formula that will tell me the "criteria" that a query is using to lookup data. There could be multpile criterion as well. Does anybody know of a formula that could display this in a text box in my report.

 

[SydneyGeek]

I get this result a bit indirectly.

(1) I use a form with combo and text boxes to allow users to choose their filters. Clicking a command button launches the report.
(2) In the report, you can set the RecordSource to the appropriate control on the form.

 

[gothung]

I made my report the exact same way, and I have tried your suggestion. The only problem is that it shows me too much data. If only two of the potential 5 filters are being used, I only want to see that data. I only want to see what was selected, nothing extra.

 

[SydneyGeek]

A bit roundabout, but you could hide those textboxes and have others that report on them...

Say txtParam1 is hidden and based on a Text data type.
txtShowParam1 could have the calculation:
Iif([txtParam1]>"A","First Filter is " & [txtParam1],"")
Repeat for the others and place them in the desired location on the report.

 

[gothung]

This may sound stupid, but I can't get that formula to work. I think that there is something that I am missing, but I do not know what it is. If the data fields that I want to see are called "Dept", "Class", "Type", "Geography", and "Platform". They are named the same in all tables, queries, and reports. But these are the names of the filters that I am using. Any help you could give me would be great.

Thanks

 

[SydneyGeek]

IF you got to the point where all the details were coming through onto the report (last Friday's reply) but couldn't get the next step to work, you may have followed me literally and left out the = sign -- I've done that a couple of times myself, and then the text box doesn't work...
If it wasn't that simple, let me know and I can give you more detail.

Denis

 

[gothung]

I did have the = sign in there. What I am finding is that it is still showing all the information. I'm sure it is just something small that I am doing wrong, but if you could point me in the right direction, that would be great.

Thanks

 

[SydneyGeek]

Just had another thought. What I suggested before was showing everything, regardless of whether the source textboxes were hidden -- sometimes I wonder about myself

What you really want to do is hide the reporting text boxes if the source ones contain no data, right?

This will require a bit of code in the OnFormat event for the report -- this is where you can define where controls are located, and their formatting.
Go into design view in the report. Presumably the controls that you want to format are in the header section -- either Report or Page. Select this section (click the section divider), go to its properties, and click the Events tab. One of the lines will be OnFormat.
Click in there, then click the Builder (...) button that appears at the right of the line. Select Code in the options that pop up, and you will be in a module.

Assume that your display textboxes are Display1...Display5, and they grab their info from Source1...Source5

Try this code for each control--
[Display1].[Visible]=Eval([Source1].[Value]>"A") 'if [Source1] is text, or

[Display1].[Visible]=Eval([Source1].[Value]>0) 'if [Source1] is numeric

Not in front of Access at the moment so I can't test it, but I think this will do the job.

Hope that helps

Denis

 

[gothung]

Thanks for the help with the code, but I keep getting errors. The one thing in Access that I am not very strong in is code. Perhaps I am doing it wrong. But there are 5 text boxes that I want to see if they are being filtered on. (They are Text439, Text495, Text497, Text499, Text501). The control sources are (Dept, Class, Type, Geography, Platform) respectively. If this helps you explain the code in further detail, that would be great.

Thanks