If Clause

  • Thread starter Thread starter Pete
  • Start date Start date
P

Pete

I would like to be a bit of VB into a Form such as the following:
If A = 1, then B = 100%
If A= 2, then B= 70%
If A=3, then B = 33%
Else B = 0%
Could someone help me to write the format so that the appropriate values
appear. Newbie at this. Appreciated.
 
I assume A and B are either fields or variables.

You could use Select Case:
Select Case A
Case 1
B = 1
Case 2
B = .7
Case 3
B = .33
Case Else
B = 0
End Select

Format B to get the desired percentage result. You could also use ElseIf.
Help has more information about Select Case and ElseIf. There is also the
Switch function, which could be used either in VBA or in an expression in a
query or text box.
 
There's also the Choose function that could be utilized in this case:

=Choose(A, 1, .7, .33)

Note that that will return Null if A is other than 1, 2 or 3. To have it
return 0 (as in Bruce's example), you can use

=Nz(Choose(A, 1, .7, .33), 0)
 
Showing my ignorance here! Thanks so far.
A and B are separate fields. What do I actually do to make the value of 100%
appear in field B if A has a value of 1? When you say, Select Case, what
does this actually mean vis a vis the VB code in field A or B? I want the
values in Field B to appear automatically simply on the appearance of the
first value in Field A.
 
Thanks so far. Is the VB code written against text[A] on update? If so, is it
possible to specify the precise code written?
 
A and B are separate fields. What do I actually do to make the value of 100%
appear in field B if A has a value of 1?
Click to expand...

Then they are redundant. Only one of the fields should be stored in your
table!

Storing derived data such as this in your table accomplishes
three things: it wastes disk space; it wastes time (almost
any calculation will be MUCH faster than a disk fetch); and
most importantly, it risks data corruption. If one of the
underlying fields is subsequently edited, you will have data
in your table WHICH IS WRONG, and no automatic way to detect
that fact.

Just redo the calculation whenever you need it, either as a
calculated field in a Query or just as you're now doing it -
in the control source of a Form or a Report textbox.
 
After Update may not be the best choice. It will work for the current
record after updating TextA, but when you move to a new record the update
will not occur. TextB will show the value from the last time TextA was
updated, no matter the current value of TextA, until you update TextA. You
would need the same code in the Current event to make each record show the
correct value as soon as you go to the record.
I just now learned about the Choose function. It seems to be the cleanest
solution. You can place the expression Douglas Steele suggested into the
Control Source of an unbound text box, except substitute the name of your
TextA field for A:
=Nz(Choose([TextA], 1, .7, .33), 0)
You could also use the same expression in a query. At the top of a blank
column in query design view:
ViewPercent: Nz(Choose([TextA], 1, .7, .33), 0)
Base your form on the query, and bind a text box to ViewPercent (i.e. use
ViewPercent as its Control Source).
In either case, format the text box for Percent.
Help has more information about Choose and Nz.
I will assume that in asking about VB you are asking about a way to produce
the desired result rather than specifically about using VBA. As I said, VBA
would require some extra work, and from what I can see it offers nothing you
can't get from an expression.

Pete said:
Thanks so far. Is the VB code written against text[A] on update? If so, is
it
possible to specify the precise code written?

Douglas J. Steele said:
There's also the Choose function that could be utilized in this case:

=Choose(A, 1, .7, .33)

Note that that will return Null if A is other than 1, 2 or 3. To have it
return 0 (as in Bruce's example), you can use

=Nz(Choose(A, 1, .7, .33), 0)
Click to expand...
Click to expand...
 
Back
Top