Web Development

Ask something
Discussion Locked

MS Access reports/queries

0 helpful
9 replies

by frey_ca

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.
Using access, one of my fields is a yes/no field. If the answer is "yes", then I'd like to output different information than if "no". To make it user friendly, I'd like to output the same report, only hiding the fields I don't need. I have been able to criteria=no, and eliminate the data, but the users don't want to see the empty field names on the report either. Can I do this with panels or some other code? This one is urgent for me!!! Thank you so much.

9 replies

by peyison

You can add code to the Format event of the Detail section on a report to do this.

Right Click in the Detail section, select Build Event, and select Code Builder.

The code would look something like:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)

If tbxCriteria = False Then
tbxField1.Visible = False
tbxField2.Visible = False
End If
End Sub

What this says is if the yes/no field (called tbxCriteria) is no, or false, then hide the two fields. You could also write the code like:

tbxField1.Visible = tbxCriteria
tbxField2.Visible = tbxCriteria

Or, to hide fields when the criteria is True, use:

tbxField1.Visible = Not tbxCriteria

Hope this helps

by donq

Another thing you can do is use the button that calls the report to actually open a small form where the user selects the yas/no value. The yes/no fields after update event can then call one of two queries. One builds the report with yes values, the second with no. Each report is based on the appropriate query. This way you eliminate white space method 1 carries with it.

If your Yes/No form is named MyYN place the following in each queries Yes/No Criteria area: [Forms]![MyYN].[Yes/No Field Name]

Good Luck

by seagull data services

You can create a dummy field in sql using the if function
select..., if (thefield = true, "YES IT IS","Oh My God it Isn't!") as dummyfield,...

If you output the results of the field [dummyfield] under in the required place on your report you candisplay whatever you want as the result.

by seagull data services

You can create a dummy field in sql using the if function
select..., if (thefield = true, "YES IT IS","Oh My God it Isn't!") as dummyfield,...

If you output the results of the field [dummyfield] under in the required place on your report you candisplay whatever you want as the result.

This discussion is closed to new replies.