?
Solved

Coded SQL statement using and with or

Posted on 2009-07-06
3
Medium Priority
?
184 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 2000 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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

719 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