Help! If statement for Text field

  • Thread starter Thread starter MikeB
  • Start date Start date
M

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
 
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
 
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

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
 
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
Click to expand...

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).
 
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

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



fredg said:
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
Click to expand...

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).
Click to expand...
 
Back
Top