How to hide fields if equal to zero (1 Viewer)

Tor_Fey

Registered User.
Local time
Today, 12:30
Joined
Feb 8, 2013
Messages
121
Good Day all;
Is it possible to hide fields on a report if they equal zero?


I have a currency field on my report called diff, what I want to do is hide other fields on my report should this field be equal to zero.

This is the code I have for the ‘On Load’ event of my report:

If Me.diff = 0 Then
Me.apd_no.Visible = False
Me.apd_name.Visible = False
Me.period.Visible = False
Me.Label48.Visible = False
Me.duefromfigures.Visible = False
Me.Label49.Visible = False
Me.duty_declared_st.Visible = False
Me.Label51.Visible = False
Me.diff.Visible = False
Me.Line53.Visible = False
End If

But unfortunately it isn’t working, would someone be able to give me a pointer as to where I have gone wrong?

Kind Regards
Tor Fey
 

Minty

AWF VIP
Local time
Today, 12:30
Joined
Jul 26, 2013
Messages
10,354
Try moving the code to the on format event.
 

Tor_Fey

Registered User.
Local time
Today, 12:30
Joined
Feb 8, 2013
Messages
121
Try moving the code to the on format event.

Hi Minty;

My report does not seem to have an 'On Format' event?

Please see attached pic.

Regards
Tor Fey
 

Attachments

  • report_events.png
    report_events.png
    9.7 KB · Views: 92

CJ_London

Super Moderator
Staff member
Local time
Today, 12:30
Joined
Feb 19, 2013
Messages
16,553
take a look at the events for each section, you'll find them there

Note that onformat works in report view and onpaint for print preview (or the other way round, can't remember)
 

Tor_Fey

Registered User.
Local time
Today, 12:30
Joined
Feb 8, 2013
Messages
121
take a look at the events for each section, you'll find them there

Note that onformat works in report view and onpaint for print preview (or the other way round, can't remember)

Hi CJ

I have my code, on the 'On Format' of each section, but still it doesn't work, the field is a currency field, my code won't let me put 0.00 or £0.00

All I want to do is prevent; records showing if the 'diff' field is equal to zero.

The fields aren't calculated from a query, but rather when the report opens, otherwise I would simply put >0 as the criteria, unfortunately I don't have this luxury as this report is bespoke.

Regards
Tor Fey
 

Minty

AWF VIP
Local time
Today, 12:30
Joined
Jul 26, 2013
Messages
10,354
What values are actually in your reports underlying query - sometimes currency fields are rounded so that 0.00 might actually be 0.00023 which does not = 0?
 

Tor_Fey

Registered User.
Local time
Today, 12:30
Joined
Feb 8, 2013
Messages
121
What values are actually in your reports underlying query - sometimes currency fields are rounded so that 0.00 might actually be 0.00023 which does not = 0?

Minty;

I have 3 fields in my report as follows:

  • duefromfigures
  • duty_declared_st
  • diff
the diff field is setup as a currency field as follows:

  • =[duefromfigures]-[duty_declared_st]
So what happens is when the figures from 'duty_declared_st' don't match the figures from 'duefromfigures' the report is showing the difference in the 'diff' field. so really the value should be zero.

Kind Regards
Tor Fey
 

CJ_London

Super Moderator
Staff member
Local time
Today, 12:30
Joined
Feb 19, 2013
Messages
16,553
try

=round([duefromfigures],2)-round([duty_declared_st],2)

OR

=round([duefromfigures]-[duty_declared_st])
 

Tor_Fey

Registered User.
Local time
Today, 12:30
Joined
Feb 8, 2013
Messages
121
try

=round([duefromfigures],2)-round([duty_declared_st],2)

OR

=round([duefromfigures]-[duty_declared_st])

Hi CJ;

This isn't working either; the report is still show all the data that contains 0.00?

Thanks
Tor Fey
 

Minty

AWF VIP
Local time
Today, 12:30
Joined
Jul 26, 2013
Messages
10,354
Go back to your reports query - what happens if you put >0 in the criteria for diff? then run the query? DO you get the expected results there?
 

Tor_Fey

Registered User.
Local time
Today, 12:30
Joined
Feb 8, 2013
Messages
121
Go back to your reports query - what happens if you put >0 in the criteria for diff? then run the query? DO you get the expected results there?

Hi Minty;

The field 'duefromfigures' is made up of the following none visible fields on the report with the following criteria: '=Nz([lowA])+Nz([stdA])+Nz([lowB])+Nz([stdB])+Nz([lowC])+Nz([stdC])+Nz([lowD])+Nz([stdD])+Nz([udecs_st])-Nz([odecs_st])'

The field 'duty_declared_st' comes from the query

and the field 'diff' is made up with the following criteria '=[duefromfigures]-[duty_declared_st]' on the report only, not within the query.

Can I build an expression in the query with something like: Diff: [Reports]![rpt_details_apd_amountdif]![duefromfigures]-[Reports]![rpt_details_apd_amountdif]![duty_declared_st]?
 

Minty

AWF VIP
Local time
Today, 12:30
Joined
Jul 26, 2013
Messages
10,354
No - move all the calculations into the query, much more efficient and much easier to see what's going on.
You may need to repeat the line above to get your difference - as you can't normally refer to a calculated field within the same query as it's created e.g.

Diff =(Nz([lowA])+Nz([stdA])+Nz([lowB])+Nz([stdB])+Nz([lowC])+Nz([stdC])+Nz([lowD])+Nz([stdD])+Nz([udecs_st])-Nz([odecs_st])) - duty_declared_st

I suspect that's why your formatting isn't working, i think Diff won't be calculated until the report is formatted or printed.
 

MarkK

bit cruncher
Local time
Today, 05:30
Joined
Mar 17, 2004
Messages
8,178
Are you using the format event of the section that contains the controls to hide?

What code are you using the hide the controls?
 

Tor_Fey

Registered User.
Local time
Today, 12:30
Joined
Feb 8, 2013
Messages
121
No - move all the calculations into the query, much more efficient and much easier to see what's going on.
You may need to repeat the line above to get your difference - as you can't normally refer to a calculated field within the same query as it's created e.g.

Diff =(Nz([lowA])+Nz([stdA])+Nz([lowB])+Nz([stdB])+Nz([lowC])+Nz([stdC])+Nz([lowD])+Nz([stdD])+Nz([udecs_st])-Nz([odecs_st])) - duty_declared_st

I suspect that's why your formatting isn't working, i think Diff won't be calculated until the report is formatted or printed.

Minty;

Thanks for all your help; putting all the calculations in queries works real well, had to build 3 queries to accomplish what was needed, but everything is working as I expect. The 'diff' field is set to <>0 in the query; which is showing me the negative and positive figures I was hoping to see.

A lesson learnt well there on my part; I will no longer try to do calculations outside of a query; when building a report.

Kind Regards
Tor Fey
 

Minty

AWF VIP
Local time
Today, 12:30
Joined
Jul 26, 2013
Messages
10,354
Excellent - glad it's all worked out.
 

isladogs

MVP / VIP
Local time
Today, 12:30
Joined
Jan 14, 2017
Messages
18,186
I realise you've already solved this but have you tried using conditional formatting setting the fore colour equal to the back colour when the field value is zero
 

Users who are viewing this thread

Top Bottom