If Then Else

L

Legris

Would someone take a look at my code and see what I missed please. My goal
is to choose dates for when we acquired the item, drop down for Product
Number 1-3 (cbxProdNo) and a dropdown for a text Item (cbxItem) and view a
report of the result.

Private Sub cbxItem_AfterUpdate()
Dim strsql As String
Set db = CurrentDb
Dim rst As Recordset

If Me!cbxProductNumber = 1 Then
strsql = "qryMFG QC Scrap Detail by Item ProductNumber1"
If Me!cbxProductNumber = 2 Then
strsql = "qryMFG QC Scrap Detail by Item ProductNumber2"
If Me!cbxProductNumber = 3 Then
strsql = "qryMFG QC Scrap Detail by Item ProductNumber3"
Else
strsql = "qryMFG QC Scrap Detail by Item"
End If
End If
End If


Me!lbxProduction.RowSource = strsql
Me!cbxClassCode = " "

End Sub

What my end result is it will not break it down into individual product
numbers on the report, but still gives me items for all three product numbers.
The last query is suppose give me all, but the numbered queries I just was to
see the Item for that product number selected in those specified queries.
Here is sample query:

Date = Between [Forms]![frmItem]![txtStartDate] and [Forms]!{frmItem]!
[txtEndDate]
ProductNumber = Like "1" (so on for the other two queries)

On the query for it to list all, I have:

Date = Between [Forms]![frmItem]![txtStartDate] and [Forms]!{frmItem]!
[txtEndDate]
Item = [Forms]![frmItem]![cbxItem]

Not too good at this, so any advice is much appreciated. Thank you.
Legris
 
K

Klatuu

The End has to be after the If. You can't nest them like that:

If Me!cbxProductNumber = 1 Then
strsql = "qryMFG QC Scrap Detail by Item ProductNumber1"
End If
If Me!cbxProductNumber = 2 Then
strsql = "qryMFG QC Scrap Detail by Item ProductNumber2"
End If
If Me!cbxProductNumber = 3 Then
strsql = "qryMFG QC Scrap Detail by Item ProductNumber3"
Else
strsql = "qryMFG QC Scrap Detail by Item"
End If

But, the way your logic flows, it will always return "qryMFG QC Scrap Detail
by Item" except when Me!cbxProductNumber = 3
You could use the ElseIf to to what you want:

If Me!cbxProductNumber = 1 Then
strsql = "qryMFG QC Scrap Detail by Item ProductNumber1"
ElseIf Me!cbxProductNumber = 2 Then
strsql = "qryMFG QC Scrap Detail by Item ProductNumber2"
ElseIf Me!cbxProductNumber = 3 Then
strsql = "qryMFG QC Scrap Detail by Item ProductNumber3"
Else
strsql = "qryMFG QC Scrap Detail by Item"
End If

But, here is a much easier way to do it and it is easier to read. Use the
Select Case statement. It will excute the first true condition, then exit
the statment. The Case Else will execute if there are no matches in the
list:

Select Case Me!cbxProductNumber
Case 1
strsql = "qryMFG QC Scrap Detail by Item ProductNumber1"
Case 2
strsql = "qryMFG QC Scrap Detail by Item ProductNumber2"
Case 3
strsql = "qryMFG QC Scrap Detail by Item ProductNumber3"
Case Else
strsql = "qryMFG QC Scrap Detail by Item"
End Select



Legris said:
Would someone take a look at my code and see what I missed please. My
goal
is to choose dates for when we acquired the item, drop down for Product
Number 1-3 (cbxProdNo) and a dropdown for a text Item (cbxItem) and view a
report of the result.

Private Sub cbxItem_AfterUpdate()
Dim strsql As String
Set db = CurrentDb
Dim rst As Recordset

If Me!cbxProductNumber = 1 Then
strsql = "qryMFG QC Scrap Detail by Item ProductNumber1"
If Me!cbxProductNumber = 2 Then
strsql = "qryMFG QC Scrap Detail by Item ProductNumber2"
If Me!cbxProductNumber = 3 Then
strsql = "qryMFG QC Scrap Detail by Item ProductNumber3"
Else
strsql = "qryMFG QC Scrap Detail by Item"
End If
End If
End If


Me!lbxProduction.RowSource = strsql
Me!cbxClassCode = " "

End Sub

What my end result is it will not break it down into individual product
numbers on the report, but still gives me items for all three product
numbers.
The last query is suppose give me all, but the numbered queries I just was
to
see the Item for that product number selected in those specified queries.
Here is sample query:

Date = Between [Forms]![frmItem]![txtStartDate] and [Forms]!{frmItem]!
[txtEndDate]
ProductNumber = Like "1" (so on for the other two queries)

On the query for it to list all, I have:

Date = Between [Forms]![frmItem]![txtStartDate] and [Forms]!{frmItem]!
[txtEndDate]
Item = [Forms]![frmItem]![cbxItem]

Not too good at this, so any advice is much appreciated. Thank you.
Legris
Click to expand...
 
G

Graham Mandeno

Hi Legris

I've pasted your exact code below and indented the lines so you can really
see what is happening:

If Me!cbxProductNumber = 1 Then
strsql = "qryMFG QC Scrap Detail by Item ProductNumber1"
If Me!cbxProductNumber = 2 Then
strsql = "qryMFG QC Scrap Detail by Item ProductNumber2"
If Me!cbxProductNumber = 3 Then
strsql = "qryMFG QC Scrap Detail by Item ProductNumber3"
Else
strsql = "qryMFG QC Scrap Detail by Item"
End If
End If
End If

You can see now that nothing at all will happen unless cbxProductNumber is
1, because the code will never get to the test for 2 or 3.

If you wish to use If Then Else, then it should look like this:

If Me!cbxProductNumber = 1 Then
strsql = "qryMFG QC Scrap Detail by Item ProductNumber1"
ElseIf Me!cbxProductNumber = 2 Then
strsql = "qryMFG QC Scrap Detail by Item ProductNumber2"
ElseIf Me!cbxProductNumber = 3 Then
strsql = "qryMFG QC Scrap Detail by Item ProductNumber3"
Else
strsql = "qryMFG QC Scrap Detail by Item"
End If

However, I suggest you use a Select Case statement instead:

Select Case Me!cbxProductNumber
Case 1
strsql = "qryMFG QC Scrap Detail by Item ProductNumber1"
Case 2
strsql = "qryMFG QC Scrap Detail by Item ProductNumber2"
Case 3
strsql = "qryMFG QC Scrap Detail by Item ProductNumber3"
Case Else
strsql = "qryMFG QC Scrap Detail by Item"
End Select

--
Good Luck :)

Graham Mandeno [Access MVP]
Auckland, New Zealand



Legris said:
Would someone take a look at my code and see what I missed please. My
goal
is to choose dates for when we acquired the item, drop down for Product
Number 1-3 (cbxProdNo) and a dropdown for a text Item (cbxItem) and view a
report of the result.

Private Sub cbxItem_AfterUpdate()
Dim strsql As String
Set db = CurrentDb
Dim rst As Recordset

If Me!cbxProductNumber = 1 Then
strsql = "qryMFG QC Scrap Detail by Item ProductNumber1"
If Me!cbxProductNumber = 2 Then
strsql = "qryMFG QC Scrap Detail by Item ProductNumber2"
If Me!cbxProductNumber = 3 Then
strsql = "qryMFG QC Scrap Detail by Item ProductNumber3"
Else
strsql = "qryMFG QC Scrap Detail by Item"
End If
End If
End If


Me!lbxProduction.RowSource = strsql
Me!cbxClassCode = " "

End Sub

What my end result is it will not break it down into individual product
numbers on the report, but still gives me items for all three product
numbers.
The last query is suppose give me all, but the numbered queries I just was
to
see the Item for that product number selected in those specified queries.
Here is sample query:

Date = Between [Forms]![frmItem]![txtStartDate] and [Forms]!{frmItem]!
[txtEndDate]
ProductNumber = Like "1" (so on for the other two queries)

On the query for it to list all, I have:

Date = Between [Forms]![frmItem]![txtStartDate] and [Forms]!{frmItem]!
[txtEndDate]
Item = [Forms]![frmItem]![cbxItem]

Not too good at this, so any advice is much appreciated. Thank you.
Legris
Click to expand...
 
M

Mike Painter

Legris said:
Would someone take a look at my code and see what I missed please.
My goal is to choose dates for when we acquired the item, drop down
for Product Number 1-3 (cbxProdNo) and a dropdown for a text Item
(cbxItem) and view a report of the result.

Private Sub cbxItem_AfterUpdate()
Dim strsql As String
Set db = CurrentDb
Dim rst As Recordset

If Me!cbxProductNumber = 1 Then
strsql = "qryMFG QC Scrap Detail by Item ProductNumber1"
If Me!cbxProductNumber = 2 Then
strsql = "qryMFG QC Scrap Detail by Item ProductNumber2"
If Me!cbxProductNumber = 3 Then
strsql = "qryMFG QC Scrap Detail by Item
ProductNumber3"
Else
strsql = "qryMFG QC Scrap Detail by Item"
End If
End If
End If


Me!lbxProduction.RowSource = strsql
Me!cbxClassCode = " "

End Sub
Click to expand...

If the value = 1 then strsql = "qryMFG QC Scrap Detail by Item
ProductNumber1"
and everything else is ignored
If it not equal to 1 then the ELSE happens and strsql = "qryMFG QC Scrap
Detail by Item"

Use a Case statement to make it very clear, I have an aversion to nested IF
statements.
(I once worked on a DOS based system with no documentation that essentially
consisted of IF statements nested like the IIF statement is.)
 
L

Legris via AccessMonster.com

Thank you all. Case does look much cleaner.

Mike said:
Would someone take a look at my code and see what I missed please.
My goal is to choose dates for when we acquired the item, drop down
Click to expand...
[quoted text clipped - 23 lines]
End Sub
Click to expand...

If the value = 1 then strsql = "qryMFG QC Scrap Detail by Item
ProductNumber1"
and everything else is ignored
If it not equal to 1 then the ELSE happens and strsql = "qryMFG QC Scrap
Detail by Item"

Use a Case statement to make it very clear, I have an aversion to nested IF
statements.
(I once worked on a DOS based system with no documentation that essentially
consisted of IF statements nested like the IIF statement is.)
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

Unbound input text boxes for multi-tab page form 2
IF Then Else 2
Help with this function... 2
if then else statement 6
If Then Else error 2
still no dynamic crosstab report 0
Value of Field In Edit Mode 1
I manipulate a record that I've put into a listbox (vba) 3

Top