Search string from form adds an extra "

[hgus393]

Hi all,

I have some code that I found on the net for searching on a form using a listbox. I also have in my form a text box that I would like to use in conjunction with the list box. I have the following code:

Private Sub Command26_Click()
 'Declare variables
    Dim db As DAO.Database
    Dim qdf As DAO.QueryDef
    Dim varItem As Variant
    Dim varItem1 As Variant
    Dim strCriteria As String
    Dim strSQL As String
 
' Get the database and stored query
    Set db = CurrentDb()
    Set qdf = db.QueryDefs("QueryFindWithDates>Q")
' Loop through the selected items in the list box and build a text string
    If Me!InstrLista.ItemsSelected.Count > 0 Then
        For Each varItem In Me!InstrLista.ItemsSelected
            strCriteria = strCriteria & "[QueryFindWithDatesQ].[CLEANNAME] = " & Chr(34) _
                          & Me!InstrLista.ItemData(varItem) & Chr(34) & " AND QueryFindWithDatesQ.[Per Date] = " & Chr(34) & [Forms]![DAD]![Per Date] & """ Or """
 
        Next varItem
        strCriteria = Left(strCriteria, Len(strCriteria) - 6)
    Else
        strCriteria = "[QueryFindWithDates>Q].[CLEANNAME] Like '*'"
    End If
 
 
             Debug.Print strSQL
' Apply the new SQL statement to the query
    qdf.sql = strSQL
' Open the query
    DoCmd.OpenQuery "QueryFindWithDates>Q"
' Empty the memory
    Set db = Nothing
    Set qdf = Nothing
End Sub

This returns in the Immediate window:

SELECT * FROM QueryFindWithDatesQ WHERE [QueryFindWithDatesQ].[CLEANNAME] = "JANE" AND QueryFindWithDatesQ.[Per Date] = "2011-01-31" Or "[QueryFindWithDatesQ].[CLEANNAME] = "BOB" AND QueryFindWithDatesQ.[Per Date] = "2011-01-31;

The problem is the starting " after the OR (I Believe???)

Does anyone know how to remove that " ?

Bob

 

[jdraw]

I'm not sure why you get the ". You don't have a closing " after the last 2011-01-31 either.
But, just a comment on your generated SQL.

If [Per Date] is a Date data type, Access/Jet expects a # as delimiter.
That is ....[Per Date] = #2011-01-31# is the correct syntax.

I would suggest using a naming convention that does not have spaces in field names.
And no special characters in your assigned names eg "QueryFindWithDates>Q"

There is a Lists related tutorial at this site that may provide some additional info.
http://www.fontstuff.com/access/acctut11.htm

Good luck

 

[DCrake]

The resulting syntax should be

"SELECT * FROM QueryFindWithDatesQ WHERE [CLEANNAME] In('Bob','Jane') AND [Per Date] = #2011-01-31#;"

 

[CBrighton]

How about this?

Changes highlighted in bold. Changed the """ before the or to Chr(34), changed the """ after or to ", changed the trim to remove the last 4 characters (which is " or ").

 If Me!InstrLista.ItemsSelected.Count > 0 Then
For Each varItem In Me!InstrLista.ItemsSelected
strCriteria = strCriteria & "[QueryFindWithDatesQ].[CLEANNAME] = " & Chr(34) _
& Me!InstrLista.ItemData(varItem) & Chr(34) & " AND QueryFindWithDatesQ.[Per Date] = " & Chr(34) & [Forms]![DAD]![Per Date] [B]& Chr(34) & " Or "[/B]
 
Next varItem
strCriteria = Left(strCriteria, Len(strCriteria) - [B]4[/B])
Else
strCriteria = "[QueryFindWithDates>Q].[CLEANNAME] Like '*'"
End If

:edit:

jdraw is correct though, it should be # around dates, not " or ', which would make it:

 If Me!InstrLista.ItemsSelected.Count > 0 Then
For Each varItem In Me!InstrLista.ItemsSelected
strCriteria = strCriteria & "[QueryFindWithDatesQ].[CLEANNAME] = " & Chr(34) _
& Me!InstrLista.ItemData(varItem) & Chr(34) & " AND QueryFindWithDatesQ.[Per Date] = [B]#" & [/B][Forms]![DAD]![Per Date] [B]& "# Or "[/B]
 
Next varItem
strCriteria = Left(strCriteria, Len(strCriteria) - [B]4[/B])
Else
strCriteria = "[QueryFindWithDates>Q].[CLEANNAME] Like '*'"
End If

 

[hgus393]

Yep that did it. Thanks alot!!

How about this?

Changes highlighted in bold. Changed the """ before the or to Chr(34), changed the """ after or to ", changed the trim to remove the last 4 characters (which is " or ").

 If Me!InstrLista.ItemsSelected.Count > 0 Then
For Each varItem In Me!InstrLista.ItemsSelected
strCriteria = strCriteria & "[QueryFindWithDatesQ].[CLEANNAME] = " & Chr(34) _
& Me!InstrLista.ItemData(varItem) & Chr(34) & " AND QueryFindWithDatesQ.[Per Date] = " & Chr(34) & [Forms]![DAD]![Per Date] [B]& Chr(34) & " Or "[/B]
 
Next varItem
strCriteria = Left(strCriteria, Len(strCriteria) - [B]4[/B])
Else
strCriteria = "[QueryFindWithDates>Q].[CLEANNAME] Like '*'"
End If

:edit:

jdraw is correct though, it should be # around dates, not " or ', which would make it:

 If Me!InstrLista.ItemsSelected.Count > 0 Then
For Each varItem In Me!InstrLista.ItemsSelected
strCriteria = strCriteria & "[QueryFindWithDatesQ].[CLEANNAME] = " & Chr(34) _
& Me!InstrLista.ItemData(varItem) & Chr(34) & " AND QueryFindWithDatesQ.[Per Date] = [B]#" & [/B][Forms]![DAD]![Per Date] [B]& "# Or "[/B]
 
Next varItem
strCriteria = Left(strCriteria, Len(strCriteria) - [B]4[/B])
Else
strCriteria = "[QueryFindWithDates>Q].[CLEANNAME] Like '*'"
End If