July 25, 2008 12:50pm pdt

1. capricorn1 548,987
2. boag2000 366,498
3. LSMCons... 273,449
4. DatabaseMX 257,158
5. peter57r 192,837
6. harfang 166,069
7. jimhorn 114,688
8. wiswalld 113,353
9. koutny 84,170
10. TextReport 76,173
11. MikeToole 72,053
12. eghtebas 70,977
13. frankytee 69,464
14. hnasr 62,269
15. ldunscombe 59,636

1. capricorn1 1,037,229
2. DatabaseMX 608,371
3. LSMCons... 450,817
4. boag2000 415,588
5. peter57r 409,806
6. jefftwilley 340,037
7. harfang 308,590
8. mbizup 248,969
9. jimhorn 218,235
10. frankytee 177,705
11. wiswalld 153,567
12. stevbe 146,536
13. MikeToole 136,871
14. GRayL 135,412
15. LPurvis 118,920

05.15.2008 at 06:49AM PDT, ID: 23405067

	[x] Attachment Details

[x]

The Solution Rating System

With so many solutions, how can you tell which solutions are most likely to help you and which ones are not? To provide you with a tool to use, we rate our solutions based on various elements that most accurately determine if a solution is a quality solution. To explain what factors affect the solution rating, here are the elements we take into consideration when formulating our solution rating.

The Grade of the Solution
The Zone Rank of the Expert Providing the Solution
The Number of Author and Expert Comments
The Number of Experts Contributing
The Feedback of the Community

Your Input Matters
Because of the way the system is set up, the most important variable in this equation is you. As a member of Experts Exchange, you are able to cast your vote on the quality of the solutions in regard to how complete, accurate, helpful and easy to understand each solution is. When you provide your feedback, each rating is adjusted accordingly. So, if you see a solution that has a poor rating that you think is a good solution, let us know by rating it. As you do, the rating will be adjusted and will become more accurate for other members of our site.

If you have any suggestions that you would like to make for our rating system, please ask a question in the Suggestions Zone of Community Support.

Thank you!

9.9

strSQL

Zones: Access Forms, Microsoft Access Database, Access Coding/Macros

I need some help with modifying/streamlining a strSQL.

I have a search form that contains 5 checkboxes. They are linked to fields:
- BinFinalReport
- BinHandbook
- BinOBE
- BinPAR
- BinRequiresClarification

Currently, the function works fine (creates the query as envisioned) as I'm using "AND" in the WHERE clause of the strSQL statement (see below):

"WHERE " & strFinalReport & " AND " & strHandbook & " AND " & strOBE & " AND " & strPAR & " AND " & strRequiresClarification & " " & _

So far so good...

Here's what I need some help with:
**********************************

I now have added an OptionGroup object (AND & OR). When replacing (hardcoded) the AND with the OR, I now pass TRUE and FALSE into the query criteria for all five (5) checkboxes.

Hence, I get 100% of the records with the OR in my WHERE clause.

Problem:
1. I only select e.g. 3 out of 5 checkboxes
2. Simultaneously, I select the "OR" in my optiongroup "fraAndOR"
3. based on #1, I only want the OR for those 3 checkboxes. The other 2 checkboxes should NOT get a FALSE passed into the query criteria.

Examples for the dynamic WHERE clause in the strSQL:

A. If I check "BinFinalReport" & "BinHandbook" and chose OR for fraAndOr, the WHERE clause should be:
"WHERE " & strFinalReport & " OR " & strHandbook & " " & _

B. If I check "BinFinalReport" & "BinHandbook" & "BinRequiresClarification" and chose OR for fraAndOr, the WHERE clause should be:
"WHERE " & strFinalReport & " OR " & strHandbook & " OR " & strRequiresClarification & " " & _

I've attached the small db (including 1 table, query, form, report). This may help facilitate the process. Pls change .txt to .mdb.

I'd welcome any ideas as to how I can dynamically modify the WHERE clause with the OR criteria based on only those selected checkboxes.

Thanks,
EEH

         
           
             1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:
39:
40:
41:
42:
43:
44:
45:
46:
47:
48:
49:
50:
51:
52:
53:
54:
55:
56:
57:
58:
59:
60:
61:
62:
63:
64:
65:
66:
67:
68:
69:
70:
71:
72:
73:

           
           
             'Declare variables
    Dim db As DAO.Database
    Dim qdf As DAO.QueryDef
    Dim varItem As Variant
    Dim strFinalReport As String
    Dim strHandbook As String
    Dim strOBE As String
    Dim strPAR As String
    Dim strRequiresClarification As String
    Dim strSQL As String
      
    
    'Get the PAR database and stored query
    Set db = CurrentDb()
    Set qdf = db.QueryDefs("qryReport")
    
      
    If Me!chkFinalReport.Value = True Then
        strFinalReport = " tblCTM.BinFinalReport = True"
     Else
        strFinalReport = " tblCTM.BinFinalReport = False"
    End If
      
      
    If Me!chkHandbook.Value = True Then
        strHandbook = " tblCTM.BinHandbook = True"
     Else
        strHandbook = " tblCTM.BinHandbook = False"
    End If
      
      
    If Me!chkOBE.Value = True Then
        strOBE = " tblCTM.BinOBE = True"
     Else
        strOBE = " tblCTM.BinOBE = False"
    End If
      
      
    If Me!chkPAR.Value = True Then
        strPAR = " tblCTM.BinPAR = True"
     Else
        strPAR = " tblCTM.BinPAR = False"
    End If
      
      
    If Me!chkRequiresClarification.Value = True Then
        strRequiresClarification = " tblCTM.BinRequiresClarification = True"
     Else
        strRequiresClarification = " tblCTM.BinRequiresClarification = False"
    End If
 
  
 
  
    If Me.fraAndOR.Value = "1" Then
      'Create WHERE  (for strSQL below) with 'AND' for all checked checkboxes
    Else
      'Create WHERE (for strSQL below) with 'OR' for all checked checkboxes
    End If
 
  
  
    strSQL = "SELECT tblCTM.TrackNoIDpk, tblCTM.BinFinalReport, tblCTM.BinHandbook, tblCTM.BinOBE, tblCTM.BinPAR, tblCTM.BinRequiresClarification FROM tblCTM " & _
             "WHERE " & strFinalReport & " AND " & strHandbook & " AND " & strOBE & " AND " & strPAR & " AND " & strRequiresClarification & " " & _
             "ORDER BY tblCTM.TrackNoIDpk;"
  
 
            'Apply the new SQL statement to specified query
            qdf.SQL = strSQL
            
 
            'Open report
            DoCmd.OpenReport "rptReviewPanelComments", acViewPreview

           
         

Attachments:

null

strSQL.txt (676 KB) (Possible File Type Mismatch)

Change .txt to .mdb

Start your free trial to view this solution

Question Stats

Zone: Microsoft

Question Asked By: ExpExchHelp

Solution Provided By: capricorn1

Participating Experts: 2

Solution Grade: A

Translate:

Loading Advertisement...

20080236-EE-VQP-29 / EE_QW_2_20070628