Hide a Row @ Report ( Access 2010 )

[AccessLearner73]

Hi,

Im trying to hide a rows where there is now value.

Data is coming from a query.

Row 2 of detail section has 3 fields ( Site2, SitName2, Utilization2)

I need to hid this row if site2=null & sitename2 = null & utilization2=0

Thanks for help.

[PhilOfWalton]

Try setting all 3 fields to CanShrink and CanGrow to true
Set the report detail to CanShrink and CanGrow to true

on the OnFormat of the detail, use something like

 
    Site2.Visible = 
 

[PhilOfWalton]

Sorry, pressed the wrong key

   If Nz(Site2) = 0 then
       Site2.Visible = False
   Else
    Site2.Visible = True
   End If
 
   If Nz(SitName2) = 0 then
       SitName2.Visible = False
   Else
    SitName2.Visible = True
   End If
 
   If Nz(SUtilization2) = 0 then
       Utilization2.Visible = False
   Else
    Utilization2.Visible = True
   End If
 

Phil

[AccessLearner73]

Thanks Phil,

Did that all but no effect at 2nd row. It remains there.

Please refer to attached images.

Regards.
SAM
Slide1:

Slide2:

Slide3:

Slide4:

Slide5:

[PhilOfWalton]

Sorry, Sam, the images are too fuzzy to see in detail.

However it looks as if you have Combo boxes on your report which shouldn't be there as data in your report is static - not relevant to your problem, but a point just the same.

In Slide5.jpg, what is in the column on the right?

Have you set the Report Detail to CanShrink & CanGrow to True?

Phil

[AccessLearner73]

Hi,
Yes Report Detail Can Shrink & Can Grow are set to "Yes".

The right column is Utilization2 to Utilization6, as i actually have 5 rows to hide/show depending on data in there. in my question i have mentioned row 2 only to keep it simple.

I'v now replaced combos with text box & visible=false is working but for last column "Utilization2" it's not working. It's a numeric field does that matter for Nz().

Regards,
SAM

[PhilOfWalton]

Hi Sam

If a field is numeric, and you want to see if it is Null, you use

   If Nz(MyField) = 0 then .......
 

If a field is Text, and you want to see if it is Null, you use

   If Nz(MyField) = "" then .......
 

It appears that in Slide5, the Utilization2 is 0.
Are you really sure this is a numeric field, and not a text field with "0" in it?

You could try, in order to cover both possibilities,

If Nz(MyField) = 0 Or Nz(MyField) = "" then .......
 

but that really shouldn't be necessary.

If that doesn't work, as an experiment, remove the Utilization2 from your report and see what happens.

Phil

[AccessLearner73]

Adjusted but no effect. Problem lies with Utilization
I removed the field & rest was working perfectly.

[PhilOfWalton]

Not sure what the problem is.

Can you strip any sensitive information from your Db and either send me a copy directly, or post it on Bytes
Phil

[AccessLearner73]

Hi,

DB enclosed
report name is Qr_Rpt_SiteList

[PhilOfWalton]

Sam

Can I suggest that at the head of each module (and any new ones that you create) you have

Option Compare Database
Option Explicit
 

You already have Option Compare Database,
but the Option Explicit ensures that any items referenced in your code do really exist.

When you Compile this you will get a number of errors.
Please correct them and if you are not getting the required results, attach the revised DB and we will have another look.

Phil

[zmbd]

AccessLearner73
Welcome to Bytes.com

1) Just to give you some guidance on what Phil is asking you to do please read the Section A section of the following: https://bytes.com/topic/access/answe...on#post3803251

You may need to repeat the Debug\compile step several times, fixing each error found in turn, until this step completes without error.

2) Just to clarify, You want the controls on the report to be hidden and the remaining record information shown? I'd look at the report result; however, the code will not compile.

[AccessLearner73]

Thanks ZMBD.

I am sure Phil is trying to help me but after adding Option Explicit to code i am getting errors i don't even know what to do with them. Still struggling. Hope it works.

[PhilOfWalton]

Sorry about that, AccessLearner73, That's exactly what Option Explicit is supposed to do.

For example, if you have a control on a form or report called "My Name" and in the code you call it "MyName" (without the space), the compiler will give an error.

You will need to use a Dim statement to declare all variables in your code, and the compiler will then throw an error if you refer to a variable that you haven't declared.

So similarly, in your code, without the Option Explicit statement you write something like

[MyDate] = Now()
 

and somewhere else you write

[My Date] = Now
 

, you won't get an error.

Using the Option Explicit, within your Function or Sub, somewhere you will need to have a statement

Dim MyDate as Date
 

Then the compiler will be happy with the first bit of code, but throw an error on the second example.

Sorry, I know it's a pain, but as zmbd says, it's just something you have to work through. The chances of your DB working reliably without a "Clean Compilation" are pretty low.

Phil