Filtering reports more effectively

[drybone]

I have a report (RptReqAcc) that I generate from a table (TblReqAcc).

TblReqAcc is a temporary table that only holds 1 record at a time. It has a lot of fields contained in it (which are listed below):

TblReqAcc:
FldUserName (Datatype: text)
FldRequestDate (Datatype: text)
FldDutyRoster (Text: Yes or Blank)
FldSecurityManager (Text: Yes or Blank)
FldSafetyManager (Text: Yes or Blank)
FldSupplyManager (Text: Yes or Blank)
FldTransportationManager (Text: Yes or Blank)

In Report "RptReqAcc" I would like to write an event procedure for the fields pushed to the report:

 Option Compare Database 
 Private Sub Report_Load()
 If DCount("*", "[TblReqAcc]", "[FldDutyRoster] = '" & Me.FldDutyRoster & "'") > 0 Then
     FldDutyRoster.Visible = True
     LblDutyRoster.Visible = True
 Else
       FldDutyRoster.Visible = False
       LblDutyRoster.Visible = False
 [FONT="Calibri"]End If[/FONT]
 End Sub

Is there a way to write out all of the fields listed above in one IIf DCount() statement instead of writing out each individually.

 

[drybone]

What the end results I'm going for are... If the information is blank in a field then it doesn't display on the report, if the information is there then it displays. That way the admin won't have to go through and sift thru all the areas that are blank or not. it will only show them the areas they user is requesting access to.

 

[drybone]

is this possible to do like dao.recordset but instead of doing it to the records, do it to the fields within the record?

 

[arnelgp]

why do you need to have a count on tblReqAcc when you already have it opened in the report? ofcourse it will always have a Count since your recordsource is tblReqAcc itself.

 

[drybone]

Because the report is a catch all for DB access rights. The report is generated from a table that has all the fields included.

There are roughly 20 yes/no fields that populate in the report whether they are yes or no. Yes actually populates as yes while the no's are blank in the report.

With that said, I'm trying to show the report with only the fields marked "yes" so that there isn't a bunch of extra fields populated in the report that could lead to the admin accidently giving rights to programs the user shouldn't have rights to.

I could write it out one by one for all 20 fields the way I showed in my 1st post but I was looking for a better way to write it to avoid writing that same code 20 times.

 

[drybone]

Ok, I have gotten the thing figured and also, how to condense the data to make the list continuous without large blank areas between selected fields to be generated in the report.

If DCount("*", "[TblReqAcc]", "[AdditionalDutyRoster] = '" & Me.AdditionalDutyRoster & "'") <= 0 Then
             AdditionalDutyRoster.Visible = False
             Label4.Visible = False
             AdditionalDutyRoster.Height = 0
             Label4.Height = 0
   
End If

^^^this is 1 of about 20 in the on load event. The fields had to be stacked, cangrow/canshrink selected in textbox properties, and 0" on all the padding. Somehow it still feels like I'm cheating myself out of a cleaner way of knocking this out. Any suggestions would be greatly appreciated.

Thanks!