Access 2003: if statement on a field
Solved
sebbiker
Posted messages
9
Status
Member
-
Salsero77 -
Salsero77 -
Hello,
I am a beginner in Access 2003 programming and I don’t know how to code this in a form:
I have: 1 field "billing", 1 field "gross margin", 1 field "net margin", 4 other fields.
What I want for the field "Net Margin":
IF billing = 0 THEN net margin = gross margin
OTHERWISE net margin = (calculation formula based on the 4 other fields (only additions or subtractions))
I tried to code this but nothing has worked.
Thank you for the help.
Seb
I am a beginner in Access 2003 programming and I don’t know how to code this in a form:
I have: 1 field "billing", 1 field "gross margin", 1 field "net margin", 4 other fields.
What I want for the field "Net Margin":
IF billing = 0 THEN net margin = gross margin
OTHERWISE net margin = (calculation formula based on the 4 other fields (only additions or subtractions))
I tried to code this but nothing has worked.
Thank you for the help.
Seb
Configuration: Windows XP Internet Explorer 7.0
5 answers
Several things seem unclear in your reasoning, but I’m not a accounting specialist anyway:
The gross margin is not derived from a calculation according to how you describe it??? that surprises me!
You put a value in euros for billing and not a yes/no? if you put a euro value why doesn't it come into the calculation?
let's say that the gross margin has a value to enter in a field and that billing does not have a non value but a number
for my part I would write the command line as follows in a free field of the query (where the field titles are entered):
Gross margin: TrueFalse([Billing]=0;[Gross margin];([price1]-[cost2])+([cost1]-[cost2])+[price bonus])
The gross margin is not derived from a calculation according to how you describe it??? that surprises me!
You put a value in euros for billing and not a yes/no? if you put a euro value why doesn't it come into the calculation?
let's say that the gross margin has a value to enter in a field and that billing does not have a non value but a number
for my part I would write the command line as follows in a free field of the query (where the field titles are entered):
Gross margin: TrueFalse([Billing]=0;[Gross margin];([price1]-[cost2])+([cost1]-[cost2])+[price bonus])
It isn’t really that. I want to know how to express this condition:
The value of "net margin" depends on this condition:
IF "billing" = 0 THEN "net margin" = "gross margin" (in fact the "net margin" will take the value of the gross margin)
ELSE (i.e., "billing" = a value in €)
"net margin" = ((price1 - price2) + (costs1 - costs2) + price bonus)
That’s what I need.
Thanks for helping me translate this into Access language.
Seb
The value of "net margin" depends on this condition:
IF "billing" = 0 THEN "net margin" = "gross margin" (in fact the "net margin" will take the value of the gross margin)
ELSE (i.e., "billing" = a value in €)
"net margin" = ((price1 - price2) + (costs1 - costs2) + price bonus)
That’s what I need.
Thanks for helping me translate this into Access language.
Seb
Good evening,
Unless there is a misunderstanding, it should look like this:
=iff([billing] = 0 ; [Net_Margin] = [Gross_Margin] ; [Net_Margin] = ([price1] - [price2]) + (([costs1]) - ([costs2]) + [Bonus_Price) )
The first ";" means "then"
The second ";" means "else"
NB: I have not run any tests
Best regards
Unless there is a misunderstanding, it should look like this:
=iff([billing] = 0 ; [Net_Margin] = [Gross_Margin] ; [Net_Margin] = ([price1] - [price2]) + (([costs1]) - ([costs2]) + [Bonus_Price) )
The first ";" means "then"
The second ";" means "else"
NB: I have not run any tests
Best regards
Hello,
In the form that summarizes the data, it is necessary to create a text field in which arithmetic calculations are performed:
Here is an example: (the online help one) that you will adapt.
= IIf([Montant] > 1000, "Élevé", "Modeste")
Attention: in this context, the result of the calculations is not recorded in the underlying table.
Note: These indications concern Access 2000
In the form that summarizes the data, it is necessary to create a text field in which arithmetic calculations are performed:
Here is an example: (the online help one) that you will adapt.
= IIf([Montant] > 1000, "Élevé", "Modeste")
Attention: in this context, the result of the calculations is not recorded in the underlying table.
Note: These indications concern Access 2000