Using command button to manipulate field on report (1 Viewer)

Finance · Jul 17, 2018

I need help omitting a field from a report upon selecting a macro, using an expression or code.

Report has a record source and the user may not require the filed sometimes. so I would like to have the field not displayed in the report through selecting a command.

The field is a 'Fee' field and the cost gets added to the totals column.

Summary,
On selecting the comman button,

Fee field should not be visible or become 0 and totals column should subtract the value.

I have tried using the set value macro to set a value for that field but its not working out.

I have alse tried coding the filed to not be visible and the the totals column to update the loss of that value. the code is as follows:

Code:

Primary sub CmdSmallEntity_Click()
   Set me.SmallEntity.Value.visible= false
   if me.SmallEntity.value.visible = false then
       me.TotalCosts.Value = me.TotalCostsValue - me.SmallEntity.value
   end if

end sub

Please help

June7 · Jul 17, 2018

Suggest you use a conditional expression in query or textbox, not VBA.

On what condition should the field not be reported - user arbitrary discretion? Really should be based on some data.

Finance · Jul 17, 2018

the entity may or may not be a small entity and this is a standard cost database. If the entity is a small entity, the cost will be included other it is to be excluded. But since that is on case to case basis, I want the user to be able to decide.

Can you give me an example of the query or textbox ?

June7 · Jul 17, 2018

So one user could decide whether to include the field for an entity but another user could decide the opposite for same entity? Sounds rather bizarre but whatever.

Is the report for only a single 'entity'?
Could have an UNBOUND control (maybe checkbox) on form. Then expression references that control.

IIf(Forms.formname.checkbox, [SmallEntity], 0)

Finance · Jul 17, 2018

Yes, The idea being that for the sake of the report, the field is not visible in case the company whose cost is being evaluated is not a small entity.

June7 · Jul 17, 2018

Okay, can return Null instead of 0 for display purpose. However, arithmetic with Null results in Null. So expression to conditionally subtract the field:

=[TotalCosts] - IIf(Forms.formname.checkbox, [SmallEntity], 0)

Finance · Jul 17, 2018

One question:

I am a new user so this may be stupid but which event should i put the expressions in on the report ? on the Report's On load event ?

Finance · Jul 18, 2018

Im doing something wrong as its not working.

I have put in the arithmetic formula in the on load event of the report but theres no change in the report results.

Please help.:banghead:

Mark_ · Jul 18, 2018

Just to be clear, in cases where there IS a fee in your database, you want an end user to be able to generate a report that intentionally fails to include a value that may have been billed for?

Just to be clear you are trying to not follow standard accounting procedures on a case by case basis for reports?

Reason I'm asking, person 1 runs the report that includes fees. They get an amount. At the same time person 2 runs the SAME report but excludes fees. You now have two reports on the same data for the same time that show different totals.

I am with June7 on this. This should come from a system configuration that will ALWAYS either allow or deny the use of "Fees" based on the entity configuration. If it is changed to allow fees, then you should NEVER be able to undo that option. Otherwise you get to report two sets of numbers from the same system for the same dataset in the same report.

All my business and accounting classes say that is a horribly bad idea as you can get into a LOT of trouble for not reporting fees.

Finance · Jul 18, 2018

Your points are valid but these aren't Invoiced fees. The fees are a list of patent fees generated from the web and the report is used for strategy purposes.
if the end user wants to know the patent filing costs over a couple of years the report will be used to reflect those costs. The total has to be changed depending on if the entity is a small entity or a large entity. If the entity is a small entity, only the patent costs relating to the small entity should form part of the total and not the large entity costs. I want the report to be the same just the two columsn interchanged as per selection.

Mark_ · Jul 18, 2018

Forecasting report makes much more sense.

You have several options, the easiest of which is to make a copy of the report and remove the fees. This would be called by the "Small Entity" button while the normal report is called by the "Large entity" button. This also means you could customize spacing and title as they are two separate reports running off of the same source data.

If your data is normalized you will be totaling your "TotalCosts" for each line item based off of the totals involved and not having it as a saved value. If this is true then you simply omit the "Fees" when you do your total.

If you ARE saving a "TotalCosts", then you will want to read up a bit on data normalization as this would normally be a calculated field generated at run time and not saved.

Using command button to manipulate field on report (1 Viewer)

Finance

Registered User.

June7

AWF VIP

Finance

Registered User.

June7

AWF VIP

Finance

Registered User.

June7

AWF VIP

Finance

Registered User.

Finance

Registered User.

Mark_

Longboard on the internet

Finance

Registered User.

Mark_

Longboard on the internet

Similar threads

Users who are viewing this thread