Help with If or Case

H

h2fcell

Hello,
I have a report that list all orders for the year. The orders report
includes a Status column and an order could have one of six possible status.
Quote
Held
Invoiced
Invoiced/Cancelled
Deleted
Deleted/Cancelled

The users have asked to get a form with six option buttons they can toggle
to choose which status conditions they want to include or exclude on the
report. I have created the form with the six option buttons with “Default
Value†property set to 1 and a Next button to run the report. I’m blanking
out tryiing to build the nested If statement. Any examples would be great.
Or should I be using a Select Case?

Private Sub Command13_Click()
Dim myFilter As String

If Me.OtpInv = 1 Then
myFilter = "([OrderStatus] = 'Invoiced' )"
Else
End If

‘or should I use a CASE

DoCmd.OpenReport "rptGen1_2SalesByDivision", acViewPreview, , myFilter,
acWindowNormal

End Sub
 
K

KARL DEWEY

Here is a different method. Use Option Group and two field table.
Select option and DLookup pulls corresponding status from table.
 
H

h2fcell

Hi Karl,
Thanks for the speedy reply. As your signature states “Build a little, test
a little.†Below is what I’ve been building.
Private Sub Command13_Click()
Dim myFilter As String
'ALL
If Me.OptInv = 1 And Me.OptQuote = 1 And Me.OptHeld = 1 And Me.OptInvCan = 1
And Me.OptDel = 1 And Me.OptDelCan = 1 Then
myFilter = "([BookingStatus] = 'Invoiced' OR [BookingStatus] = 'Quote' OR
[BookingStatus] = 'Held' OR [BookingStatus] = 'Invoice/Cancelled' OR
[BookingStatus] = 'Deleted' OR [BookingStatus] = 'Deleted/Cancelled')"
End If
'NO INVOICED
If Me.OptInv = 0 And Me.OptQuote = 1 And Me.OptHeld = 1 And Me.OptInvCan = 1
And Me.OptDel = 1 And Me.OptDelCan = 1 Then
myFilter = "([BookingStatus] = 'Quote' OR [BookingStatus] = 'Held' OR
[BookingStatus] = 'Invoice/Cancelled' OR [BookingStatus] = 'Deleted' OR
[BookingStatus] = 'Deleted/Cancelled')"
End If
'NO QUOTE
If Me.OptInv = 1 And Me.OptQuote = 0 And Me.OptHeld = 1 And Me.OptInvCan = 1
And Me.OptDel = 1 And Me.OptDelCan = 1 Then
myFilter = "([BookingStatus] = 'Invoiced' OR [BookingStatus] = 'Held' OR
[BookingStatus] = 'Invoice/Cancelled' OR [BookingStatus] = 'Deleted' OR
[BookingStatus] = 'Deleted/Cancelled')"
End If
'NO HELD
If Me.OptInv = 1 And Me.OptQuote = 1 And Me.OptHeld = 0 And Me.OptInvCan = 1
And Me.OptDel = 1 And Me.OptDelCan = 1 Then
myFilter = "([BookingStatus] = 'Invoiced' OR [BookingStatus] = 'Quote' OR
[BookingStatus] = 'Invoice/Cancelled' OR [BookingStatus] = 'Deleted' OR
[BookingStatus] = 'Deleted/Cancelled')"
End If
'NO INVOICE/CANCELLED
If Me.OptInv = 1 And Me.OptQuote = 1 And Me.OptHeld = 1 And Me.OptInvCan = 0
And Me.OptDel = 1 And Me.OptDelCan = 1 Then
myFilter = "([BookingStatus] = 'Invoiced' OR [BookingStatus] = 'Quote' OR
[BookingStatus] = 'Held' OR [BookingStatus] = 'Deleted' OR [BookingStatus] =
'Deleted/Cancelled')"
End If
'NO DELETED
If Me.OptInv = 1 And Me.OptQuote = 1 And Me.OptHeld = 1 And Me.OptInvCan = 1
And Me.OptDel = 0 And Me.OptDelCan = 1 Then
myFilter = "([BookingStatus] = 'Invoiced' OR [BookingStatus] = 'Quote' OR
[BookingStatus] = 'Held' OR [BookingStatus] = 'Invoice/Cancelled' OR
[BookingStatus] = 'Deleted/Cancelled')"
End If
'NO DELETED/CANCELLED
If Me.OptInv = 1 And Me.OptQuote = 1 And Me.OptHeld = 1 And Me.OptInvCan = 1
And Me.OptDel = 1 And Me.OptDelCan = 0 Then
myFilter = "([BookingStatus] = 'Invoiced' OR [BookingStatus] = 'Quote' OR
[BookingStatus] = 'Held' OR [BookingStatus] = 'Invoice/Cancelled' OR
[BookingStatus] = 'Deleted')"
End If
'NO INVOICED OR QUOTE
If Me.OptInv = 0 And Me.OptQuote = 0 And Me.OptHeld = 1 And Me.OptInvCan = 1
And Me.OptDel = 1 And Me.OptDelCan = 1 Then
myFilter = "([BookingStatus] = 'Held' OR [BookingStatus] =
'Invoice/Cancelled' OR [BookingStatus] = 'Deleted' OR [BookingStatus] =
'Deleted/Cancelled')"
End If
'NO INVOICED OR QUOTE OR HELD
If Me.OptInv = 0 And Me.OptQuote = 0 And Me.OptHeld = 0 And Me.OptInvCan = 1
And Me.OptDel = 1 And Me.OptDelCan = 1 Then
myFilter = "([BookingStatus] = 'Invoice/Cancelled' OR [BookingStatus] =
'Deleted' OR [BookingStatus] = 'Deleted/Cancelled')"
End If
'NO INVOICED OR QUOTE OR HELD OR INVOICE/CANCELLED
If Me.OptInv = 0 And Me.OptQuote = 0 And Me.OptHeld = 0 And Me.OptInvCan = 0
And Me.OptDel = 1 And Me.OptDelCan = 1 Then
myFilter = "([BookingStatus] = 'Deleted' OR [BookingStatus] =
'Deleted/Cancelled')"
End If
'NO INVOICED OR HELD OR INVOICE/CANCELLED
If Me.OptInv = 0 And Me.OptQuote = 1 And Me.OptHeld = 0 And Me.OptInvCan = 0
And Me.OptDel = 1 And Me.OptDelCan = 1 Then
myFilter = "([BookingStatus] = 'Quote' OR [BookingStatus] = 'Deleted' OR
[BookingStatus] = 'Deleted/Cancelled')"
End If

If cboDepMonth = "<ALL>" Then
myFilter = myFilter & "AND [Division] =
[Forms]![frmGen1_2SalesByDivisionV2]![cboDivision] AND [YOD] =
[Forms]![frmGen1_2SalesByDivisionV2]![cboDepYR]"
Else
myFilter = myFilter & "AND [Division] =
[Forms]![frmGen1_2SalesByDivisionV2]![cboDivision] AND [YOD] =
[Forms]![frmGen1_2SalesByDivisionV2]![cboDepYR] AND [MODName] =
[Forms]![frmGen1_2SalesByDivisionV2]![cboDepMonth]"
End If

DoCmd.OpenReport "rptGen1_2SalesByDivision", acViewPreview, , myFilter,
acWindowNormal

End Sub

The above works, but I’ve not covered all selection possibilities.
I can’t believe there isn’t a better way?


KARL DEWEY said:
Here is a different method. Use Option Group and two field table.
Select option and DLookup pulls corresponding status from table.

--
Build a little, test a little.


h2fcell said:
Hello,
I have a report that list all orders for the year. The orders report
includes a Status column and an order could have one of six possible status.
Quote
Held
Invoiced
Invoiced/Cancelled
Deleted
Deleted/Cancelled

The users have asked to get a form with six option buttons they can toggle
to choose which status conditions they want to include or exclude on the
report. I have created the form with the six option buttons with “Default
Value†property set to 1 and a Next button to run the report. I’m blanking
out tryiing to build the nested If statement. Any examples would be great.
Or should I be using a Select Case?

Private Sub Command13_Click()
Dim myFilter As String

If Me.OtpInv = 1 Then
myFilter = "([OrderStatus] = 'Invoiced' )"
Else
End If

‘or should I use a CASE

DoCmd.OpenReport "rptGen1_2SalesByDivision", acViewPreview, , myFilter,
acWindowNormal

End Sub
Click to expand...
Click to expand...
 
D

Daryl S

H2fcell -

Make your life a little simpler. You are not really using an option group
(which only allows one option at a time), so you will need to test each
criteria. Try this:

myFilter = ""

If Me.OptInv = 1 then
If Left(myFilter,4) = "[Boo" Then
myFilter = myFilter & ",'Invoiced'"
Else
myFilter = "[BookingStatus] in ('Invoiced'"
End If

If Me.OptQuote = 1 then
If Left(myFilter,4) = "[Boo" Then
myFilter = myFilter & ",'Quote'"
Else
myFilter = "[BookingStatus] in ('Quote'"
End If

If Me.OptHeld = 1 then
If Left(myFilter,4) = "[Boo" Then
myFilter = myFilter & ",'Held'"
Else
myFilter = "[BookingStatus] in ('Held'"
End If

If Me.OptInvCan = 1 then
If Left(myFilter,4) = "[Boo" Then
myFilter = myFilter & ",'Invoice/Cancelled' "
Else
myFilter = "[BookingStatus] in ('Invoice/Cancelled' "
End If

If Me.OptDelCan = 1 then
If Left(myFilter,4) = "[Boo" Then
myFilter = myFilter & ",'Deleted/Cancelled' "
Else
myFilter = "[BookingStatus] in ('Deleted/Cancelled' "
End If

If Left(myFilter,4) = "[Boo" Then
myFilter = myFilter & ")"
End If

--
Daryl S


h2fcell said:
Hi Karl,
Thanks for the speedy reply. As your signature states “Build a little, test
a little.†Below is what I’ve been building.
Private Sub Command13_Click()
Dim myFilter As String
'ALL
If Me.OptInv = 1 And Me.OptQuote = 1 And Me.OptHeld = 1 And Me.OptInvCan = 1
And Me.OptDel = 1 And Me.OptDelCan = 1 Then
myFilter = "([BookingStatus] = 'Invoiced' OR [BookingStatus] = 'Quote' OR
[BookingStatus] = 'Held' OR [BookingStatus] = 'Invoice/Cancelled' OR
[BookingStatus] = 'Deleted' OR [BookingStatus] = 'Deleted/Cancelled')"
End If
'NO INVOICED
If Me.OptInv = 0 And Me.OptQuote = 1 And Me.OptHeld = 1 And Me.OptInvCan = 1
And Me.OptDel = 1 And Me.OptDelCan = 1 Then
myFilter = "([BookingStatus] = 'Quote' OR [BookingStatus] = 'Held' OR
[BookingStatus] = 'Invoice/Cancelled' OR [BookingStatus] = 'Deleted' OR
[BookingStatus] = 'Deleted/Cancelled')"
End If
'NO QUOTE
If Me.OptInv = 1 And Me.OptQuote = 0 And Me.OptHeld = 1 And Me.OptInvCan = 1
And Me.OptDel = 1 And Me.OptDelCan = 1 Then
myFilter = "([BookingStatus] = 'Invoiced' OR [BookingStatus] = 'Held' OR
[BookingStatus] = 'Invoice/Cancelled' OR [BookingStatus] = 'Deleted' OR
[BookingStatus] = 'Deleted/Cancelled')"
End If
'NO HELD
If Me.OptInv = 1 And Me.OptQuote = 1 And Me.OptHeld = 0 And Me.OptInvCan = 1
And Me.OptDel = 1 And Me.OptDelCan = 1 Then
myFilter = "([BookingStatus] = 'Invoiced' OR [BookingStatus] = 'Quote' OR
[BookingStatus] = 'Invoice/Cancelled' OR [BookingStatus] = 'Deleted' OR
[BookingStatus] = 'Deleted/Cancelled')"
End If
'NO INVOICE/CANCELLED
If Me.OptInv = 1 And Me.OptQuote = 1 And Me.OptHeld = 1 And Me.OptInvCan = 0
And Me.OptDel = 1 And Me.OptDelCan = 1 Then
myFilter = "([BookingStatus] = 'Invoiced' OR [BookingStatus] = 'Quote' OR
[BookingStatus] = 'Held' OR [BookingStatus] = 'Deleted' OR [BookingStatus] =
'Deleted/Cancelled')"
End If
'NO DELETED
If Me.OptInv = 1 And Me.OptQuote = 1 And Me.OptHeld = 1 And Me.OptInvCan = 1
And Me.OptDel = 0 And Me.OptDelCan = 1 Then
myFilter = "([BookingStatus] = 'Invoiced' OR [BookingStatus] = 'Quote' OR
[BookingStatus] = 'Held' OR [BookingStatus] = 'Invoice/Cancelled' OR
[BookingStatus] = 'Deleted/Cancelled')"
End If
'NO DELETED/CANCELLED
If Me.OptInv = 1 And Me.OptQuote = 1 And Me.OptHeld = 1 And Me.OptInvCan = 1
And Me.OptDel = 1 And Me.OptDelCan = 0 Then
myFilter = "([BookingStatus] = 'Invoiced' OR [BookingStatus] = 'Quote' OR
[BookingStatus] = 'Held' OR [BookingStatus] = 'Invoice/Cancelled' OR
[BookingStatus] = 'Deleted')"
End If
'NO INVOICED OR QUOTE
If Me.OptInv = 0 And Me.OptQuote = 0 And Me.OptHeld = 1 And Me.OptInvCan = 1
And Me.OptDel = 1 And Me.OptDelCan = 1 Then
myFilter = "([BookingStatus] = 'Held' OR [BookingStatus] =
'Invoice/Cancelled' OR [BookingStatus] = 'Deleted' OR [BookingStatus] =
'Deleted/Cancelled')"
End If
'NO INVOICED OR QUOTE OR HELD
If Me.OptInv = 0 And Me.OptQuote = 0 And Me.OptHeld = 0 And Me.OptInvCan = 1
And Me.OptDel = 1 And Me.OptDelCan = 1 Then
myFilter = "([BookingStatus] = 'Invoice/Cancelled' OR [BookingStatus] =
'Deleted' OR [BookingStatus] = 'Deleted/Cancelled')"
End If
'NO INVOICED OR QUOTE OR HELD OR INVOICE/CANCELLED
If Me.OptInv = 0 And Me.OptQuote = 0 And Me.OptHeld = 0 And Me.OptInvCan = 0
And Me.OptDel = 1 And Me.OptDelCan = 1 Then
myFilter = "([BookingStatus] = 'Deleted' OR [BookingStatus] =
'Deleted/Cancelled')"
End If
'NO INVOICED OR HELD OR INVOICE/CANCELLED
If Me.OptInv = 0 And Me.OptQuote = 1 And Me.OptHeld = 0 And Me.OptInvCan = 0
And Me.OptDel = 1 And Me.OptDelCan = 1 Then
myFilter = "([BookingStatus] = 'Quote' OR [BookingStatus] = 'Deleted' OR
[BookingStatus] = 'Deleted/Cancelled')"
End If

If cboDepMonth = "<ALL>" Then
myFilter = myFilter & "AND [Division] =
[Forms]![frmGen1_2SalesByDivisionV2]![cboDivision] AND [YOD] =
[Forms]![frmGen1_2SalesByDivisionV2]![cboDepYR]"
Else
myFilter = myFilter & "AND [Division] =
[Forms]![frmGen1_2SalesByDivisionV2]![cboDivision] AND [YOD] =
[Forms]![frmGen1_2SalesByDivisionV2]![cboDepYR] AND [MODName] =
[Forms]![frmGen1_2SalesByDivisionV2]![cboDepMonth]"
End If

DoCmd.OpenReport "rptGen1_2SalesByDivision", acViewPreview, , myFilter,
acWindowNormal

End Sub

The above works, but I’ve not covered all selection possibilities.
I can’t believe there isn’t a better way?


KARL DEWEY said:
Here is a different method. Use Option Group and two field table.
Select option and DLookup pulls corresponding status from table.

--
Build a little, test a little.


h2fcell said:
Hello,
I have a report that list all orders for the year. The orders report
includes a Status column and an order could have one of six possible status.
Quote
Held
Invoiced
Invoiced/Cancelled
Deleted
Deleted/Cancelled

The users have asked to get a form with six option buttons they can toggle
to choose which status conditions they want to include or exclude on the
report. I have created the form with the six option buttons with “Default
Value†property set to 1 and a Next button to run the report. I’m blanking
out tryiing to build the nested If statement. Any examples would be great.
Or should I be using a Select Case?

Private Sub Command13_Click()
Dim myFilter As String

If Me.OtpInv = 1 Then
myFilter = "([OrderStatus] = 'Invoiced' )"
Else
End If

‘or should I use a CASE

DoCmd.OpenReport "rptGen1_2SalesByDivision", acViewPreview, , myFilter,
acWindowNormal

End Sub
Click to expand...
Click to expand...
Click to expand...
 
M

Mike Painter

h2fcell said:
Hello,
I have a report that list all orders for the year. The orders report
includes a Status column and an order could have one of six possible
status. Quote
Held
Invoiced
Invoiced/Cancelled
Deleted
Deleted/Cancelled

The users have asked to get a form with six option buttons they can
toggle to choose which status conditions they want to include or
exclude on the report.
Click to expand...

You have indicated they can pick one or more so you will need to use check
boxes.
Build a Status table and relate it to your query
Build a subform with the description and "Pick" field.
Drop it on your main form
Create two buttons that sit at the top of this form.
"Select All" which runs query setting all Status to True
"Clear All" which runs query setting all Status to False

The report query will include a criteria that only picks records where Pick
is true.
This will allow you to add another status without rewriting code.
The status table retains what was checked so users can run the same report
over again.




SID Description Pick (A yes/no field)
1 Quote
2 Held
3 Invoiced
4 Invoiced/Cancelled
5 Deleted
6 Deleted/Cancelled
 
H

h2fcell

Hi Mike,
This sounds like the way to go.
Do you know of any sample DB's that implement this type if pick logic?
I would like to take a detailed look at an example.

Thanks.
 
M

Mike Painter

h2fcell said:
Hi Mike,
This sounds like the way to go.
Do you know of any sample DB's that implement this type if pick logic?
I would like to take a detailed look at an example.

Thanks.
Click to expand...

I wrote a medical lab program that tracks testing and used it to pick
doctors and tests.
If I can find a copy I'll post it but there is not much to it.
The buttons run something like
UPDATE StatusTable SET PickField = False;
to unselect and
UPDATE StatusTable SET PickField = True;
to select all.
The rest is as I stated.
 

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

Help! with myfilter confusion. 0
Report using subform Order By and Filter values 1
Interesting query requirement 1
Print All/Selected 0
Help or suggestion needed on tabledesign 4
Filtering reports 0
How to generate list of items with same status over three or moresuccessive days 3
Help on IF Expression 2

Top