buildcriteria and query by form

[odun]

Hello all,

I need your urgent help with this:

I found the following code on the microsoft website (Q304302) to help with creating a dynamic qbf.

Private Sub cmdSearch_Click()
    On Error Resume Next

    Dim ctl As Control
    Dim sSQL As String
    Dim sWhereClause As String

    'Initialize the Where Clause variable.
    sWhereClause = " Where "

    'Start the first part of the select statement.
    sSQL = "select * from customers "

    'Loop through each control on the form to get its value.
    For Each ctl In Me.Controls
        With ctl
            'The only Control you are using is the text box.
            'However, you can add as many types of controls as you want.
            Select Case .ControlType
                Case acTextBox
                    .SetFocus
                    'This is the function that actually builds
                    'the clause.
                    If sWhereClause = " Where " Then
                        sWhereClause = sWhereClause & BuildCriteria(.Name, dbtext, .Text)
                    Else
                        sWhereClause = sWhereClause & " and " & BuildCriteria(.Name, dbtext, .Text)
                    End If
            End Select
        End With
    Next ctl

    'Set the forms recordsource equal to the new
    'select statement.
    Me.txtSQL = sSQL & sWhereClause
    Me.RecordSource = sSQL & sWhereClause
    Me.Requery

End Sub

The code above works only if I have just have a single criterium in each of my textboxes in my form, e.g.
ca

But when I put in:
ca or fl

It does not work. Can you please help in revising this so that it can do the above?

Thanks a lot for your help!

 

[Bat17]

I assume that your problem is that it does not bracket the or's. you could try training your users to use IN() rather than OR on a test using IN() in two fields I got
select * from customers Where Text 1 In ("ca","fl") and Text4 In ("red","blue","yellow")

Note though that it does not cope with field names with spaces in!

HTH

Peter

 

[odun]

Hi, thanks.

Are you saying that in my textbox on the form, I should type:

IN("ca","fl")

I also tried

IN(ca,fl)

IN(ca fl)

but it just comes up blank.

Thanks

 

[Bat17]

IN(ca, fl) should work, I just tried it on a quick demo form and it worked OK.

 

[odun]

Hi, it didn't work, do I need to adjust the code in anyway, if so, what do I include or exclude to make this work?

 

[Bat17]

Been doing some more playing
Here is what I've used.

Private Sub cmdSearch_Click()
    On Error Resume Next

    Dim ctl As Control
    Dim sSQL As String
    Dim sWhereClause As String
    Dim strIn As String

    'Initialize the Where Clause variable.
    sWhereClause = " Where "

    'Start the first part of the select statement.
    sSQL = "select * from customers "

    'Loop through each control on the form to get its value.
    For Each ctl In Me.Controls
        With ctl
            'The only Control you are using is the text box.
            'However, you can add as many types of controls as you want.
            Select Case .ControlType
                Case acTextBox
                    .SetFocus
                    strIn = .Text
                    strIn = checkOr(strIn)
                    'This is the function that actually builds
                    'the clause.
                    If sWhereClause = " Where " Then
                        sWhereClause = sWhereClause & BuildCriteria(.Name, dbText, strIn)
                    Else
                        sWhereClause = sWhereClause & " and " & BuildCriteria(.Name, dbText, strIn)
                    End If
            End Select
        End With
    Next ctl

    'Set the forms recordsource equal to the new
    'select statement.
'    Me.txtSQL = sSQL & sWhereClause
'    Me.RecordSource = sSQL & sWhereClause
'    Me.Requery
Debug.Print sSQL & sWhereClause
End Sub

Function checkOr(strIn) As String
strIn = Nz(strIn, "")
If InStr(strIn, " or ") Then
    checkOr = "in(" & Join(Split(strIn, " or "), ", ") & ")"
Else
    checkOr = strIn
End If
End Function

It still has the problem with field names that have spaces, and if you use numbers in a text field you need to wrap them in quotes or Access will treat them as numeric.

in text4 - red or blue or green
in text1 - "1" or 2 or 3

Produced
select * from customers Where Text1 In ("1",2,3) and Text4 In ("red","blue","green")

Not tested it in a query but it looks like it should work.

HTH

Peter

 

[odun]

Thanks for helping with this, but when I tried this in the textbox:
ca, fl

It didn't work....can you check it please, thanks!!!

I have also noticed that when I leave all the textboxes blank, it doesn't spill out the whole data, that's what it should do because there is no criteria, can you help.

Any ideas welcome! Thanks

 

[Bat17]

did you leave the debug line in so that you can see what SQL is being generated? that is your start point. if it looks right copy it into a query and see if it returns data.

Peter

 

[odun]

did you leave the debug line in so that you can see what SQL is being generated? that is your start point. if it looks right copy it into a query and see if it returns data.

Peter

Thanks again:

Here is the sql in the textbox:

select * from customers Where Ship_to_ In ("ca","fl")

When I type in: ca or fl

I copied and pasted: In ("ca","fl") in one of the queries and it worked, but it's not working through my form, so I think I should backtrack a little.

I have a query: "q1certnon" that has a criterium to filter off some states.

I have another query, q1cert that is based on q1certnon.

and this has the query by form formulas for 13 fields e.g.

[Company] Like [Forms]![QBF_Form]![Sales] & "*" Or [Forms]![QBF_Form]![Sales] Is Null

and then I created a comman button and inserted the code you have been helping me with, I also created a textbox to show the sql.

So, do you know where I am going wrong here?

Thanks much again!