Help! If statement for Text field

[MikeB]

I have a Text2 field that will be part of a formula. I want to user to be
able to enter a number (0,1, or 2) that corrrsponds to a formula percentage.
I am having trouble writing the If statement. What I would like to have
happen is.

User would enter into Text2,

0=1.0 (no discount)
1=.70 (30% discount)
2=.85 (15% discount)

We only have three discounts.



The form I created has the following,

Text0, for the List Price
Text2, for the discount code value as listed above.

Text3, shows the result that is discounted. =(Text0*Text2)

Any help you can give would be great.

Thanks,
MikeB

 

[Matthew Hager]

dim discount as double
discoutn = 0
select case text2.text
case is = "0"
discount = 1.0
case is = "1"
dicsount = .7
case is = "2"
discount = .85
case else
msgbox "Invalid Case."
dicsount = 1
end select

Thanks!
mh

P.S. Hope this helps

 

[MikeB]

Matthew,

I am still a little confused. Where does this code go? On Got Focus?

Answer I am getting is too high (1955.63), should be 831.14.

Text0 is 263.90 Euros
Text2 is discount code 2 for a 15% discount (.85 factor)

List Price box uses this formula. =((([Text0])*([Text2])*1.1)*1.1228)*3

What should happen is this,
Text0 = 263.90 Euro's
multiple by .85 = 224.32 (discount)
multiple by 1.1 (conversion to US dollars) = 246.75
mulptiple by 1.1228 (our overhead rate) = 277.05
multiple by 3 to get List Price - 831.14

There maybe a better way of doing this. To get List Price all I want my
users to do is enter the Euros, then discount code number. Then the List
Price box will display the correct price.

Thanks for the help,
Mike

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

 

[fredg]

I have a Text2 field that will be part of a formula. I want to user to be
able to enter a number (0,1, or 2) that corrrsponds to a formula percentage.
I am having trouble writing the If statement. What I would like to have
happen is.

User would enter into Text2,

0=1.0 (no discount)
1=.70 (30% discount)
2=.85 (15% discount)

We only have three discounts.

The form I created has the following,

Text0, for the List Price
Text2, for the discount code value as listed above.

Text3, shows the result that is discounted. =(Text0*Text2)

Any help you can give would be great.

Thanks,
MikeB

If there are just the 3 choices, you can use an IIf() statement:
as Control Source in an unbound control:

=IIf([Text2] = 1,[Text0] * 0.70,IIf([Text2] = 2,[Text0] *
0.85,[Text0]))

This is not a good system to use however, as it may be you will add
additional discount categories, or change an existing discount value.
You then must re-write the entire code.

A better solution would be to store the discount value in a table of
discounts:

DiscountNumber: Number Datatype Integer Indexed No Duplicates Prime
Key
DiscountValue: Number Datatype Single

where DiscountNumber is 0, 1, 2, etc. and DiscountValue is 0, 0.15,
0.30, etc.

Then use as the control source for [Text3]:

= [Text0] * (1-
DLookUp("[DiscountValue]","tblDiscounts","[DiscountNumber] = " &
[Text2]))

where Discount number is an Integer 0, 1, 2, etc. and Discount value
is a Single 0, 0.15, 0.30, etc.
This way all you need do is change the DiscountValue in the table, or
add a new DiscountNumber and Value if you wish, with no further
changes needed to the database, (and no need to mentally convert .15
to .85).

 

[MikeB]

Fred,

Thanks this worked great.

Another question, once I make the calcuation, how can I clear the first two
fields (Text0 and Text2), so I can run another calcuation?
Right now I have to close the form, then reopen.

Thanks,
MikeB

~~~~~~~~~~~~~~~~~~~~~~~~~````

I have a Text2 field that will be part of a formula. I want to user to be
able to enter a number (0,1, or 2) that corrrsponds to a formula percentage.
I am having trouble writing the If statement. What I would like to have
happen is.

User would enter into Text2,

0=1.0 (no discount)
1=.70 (30% discount)
2=.85 (15% discount)

We only have three discounts.

The form I created has the following,

Text0, for the List Price
Text2, for the discount code value as listed above.

Text3, shows the result that is discounted. =(Text0*Text2)

Any help you can give would be great.

Thanks,
MikeB

If there are just the 3 choices, you can use an IIf() statement:
as Control Source in an unbound control:

=IIf([Text2] = 1,[Text0] * 0.70,IIf([Text2] = 2,[Text0] *
0.85,[Text0]))

This is not a good system to use however, as it may be you will add
additional discount categories, or change an existing discount value.
You then must re-write the entire code.

A better solution would be to store the discount value in a table of
discounts:

DiscountNumber: Number Datatype Integer Indexed No Duplicates Prime
Key
DiscountValue: Number Datatype Single

where DiscountNumber is 0, 1, 2, etc. and DiscountValue is 0, 0.15,
0.30, etc.

Then use as the control source for [Text3]:

= [Text0] * (1-
DLookUp("[DiscountValue]","tblDiscounts","[DiscountNumber] = " &
[Text2]))

where Discount number is an Integer 0, 1, 2, etc. and Discount value
is a Single 0, 0.15, 0.30, etc.
This way all you need do is change the DiscountValue in the table, or
add a new DiscountNumber and Value if you wish, with no further
changes needed to the database, (and no need to mentally convert .15
to .85).