if then statements using "or" and "and"

[kmart]

I have created a form with a dropdown list of treatments and a field with
warrants that are manually entered.
I want to build a statement in a different field called priority using both
of these. I want to say:
If treatment=bypass lane or treatment=channelization or treatment=flare or
treatment=LT lane AND If warrants<50 then 5 or warrants<55.1 then 4 or
warrants<60.1 then 3 else If treatment=areal or treatment=dell AND
warrants<50.1 then 1 or warrants < 60.1 then 2.
I want to say if the treatment is bypass lane or channelization or flare and
if the warrants are less than 50 then it receives 5 or if the warrants are
less than 55.1 then it receives 4 in the priority field.

thank you

 

[Beetle]

Assuming that the treatment vulues you posted represent all the
possible choices and that those values are the bound column of the
combo box (if not you'll have to adjust the code accordingly), the
following code should work;

Select Case Me.cboTreatment
Case "areal", "dell"
Me.txtPriority = Switch(Me.txtWarrants < 50.1, 1, _
Me.txtWarrants < 60.1, 2)
Case Else
Me.txtPriority = Switch(Me.txtWarrants < 50.1, 5, _
Me.txtWarrants < 55.1, 4, _
Me.txtWarrants < 60.1, 3)
End Select

You would want to put this code in the After Update event of your
Warrants text box, the Current event of your form, and possibly
the After Update event of your Treatments combo box (in case a
user changes the Treatment without changing the value in the Warrants
text box). I've added continuation characters to adjust for the
newsgroup line wrap. Also, you'll need to use your actual control
names of course.

 

[UpRider]

Kmart, this function will calculate Priority without using complex if...else
code.

Function fcnPriority(Treatment As String, Warrants As Variant) As Long
Select Case Treatment
Case "bypass lane", "channelization", "flare", "LT lane"
Select Case Warrants
Case Is < 50
fcnPriority = 5
Case Is < 55.1
fcnPriority = 4
Case Is < 60.1
fcnPriority = 3
End Select
Case "areal", "dell"
Select Case Warrants
Case Is < 50.1
fcnPriority = 1
Case Is < 60.1
fcnPriority = 2
End Select
End Select
End Function

HTH, UpRider

 

[kmart]

Thank you for responding so promptly. I understand what you are saying
however, I do not know how to use Visual Basic. I was assigned the task at
my job to create a database and I had no previous experience with Access. I
have taken a few courses, but they were not what I needed. Could you tell me
how to use Visual Basic or what I need to do, so as I can use what you told
me, if it's it possible to explain via email?

Thank you,
Kmart

 

[UpRider]

Well, let's see where we have to start.
What version of Access are you using? Is your application being used over a
network?
When viewing the form, do you know how to put it into design view, and how
to view the code?
Do you know how to create queries?

UpRider

 

[kmart]

I am using Access 2003. Yes, it is being used over a network. I know how to
go to design view and view the code. I have created a form with list boxes,
combo boxes, I have subforms and I have used the expression builder for "if"
"then" statements and calculations. I know how to create queries as well.

Kmart

 

[UpRider]

OK. Let's go.

Copy the function I provided into the form's code module.
I'm assuming that the Treatments control on the form is a combo box that
will drop down showing the various treatments (I called it cboTreatment). I
also assume that the Warrants are in a text box. (I called it txtWarrants).
I also assume Priority is in an unbound textbox, named txtPriority.
Now, in form design mode, click on txtPriority. If the property sheet does
not show, hit F4, or right click on Priority and select Properties. In the
displayed property sheet, select the Data tab.
In the Control Source box type exactly
=fcnPriority([cboTreatment],[txtWarrants])

Right click on the form, select Form View and test the results.

HTH UpRider