Solved

Coded SQL statement using and with or

Posted on 2009-07-06
3
181 Views
Last Modified: 2013-11-28
Hi, I have very nearly finished creating a form which builds up an SQL statement to create a query which helps user build a mailing list.  There are lots of different criteria, most of which is pretty straightforward, and working well.  However the "status" of each customer is selected from a drop down list. I want the user to be able to select three different types of status for their criteria -  for example they may want to know which customers are "new", "existing" and on "trial" in one hit.  This works fine using "or" but the problem comes when I then add on more criteria using "and" - the statement it builds just can't handle it.  I think this is because the statement should read something like

WHERE (((Children.Status)="Interested") AND ((Children.HadTrial)=Yes)) OR (((Children.Status)="rejected") AND ((Children.HadTrial)=Yes))

My problem is that I'm very green when it comes to doing a. writing SQL and b. trying to build up a string using it!  How do I get this to work using a similar process as I have below (I don't really want to re-write the whole thing!)

Please bear with me if I disappear for periods of time.  I have a young family who need feeding, bedtime stories etc.  - I will keep track and follow up as soon as I can.
Function BuildSQLString(strSQL As String) As Boolean
 
Dim strSELECT As String
Dim strFROM As String
Dim strWHERE As String
Dim qdf As QueryDef
Dim boolResultCode As Boolean
 
strSELECT = "CustomerNumber, Title, FirstName, c.LastName, [Street&HouseNumber], [Town/City], County, [Post-Code], HomeTelephoneNumber, MobilePhoneNo, CarerattendingSession, MarketingID, EMail, ChFirstName, s.LastName, DateofBirth, Dateoftrial, s.ClassName, Status "
 
strFROM = "(customers c INNER JOIN children s " & _
        "ON c.CustomerNumber = s.CustomerNo) "
    
    If chkStatus1 Then
        strWHERE = strWHERE & " AND s.status = " & Chr(34) & Me!cboStatus1 & Chr(34)
    End If
 
    If chkStatus2 Then
        strWHERE = strWHERE & " OR s.status = " & Chr(34) & Me!cboStatus2 & Chr(34)
    End If
 
    If chkStatus3 Then
        strWHERE = strWHERE & " OR s.status = " & Chr(34) & Me!cboStatus3 & Chr(34)
    End If
    
    If chkBirthDate Then
        If Not IsNull(txtBirthdate1) Then
            strWHERE = strWHERE & " AND s.dateofbirth >= " & _
            "#" & Format$(txtBirthdate1, "mm/dd/yyyy") & "#"
        End If
        
        If Not IsNull(txtBirthdate2) Then
            strWHERE = strWHERE & " AND s.dateofbirth <= " & _
            "#" & Format$(txtBirthdate2, "mm/dd/yyyy") & "#"
        End If
    End If
    
    If chkPostCode Then
        strWHERE = strWHERE & " AND [Post-Code] Like " & Chr(34) & txtPostCode & "*" & Chr(34)
    End If
      
    If chkTown Then
        strWHERE = strWHERE & " AND [Town/City] = " & Chr(34) & txtTown & Chr(34)
    
    End If
    
    If chkCounty Then
        strWHERE = strWHERE & " AND County = " & Chr(34) & txtCounty & Chr(34)
    
    End If
    
    If chkHeard Then
        strWHERE = strWHERE & " AND MarketingID = " & cboHeard
    End If
    
    If chkDateContacted Then
        If Not IsNull(txtDateContacted1) Then
            strWHERE = strWHERE & " AND c.datefirstcontacted >= " & _
            "#" & Format$(txtDateContacted1, "mm/dd/yyyy") & "#"
        End If
        
        If Not IsNull(txtDateContacted2) Then
            strWHERE = strWHERE & " AND c.datefirstcontacted <= " & _
            "#" & Format$(txtDateContacted2, "mm/dd/yyyy") & "#"
        End If
    End If
    
    If chkTrial Then
        strWHERE = strWHERE & " AND s.Hadtrial = " & cboTrial
    End If
    
    If chkWaiting Then
        strWHERE = strWHERE & " AND s.WaitingList = " & cboTrial
    End If
    
    If chkReenrol Then
        strWHERE = strWHERE & " AND [Re-enrol] = " & cboReenrol
    End If
    
    If chkClass Then
        strWHERE = strWHERE & " AND s.ClassName = " & Chr(34) & cboClass & Chr(34)
    End If
    
    If chkTeacher Then
        strFROM = strFROM & " INNER JOIN Classes " & _
        "ON s.ClassName = Classes.ClassName "
        strWHERE = strWHERE & " AND Classes.TeacherName = " & Chr(34) & cboTeacher & Chr(34)
    End If
    
    If chkVenue Then
        If chkTeacher Then
            strWHERE = strWHERE & " AND Classes.VenueID = " & cboVenue
        Else: strFROM = strFROM & " INNER JOIN Classes " & _
                "ON s.ClassName = Classes.ClassName "
                strWHERE = strWHERE & " AND Classes.VenueID = " & cboVenue
        End If
    End If
        
       
strSQL = "SELECT " & strSELECT
strSQL = strSQL & "FROM " & strFROM
If strWHERE <> " " Then strSQL = strSQL & "WHERE " & Mid$(strWHERE, 6)
 
BuildSQLString = True
 
If strSQL = "" Then
    boolResultCode = False        'no query string to use!
  Else
    On Error Resume Next          'get ready to handle any errors
    Set qdf = CurrentDb.CreateQueryDef("qryMailingList") 'create the new query object
    If Err.Number <> 0 Then
      boolResultCode = False      'failed for some reason so cannot continue
    Else
      qdf.SQL = strSQL            'succeeded so load the query object with the query string
      qdf.Close                   'finished with the query object so close it
      RefreshDatabaseWindow       'update the Query window
      boolResultCode = True       'Report a success
    End If
    On Error GoTo 0               'reset error handler
  End If
  
  MakeQueryDef = boolResultCode   'report what happened
 
 
End Function

Open in new window

0
Comment
Question by:luigi_prego
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 12

Accepted Solution

by:
ErezMor earned 500 total points
ID: 24787254
replace lines 14 to 24 (the criteria for the checkboxes) with the attached code

If chkStatus1 Or chkStatu2 Or chkStatus3 Then
    If chkStatus1 Then
        strWhere = strWhere & Chr(34) & Me!cboStatus1 & Chr(34) & ","
    End If
    If chkStatus2 Then
        strWhere = strWhere & Chr(34) & Me!cboStatus2 & Chr(34) & ","
    End If
    If chkStatus3 Then
        strWhere = strWhere & Chr(34) & Me!cboStatus3 & Chr(34) & ","
    End If
    strWhere = " And s.Status In(" & Left(strWhere, Len(strWhere) - 1) & ")"
End If

Open in new window

0
 

Author Comment

by:luigi_prego
ID: 24787382
Thank you so much ErezMor, worked like a dream and learned something in the process.  Thanks - full points winging their way to you, Louise
0
 

Author Closing Comment

by:luigi_prego
ID: 31600241
Thanks for your help
0

Featured Post

Enroll in June's Course of the Month

June's Course of the Month is now available! Every 10 seconds, a consumer gets hit with ransomware. Refresh your knowledge of ransomware best practices by enrolling in this month's complimentary course for Premium Members, Team Accounts, and Qualified Experts.

Question has a verified solution.

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

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
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 …
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

734 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