SQL recordset with wildcards

[jodym43]

Does anyone have a good example for an SQL recordset that uses wildcards in it's WHERE clause, I'm having a hard time getting my syntax correct in doing what I desire.

Specifically, I'm trying to select a record set based on the value of a MapCounty field. I want the search to find any record that has even a segment of what is entered into the field (i.e. if someone enters "lar" into the field on the form, I want it to find records with "clark" and also "larwy&quot, and to return all records if the field is left blank.

Here's an example of what I am using:

Dim dB As Database
Dim rst As Recordset
Dim Criteria As String
Dim strSQL As String

Criteria = "([MapCounty] like " & "'" & "*" & "' AND " & "'" & Me![MapCounty] & "'" & " AND "'" & "*" & "')"

strSQL = "select * from Maps WHERE" & Criteria

Set dB = CurrentDb
Set rst = dB.OpenRecordset(strSQL)



If I happen to create a Query, the SQL statement WHERE clause looks like this:
WHERE ((Maps.MapCounty) Like "*" & [Choose a County] & "*&quot

Any help is very much appreciated!!!!
Thanks

 

[Kalin]

Seem to me all the quotes and ampersands are messing everything up.

I think this should work

Criteria = "[MapCounty] Like ""*" & Me.MapCounty & "*"";" Grtz,

Kalin

 

[jodym43]

Thanks Kalin...that's exactly what I was looking for. I don't think I would have tried the format that you provided, but it worked beautifully.

I appreciate your help.