strCriteria

I use a pop-up form that dynamically generates a query which is used as rowsource for another form.

This pop-up form uses ID number to filter for the records.   This process works great (see SQL between &&&&s).

Now, I need to use the same concept for another pop-form.  In this case, however, I'm want to use a text string that needs to be added to the query criteria.   For the SQL (between ****s), I added additional double-quotes to the strCriteria variable.   Unfortunately, the query is not created when executing the SQL.  

What's missing in the SQL (between *******s) so that it accepts text string in field [UserCreated]?

Thanks,
EEH


&&&&&&  Works great when adding NUMBER data type to query criteria (such as "In") &&&&&&

'Loop through selected items in the list box and build text string
If Me!lstCTM.ItemsSelected.Count > 0 Then
    For Each varItem In Me!lstCTM.ItemsSelected
        If Len(strCriteria) > 0 Then
            strCriteria = strCriteria & ", "
        End If
        strCriteria = strCriteria & Me!lstCTM.ItemData(varItem)
    Next varItem
    strCriteria = "[tblCTM].TrackNoIDpk IN (" & strCriteria & ")"
Else
    strCriteria = "[tblCTM].TrackNoIDpk Like '*'"
End If                                    
                 
strSQL = "SELECT tblCTM.TrackNoIDpk, tblCTM.ClonedTrackNoIDpk, tblCTM.PARIDfk, tblCTM.CollectionAnalysis, tblCTM.CTM, tblCTM.CTMSublevel, " & _
         "tblCTM.Source, tblCTM.WorkingGroup, tblCTM.Observation, tblCTM.Discussion, tblCTM.Recommendation, tblCTM.DateCreated, tblCTM.UserCreated, tblCTM.DateModified, tblCTM.UserModified, tblCTM.ReportDate FROM tblCTM " & _
         "WHERE " & strCriteria & ";"

&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&         
         
 

******* Attempted to use above concept to add text data type to query criteria (such as 'joe.schmo', 'jane.doe') **************
 
'Loop through selected items in the list box and build text string
If Me!lstUser.ItemsSelected.Count > 0 Then
    For Each varItem In Me!lstUser.ItemsSelected
        If Len(strCriteria) > 0 Then
            strCriteria = strCriteria & ", "
        End If
        strCriteria = strCriteria & Me!lstUser.ItemData(varItem)
    Next varItem
    strCriteria = "[tblCTM].UserCreated """ & strCriteria & """"
Else
    strCriteria = "[tblCTM].UserCreated Like '*'"
End If                                    
                 
strSQL = "SELECT tblCTM.TrackNoIDpk, tblCTM.ClonedTrackNoIDpk, tblCTM.PARIDfk, tblCTM.CollectionAnalysis, tblCTM.CTM, tblCTM.CTMSublevel, " & _
         "tblCTM.Source, tblCTM.WorkingGroup, tblCTM.Observation, tblCTM.Discussion, tblCTM.Recommendation, tblCTM.DateCreated, tblCTM.UserCreated, tblCTM.DateModified, tblCTM.UserModified, tblCTM.ReportDate FROM tblCTM " & _
         "WHERE """ & strCriteria & """;"

********************************
ExpExchHelpAsked:
Who is Participating?
 
Rey Obrero (Capricorn1)Connect With a Mentor Commented:
for text data you have to enclosed each item with a single  quote (chr(39))

strCriteria = strCriteria & chr(39) & Me!lstUser.ItemData(varItem) & chr(39)
0
 
ExpExchHelpAuthor Commented:
Perfect... thanks for providing me this feedback.

EEH
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.