if statement with multiple conditions

H

hedrick

I am trying to get a form to automatically enter a row based on
criteria set in an if statement but it's not working. I am new to vba
and am getting really confused. below is the if statement, any help
would be greatly appreciated.
Dim strSQL As String
Dim mdl As String
Dim jbn As String
Dim strsql1 As String
Dim strsql2 As String
Dim STRSQL3 As String
mdl = Forms![form1]![Text1]
odr = Forms![form1]![orderid]
strSQL = "INSERT INTO dbo.tbl_tloptions (optionsid, opt_qty, orderid,
subtotal, bo, modelid, opt_cat, descript) values ('407','1', '" & odr &
"', CONVERT(money,' 0 ') ,'b','" & mdl & "','LIGHT','LIGHT- Std Running
Lights 3-each side & 3-nose (21 & up)') ;"
strsql1 = "INSERT INTO dbo.tbl_tloptions (optionsid, opt_qty, orderid,
subtotal, bo, modelid, opt_cat, descript) values ('406','1', '" & odr &
"', CONVERT(money,' 0 '),'b','" & mdl & "','LIGHT','LIGHT- Std Running
Lights 2-each side & 3-nose (20 & under)') ;"
strsql2 = "INSERT INTO dbo.tbl_tloptions (optionsid, opt_qty, orderid,
subtotal, bo, modelid, opt_cat, descript) values ('742','1', '" & odr &
"', CONVERT(money,' 0 '),'b','" & mdl & "','LIGHT','LIGHTS - STD
Running Lights - 2 Each Side & 2 on Nose') ;"
STRSQL3 = "INSERT INTO dbo.tbl_tloptions (optionsid, opt_qty, orderid,
subtotal, bo, modelid, opt_cat, descript) values ('758','1', '" & odr &
"', CONVERT(money,' 0 '),'b','" & mdl & "','LIGHT','LIGHTS - STD
Running Lights - 3 Each Side & 2 on Nose') ;"
If ((Me![Text1]) <> 61) Or ((Me![Text1]) <> 116) Or ((Me![Text1]) <>
86) Or ((Me![Text1]) said:
= 21) Then
Click to expand...
DoCmd.RunSQL strSQL
ElseIf ((Me![Text1]) <> 61) Or ((Me![Text1]) <> 116) Or ((Me![Text1])
<> 86) Or ((Me![Text1]) <> 84) Or ((Me![Text1]) <> 76) Or ((Me![Text1])
<> 53) Or ((Me![Text1]) <> 54) Or ((Me![Text1]) <> 55) And
((Me![length]) <= 20) Then
DoCmd.RunSQL strsql1
ElseIf ((Me![Text1]) = 61) Or ((Me![Text1]) = 116) Or ((Me![Text1]) =
86) Or ((Me![Text1]) = 84) Or ((Me![Text1]) = 76) Or ((Me![Text1]) =
53) Or ((Me![Text1]) = 54) Or ((Me![Text1]) = 55) And ((Me![length]) <=
20) Then
DoCmd.RunSQL strsql2
Else: DoCmd.RunSQL STRSQL3


End If
 
J

Jeff L

I believe what you are looking for is this instead

If (Me.[Text1] <> 61) And (Me.[Text1] <> 116) And (Me.[Text1] <> 86) Or

(Me.[Text1] <> 84) And (Me.[Text1] <> 76) And (Me.[Text1] <> 53) Or
(Me.[Text1] <> 54) And (Me.[Text1] <> 55) Then
If (Me![length] >= 21) Then
DoCmd.RunSQL strSQL
Else
DoCmd.RunSQL strsql1
End IF
Else
If (Me![length] <= 20) Then
DoCmd.RunSQL strsql2
Else
DoCmd.RunSQL STRSQL3
End IF
End IF

With a bunch of Or statements, you were probably finding that strsql2
and strsql3 were never running. That is because your code was hitting
the first if statment and it would be true every time. If Text1 = 61,
Me.Text1 <> 61 would be False, but Me.Text1 <> 86 would be True and
thus the statement would be true because of the OR. When you use an
AND it makes the statement False and thus jumps down into your Else
statement.

Hope that helps!


I am trying to get a form to automatically enter a row based on
criteria set in an if statement but it's not working. I am new to vba
and am getting really confused. below is the if statement, any help
would be greatly appreciated.
Dim strSQL As String
Dim mdl As String
Dim jbn As String
Dim strsql1 As String
Dim strsql2 As String
Dim STRSQL3 As String
mdl = Forms![form1]![Text1]
odr = Forms![form1]![orderid]
strSQL = "INSERT INTO dbo.tbl_tloptions (optionsid, opt_qty, orderid,
subtotal, bo, modelid, opt_cat, descript) values ('407','1', '" & odr &
"', CONVERT(money,' 0 ') ,'b','" & mdl & "','LIGHT','LIGHT- Std Running
Lights 3-each side & 3-nose (21 & up)') ;"
strsql1 = "INSERT INTO dbo.tbl_tloptions (optionsid, opt_qty, orderid,
subtotal, bo, modelid, opt_cat, descript) values ('406','1', '" & odr &
"', CONVERT(money,' 0 '),'b','" & mdl & "','LIGHT','LIGHT- Std Running
Lights 2-each side & 3-nose (20 & under)') ;"
strsql2 = "INSERT INTO dbo.tbl_tloptions (optionsid, opt_qty, orderid,
subtotal, bo, modelid, opt_cat, descript) values ('742','1', '" & odr &
"', CONVERT(money,' 0 '),'b','" & mdl & "','LIGHT','LIGHTS - STD
Running Lights - 2 Each Side & 2 on Nose') ;"
STRSQL3 = "INSERT INTO dbo.tbl_tloptions (optionsid, opt_qty, orderid,
subtotal, bo, modelid, opt_cat, descript) values ('758','1', '" & odr &
"', CONVERT(money,' 0 '),'b','" & mdl & "','LIGHT','LIGHTS - STD
Running Lights - 3 Each Side & 2 on Nose') ;"
If ((Me![Text1]) <> 61) Or ((Me![Text1]) <> 116) Or ((Me![Text1]) <>
86) Or ((Me![Text1]) said:
= 21) Then
Click to expand...
DoCmd.RunSQL strSQL
ElseIf ((Me![Text1]) <> 61) Or ((Me![Text1]) <> 116) Or ((Me![Text1])
<> 86) Or ((Me![Text1]) <> 84) Or ((Me![Text1]) <> 76) Or ((Me![Text1])
<> 53) Or ((Me![Text1]) <> 54) Or ((Me![Text1]) <> 55) And
((Me![length]) <= 20) Then
DoCmd.RunSQL strsql1
ElseIf ((Me![Text1]) = 61) Or ((Me![Text1]) = 116) Or ((Me![Text1]) =
86) Or ((Me![Text1]) = 84) Or ((Me![Text1]) = 76) Or ((Me![Text1]) =
53) Or ((Me![Text1]) = 54) Or ((Me![Text1]) = 55) And ((Me![length]) <=
20) Then
DoCmd.RunSQL strsql2
Else: DoCmd.RunSQL STRSQL3


End If
Click to expand...
 
H

hedrick

Sorry took so long to get back everything has been crazy. Thank you.
That makes perfect sense. I appreciate your help.
Jeff said:
I believe what you are looking for is this instead

If (Me.[Text1] <> 61) And (Me.[Text1] <> 116) And (Me.[Text1] <> 86) Or

(Me.[Text1] <> 84) And (Me.[Text1] <> 76) And (Me.[Text1] <> 53) Or
(Me.[Text1] <> 54) And (Me.[Text1] <> 55) Then
If (Me![length] >= 21) Then
DoCmd.RunSQL strSQL
Else
DoCmd.RunSQL strsql1
End IF
Else
If (Me![length] <= 20) Then
DoCmd.RunSQL strsql2
Else
DoCmd.RunSQL STRSQL3
End IF
End IF

With a bunch of Or statements, you were probably finding that strsql2
and strsql3 were never running. That is because your code was hitting
the first if statment and it would be true every time. If Text1 = 61,
Me.Text1 <> 61 would be False, but Me.Text1 <> 86 would be True and
thus the statement would be true because of the OR. When you use an
AND it makes the statement False and thus jumps down into your Else
statement.

Hope that helps!


I am trying to get a form to automatically enter a row based on
criteria set in an if statement but it's not working. I am new to vba
and am getting really confused. below is the if statement, any help
would be greatly appreciated.
Dim strSQL As String
Dim mdl As String
Dim jbn As String
Dim strsql1 As String
Dim strsql2 As String
Dim STRSQL3 As String
mdl = Forms![form1]![Text1]
odr = Forms![form1]![orderid]
strSQL = "INSERT INTO dbo.tbl_tloptions (optionsid, opt_qty, orderid,
subtotal, bo, modelid, opt_cat, descript) values ('407','1', '" & odr &
"', CONVERT(money,' 0 ') ,'b','" & mdl & "','LIGHT','LIGHT- Std Running
Lights 3-each side & 3-nose (21 & up)') ;"
strsql1 = "INSERT INTO dbo.tbl_tloptions (optionsid, opt_qty, orderid,
subtotal, bo, modelid, opt_cat, descript) values ('406','1', '" & odr &
"', CONVERT(money,' 0 '),'b','" & mdl & "','LIGHT','LIGHT- Std Running
Lights 2-each side & 3-nose (20 & under)') ;"
strsql2 = "INSERT INTO dbo.tbl_tloptions (optionsid, opt_qty, orderid,
subtotal, bo, modelid, opt_cat, descript) values ('742','1', '" & odr &
"', CONVERT(money,' 0 '),'b','" & mdl & "','LIGHT','LIGHTS - STD
Running Lights - 2 Each Side & 2 on Nose') ;"
STRSQL3 = "INSERT INTO dbo.tbl_tloptions (optionsid, opt_qty, orderid,
subtotal, bo, modelid, opt_cat, descript) values ('758','1', '" & odr &
"', CONVERT(money,' 0 '),'b','" & mdl & "','LIGHT','LIGHTS - STD
Running Lights - 3 Each Side & 2 on Nose') ;"
If ((Me![Text1]) <> 61) Or ((Me![Text1]) <> 116) Or ((Me![Text1]) <>
86) Or ((Me![Text1]) said:
= 21) Then
Click to expand...
DoCmd.RunSQL strSQL
ElseIf ((Me![Text1]) <> 61) Or ((Me![Text1]) <> 116) Or ((Me![Text1])
<> 86) Or ((Me![Text1]) <> 84) Or ((Me![Text1]) <> 76) Or ((Me![Text1])
<> 53) Or ((Me![Text1]) <> 54) Or ((Me![Text1]) <> 55) And
((Me![length]) <= 20) Then
DoCmd.RunSQL strsql1
ElseIf ((Me![Text1]) = 61) Or ((Me![Text1]) = 116) Or ((Me![Text1]) =
86) Or ((Me![Text1]) = 84) Or ((Me![Text1]) = 76) Or ((Me![Text1]) =
53) Or ((Me![Text1]) = 54) Or ((Me![Text1]) = 55) And ((Me![length]) <=
20) Then
DoCmd.RunSQL strsql2
Else: DoCmd.RunSQL STRSQL3


End If
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

HOW TO Simplify following code 2

Top