Solved

Coded SQL statement using and with or

Posted on 2009-07-06
3
174 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
  • 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
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.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

912 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

17 Experts available now in Live!

Get 1:1 Help Now