"If" Statement in Form

[Guest]

When designing our sales report, if the amount in the field is equal to "0" I
prefer there to be a blank, rather than a page filled with zeros. I do this
by entering:

If Me!ProposalAmount = 0 Then
Me!ProposalAmount.Visible = 0
Else
Me!ProposalAmount.Visible = -1
End If


In my form I have a field "InvoiceTotal" and another "CGS"
The next field calculates the profit
=([InvoiceTotal]-[cgs])

It's the last field I'm having a problem with. It calculates the percentage
of profit
=([PROFIT]/[InvoiceTotal])

The calculation works great when there is a number in the invoice and cgs
fields. If the invoice and cgs fields are the default "0" an error message
is displayed.

How and WHEREdo I insert the....something like

If Me!cgs= 0 Then
Me!profitpercent.Visible = 0
Else
Me!profitpercent.Visible = -1
End If

Thank you
RMarko

 

[JethroUK©]

you can replace both if statements with

ProposalAmount.Visible = ProposalAmount


and likewise


profitpercent.Visible = profitpercent

 

[J. Goddard]

The error message is probalby due to division by 0 when [invoicetotal] is 0.

Try putting the percentage calculation into an iif function:

=iif([invoicetotal] > 0,[PROFIT]/[InvoiceTotal],0.0)

The best place for the visible / invisible decision is in the On Format
event of the applicable report section.

John

 

[Guest]

Thank you for your response. I didn't quite understand where on the "form"
to enter this expression. This is a form, not a report.

The error message is probalby due to division by 0 when [invoicetotal] is 0.

Try putting the percentage calculation into an iif function:

=iif([invoicetotal] > 0,[PROFIT]/[InvoiceTotal],0.0)

The best place for the visible / invisible decision is in the On Format
event of the applicable report section.

John

When designing our sales report, if the amount in the field is equal to "0" I
prefer there to be a blank, rather than a page filled with zeros. I do this
by entering:

If Me!ProposalAmount = 0 Then
Me!ProposalAmount.Visible = 0
Else
Me!ProposalAmount.Visible = -1
End If


In my form I have a field "InvoiceTotal" and another "CGS"
The next field calculates the profit
=([InvoiceTotal]-[cgs])

It's the last field I'm having a problem with. It calculates the percentage
of profit
=([PROFIT]/[InvoiceTotal])

The calculation works great when there is a number in the invoice and cgs
fields. If the invoice and cgs fields are the default "0" an error message
is displayed.

How and WHEREdo I insert the....something like

If Me!cgs= 0 Then
Me!profitpercent.Visible = 0
Else
Me!profitpercent.Visible = -1
End If

Thank you
RMarko

 

[John Vinson]

Thank you for your response. I didn't quite understand where on the "form"
to enter this expression.

In the Control Source property of a textbox on the form.

This is a form, not a report.

They work in exactly the same way, in this respect at least.

John W. Vinson[MVP]

 

[J. Goddard]

Sorry, I saw only the "sales report"; didn't realize it was a form.

You'll have to put the code into the "after update" event of one of the
fields the users fill in. If there are none (i.e. it's just a reporting
form), you can put the code in the "on open" event of the form, or if
there are a number of records the form can display, the "on current" event.

John

Thank you for your response. I didn't quite understand where on the "form"
to enter this expression. This is a form, not a report.

:

The error message is probalby due to division by 0 when [invoicetotal] is 0.

Try putting the percentage calculation into an iif function:

=iif([invoicetotal] > 0,[PROFIT]/[InvoiceTotal],0.0)

The best place for the visible / invisible decision is in the On Format
event of the applicable report section.

John


R Marko wrote:

When designing our sales report, if the amount in the field is equal to "0" I
prefer there to be a blank, rather than a page filled with zeros. I do this
by entering:

If Me!ProposalAmount = 0 Then
Me!ProposalAmount.Visible = 0
Else
Me!ProposalAmount.Visible = -1
End If


In my form I have a field "InvoiceTotal" and another "CGS"
The next field calculates the profit
=([InvoiceTotal]-[cgs])

It's the last field I'm having a problem with. It calculates the percentage
of profit
=([PROFIT]/[InvoiceTotal])

The calculation works great when there is a number in the invoice and cgs
fields. If the invoice and cgs fields are the default "0" an error message
is displayed.

How and WHEREdo I insert the....something like

If Me!cgs= 0 Then
Me!profitpercent.Visible = 0
Else
Me!profitpercent.Visible = -1
End If

Thank you
RMarko

 

[Guest]

Hi,

'Scuse me butting in but I'm curious about how you blank out zeros on your
report. Is there any reason you cannot set the Format property of
ProposalAmount to #,##0.00;-#,##0.00;" " ?

Regards,

Rod

 

[Guest]

I'm very sad to say that none of your suggestions have worked. I've used the
formula is every place I can think of on the page with no success. Any
suggestions?

Once again......
When you open a record in this contact mgmt database, there is no financial
information....everything defaults to "$0.00"

At the end there are 4 fields:

INVOICEAMOUNT CGS PROFIT PERCENTPROFIT

Invoice amount is typed in
Cost of goods sold amount is typed in

Profit is calculated =([INVOICEAMOUNT]-[CGS])

Percent is calculated =([PROFIT]/[INVOICEAMOUNT])

All calculations work perfectly. Whenever we enter an amount in Invoice and
CGS, the profit and percent profit display perfectly.
However, when we open a file, we don't have an invoice amount yet, so we
default everything to $0.00.

Profit% field displays #Num!
How can I get rid of this?
Please help!
Rhonda

Hi,

'Scuse me butting in but I'm curious about how you blank out zeros on your
report. Is there any reason you cannot set the Format property of
ProposalAmount to #,##0.00;-#,##0.00;" " ?

Regards,

Rod

When designing our sales report, if the amount in the field is equal to "0" I
prefer there to be a blank, rather than a page filled with zeros. I do this
by entering:

If Me!ProposalAmount = 0 Then
Me!ProposalAmount.Visible = 0
Else
Me!ProposalAmount.Visible = -1
End If


In my form I have a field "InvoiceTotal" and another "CGS"
The next field calculates the profit
=([InvoiceTotal]-[cgs])

It's the last field I'm having a problem with. It calculates the percentage
of profit
=([PROFIT]/[InvoiceTotal])

The calculation works great when there is a number in the invoice and cgs
fields. If the invoice and cgs fields are the default "0" an error message
is displayed.

How and WHEREdo I insert the....something like

If Me!cgs= 0 Then
Me!profitpercent.Visible = 0
Else
Me!profitpercent.Visible = -1
End If

Thank you
RMarko

 

[J. Goddard]

The #Num is due to division by zero in the calculation.
Instead of defaulting the value of profit and percentprofit to the
calculated values, why not default them to 0.0, then calculate the
values in the After Update events of the InvoiceAmount and/or CDS?

It's worth noting that even though Invoiceamount and CGS might have
values, PercentProfit of zero might still be a perfectly valid value.
(not that that is a very good way to run a business - but that's another
issue entirely!), so hiding it in those circumstances might not be a
good idea.

Hope this helps

John

I'm very sad to say that none of your suggestions have worked. I've used the
formula is every place I can think of on the page with no success. Any
suggestions?

Once again......
When you open a record in this contact mgmt database, there is no financial
information....everything defaults to "$0.00"

At the end there are 4 fields:

INVOICEAMOUNT CGS PROFIT PERCENTPROFIT

Invoice amount is typed in
Cost of goods sold amount is typed in

Profit is calculated =([INVOICEAMOUNT]-[CGS])

Percent is calculated =([PROFIT]/[INVOICEAMOUNT])

All calculations work perfectly. Whenever we enter an amount in Invoice and
CGS, the profit and percent profit display perfectly.
However, when we open a file, we don't have an invoice amount yet, so we
default everything to $0.00.

Profit% field displays #Num!
How can I get rid of this?
Please help!
Rhonda



:

Hi,

'Scuse me butting in but I'm curious about how you blank out zeros on your
report. Is there any reason you cannot set the Format property of
ProposalAmount to #,##0.00;-#,##0.00;" " ?

Regards,

Rod

:

When designing our sales report, if the amount in the field is equal to "0" I
prefer there to be a blank, rather than a page filled with zeros. I do this
by entering:

If Me!ProposalAmount = 0 Then
Me!ProposalAmount.Visible = 0
Else
Me!ProposalAmount.Visible = -1
End If


In my form I have a field "InvoiceTotal" and another "CGS"
The next field calculates the profit
=([InvoiceTotal]-[cgs])

It's the last field I'm having a problem with. It calculates the percentage
of profit
=([PROFIT]/[InvoiceTotal])

The calculation works great when there is a number in the invoice and cgs
fields. If the invoice and cgs fields are the default "0" an error message
is displayed.

How and WHEREdo I insert the....something like

If Me!cgs= 0 Then
Me!profitpercent.Visible = 0
Else
Me!profitpercent.Visible = -1
End If

Thank you
RMarko

 

[Guest]

John,
You win the "THINK OUTSIDE THE BOX" Award. I was so consumed with figuring
out how to program the form to do what I needed it to do, I didn't even think
to program the "CGS" field. What I entered in "after update"

Private Sub CGS_AfterUpdate()

Me!Profit = ([INVOICEAMOUNT] - [CGS])
Me!ProfitPercent = ([Profit] / [INVOICEAMOUNT])

End Sub

So simple, I will the big "DUH" Award
WORKS GREAT!!!! THANK YOU SO MUCH.

Rhonda

The #Num is due to division by zero in the calculation.
Instead of defaulting the value of profit and percentprofit to the
calculated values, why not default them to 0.0, then calculate the
values in the After Update events of the InvoiceAmount and/or CDS?

It's worth noting that even though Invoiceamount and CGS might have
values, PercentProfit of zero might still be a perfectly valid value.
(not that that is a very good way to run a business - but that's another
issue entirely!), so hiding it in those circumstances might not be a
good idea.

Hope this helps

John

I'm very sad to say that none of your suggestions have worked. I've used the
formula is every place I can think of on the page with no success. Any
suggestions?

Once again......
When you open a record in this contact mgmt database, there is no financial
information....everything defaults to "$0.00"

At the end there are 4 fields:

INVOICEAMOUNT CGS PROFIT PERCENTPROFIT

Invoice amount is typed in
Cost of goods sold amount is typed in

Profit is calculated =([INVOICEAMOUNT]-[CGS])

Percent is calculated =([PROFIT]/[INVOICEAMOUNT])

All calculations work perfectly. Whenever we enter an amount in Invoice and
CGS, the profit and percent profit display perfectly.
However, when we open a file, we don't have an invoice amount yet, so we
default everything to $0.00.

Profit% field displays #Num!
How can I get rid of this?
Please help!
Rhonda



:

Hi,

'Scuse me butting in but I'm curious about how you blank out zeros on your
report. Is there any reason you cannot set the Format property of
ProposalAmount to #,##0.00;-#,##0.00;" " ?

Regards,

Rod

:


When designing our sales report, if the amount in the field is equal to "0" I
prefer there to be a blank, rather than a page filled with zeros. I do this
by entering:

If Me!ProposalAmount = 0 Then
Me!ProposalAmount.Visible = 0
Else
Me!ProposalAmount.Visible = -1
End If


In my form I have a field "InvoiceTotal" and another "CGS"
The next field calculates the profit
=([InvoiceTotal]-[cgs])

It's the last field I'm having a problem with. It calculates the percentage
of profit
=([PROFIT]/[InvoiceTotal])

The calculation works great when there is a number in the invoice and cgs
fields. If the invoice and cgs fields are the default "0" an error message
is displayed.

How and WHEREdo I insert the....something like

If Me!cgs= 0 Then
Me!profitpercent.Visible = 0
Else
Me!profitpercent.Visible = -1
End If

Thank you
RMarko

 

[J. Goddard]

Thank you for the kind words, Rhonda.

Glad I was able to help.

John

John,
You win the "THINK OUTSIDE THE BOX" Award. I was so consumed with figuring
out how to program the form to do what I needed it to do, I didn't even think
to program the "CGS" field. What I entered in "after update"

Private Sub CGS_AfterUpdate()

Me!Profit = ([INVOICEAMOUNT] - [CGS])
Me!ProfitPercent = ([Profit] / [INVOICEAMOUNT])

End Sub

So simple, I will the big "DUH" Award
WORKS GREAT!!!! THANK YOU SO MUCH.

Rhonda

:

The #Num is due to division by zero in the calculation.
Instead of defaulting the value of profit and percentprofit to the
calculated values, why not default them to 0.0, then calculate the
values in the After Update events of the InvoiceAmount and/or CDS?

It's worth noting that even though Invoiceamount and CGS might have
values, PercentProfit of zero might still be a perfectly valid value.
(not that that is a very good way to run a business - but that's another
issue entirely!), so hiding it in those circumstances might not be a
good idea.

Hope this helps

John

I'm very sad to say that none of your suggestions have worked. I've used the
formula is every place I can think of on the page with no success. Any
suggestions?

Once again......
When you open a record in this contact mgmt database, there is no financial
information....everything defaults to "$0.00"

At the end there are 4 fields:

INVOICEAMOUNT CGS PROFIT PERCENTPROFIT

Invoice amount is typed in
Cost of goods sold amount is typed in

Profit is calculated =([INVOICEAMOUNT]-[CGS])

Percent is calculated =([PROFIT]/[INVOICEAMOUNT])

All calculations work perfectly. Whenever we enter an amount in Invoice and
CGS, the profit and percent profit display perfectly.
However, when we open a file, we don't have an invoice amount yet, so we
default everything to $0.00.

Profit% field displays #Num!
How can I get rid of this?
Please help!
Rhonda



:



Hi,

'Scuse me butting in but I'm curious about how you blank out zeros on your
report. Is there any reason you cannot set the Format property of
ProposalAmount to #,##0.00;-#,##0.00;" " ?

Regards,

Rod

:



When designing our sales report, if the amount in the field is equal to "0" I
prefer there to be a blank, rather than a page filled with zeros. I do this
by entering:

If Me!ProposalAmount = 0 Then
Me!ProposalAmount.Visible = 0
Else
Me!ProposalAmount.Visible = -1
End If


In my form I have a field "InvoiceTotal" and another "CGS"
The next field calculates the profit
=([InvoiceTotal]-[cgs])

It's the last field I'm having a problem with. It calculates the percentage
of profit
=([PROFIT]/[InvoiceTotal])

The calculation works great when there is a number in the invoice and cgs
fields. If the invoice and cgs fields are the default "0" an error message
is displayed.

How and WHEREdo I insert the....something like

If Me!cgs= 0 Then
Me!profitpercent.Visible = 0
Else
Me!profitpercent.Visible = -1
End If

Thank you
RMarko