Solved

Multi Where clause

Posted on 2013-12-31
3
241 Views
Last Modified: 2013-12-31
Hi,
I am trying to open a report based on a where clause depending on what the user selects in an optionframe on a form.

I have looked through here and google but I am getting more and more confused.
It works if I select an option with a single number behind it, but not with the two numbers. (the numbers are primary keys for the customers).

Any help is appreciated.
Thanks
Dim strWhere As String
Dim Cust As Integer

Select Case Me.frameProdGroup
    Case 1
        Cust = 300 Or 169
    Case 2
        Cust = 444 Or 329
    Case 3
        Cust = 238
    Case 4
        Cust = 220
    Case 5
        Cust = 235
    Case 6
        Cust = 180
End Select

strWhere = "[fkCustID] = " & Cust
DoCmd.OpenReport "rptStoxBase", acViewReport, , strWhere

Open in new window

0
Comment
Question by:Stephen Byrom
  • 2
3 Comments
 
LVL 47

Accepted Solution

by:
Dale Fye (Access MVP) earned 500 total points
ID: 39748615
Try this:

Dim Cust As as string

Select Case Me.frameProdGroup
    Case 1
        Cust = "IN (300,169)"
    Case 2
        Cust = "IN (444,329)"
    Case 3
        Cust = " = 238"
    Case 4
        Cust = " = 220"
    Case 5
        Cust = " = 235"
    Case 6
        Cust = " = 180"
End Select

strWhere = "[fkCustID]  " & Cust
DoCmd.OpenReport "rptStoxBase", acViewReport, , strWhere
0
 
LVL 1

Author Closing Comment

by:Stephen Byrom
ID: 39748648
Perfect!
Thanks for your time, you've saved me loads
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 39748656
glad I could help.

Happy New Year!
0

Featured Post

Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

947 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now