Passing Variables into Search Criteria

[Graham M Haines]

Can anyone help me with this one

I want to pass 2 variables into a search criteria to use with FINDFIRST

strsearch = "strcol = '" & strval & "'" & ""

Set rst = mdb.OpenRecordset(strtable, dbOpenDynaset)
With rst
.FindFirst (strsearch)
End With

strcol is a string variable containing the column name in the table, and
strval is the value to find. Normally I use a predefined column name ie

strsearch = "[POSTCODE] ='" & strpostcode & "' AND [BIRTHDTE] = '" & strdob
& "' AND [QUALAIM] = '" & strQualaim & "'" & ""

Thanks

Graham







--
Regards

Graham

Graham Haines

 

[Ken Snell \(MVP\)]

strsearch = "[" & strcol & "] = '" & strval & "'"

 

[Allen Browne]

Create a query statement that only retrieves the records that match the
criteria.

This kind of thing:

strSearch = "(POSTCODE ='" & strpostcode & _
"') AND (BIRTHDTE = #" & strdob & _
"#) AND (QUALAIM = '" & strQualaim & "')"
Set rs = mdb.OpenRecorset("SELECT .* FROM " & strTable & " WHERE " &
strSearch & ";")

Note that the delimiters are important. Use quotes on Text fields, # on Date
fields, an no delimiter on Number fields.

If this program could be used outside the US, then the formatting of the
dates is important too. If you are in the UK, you need to use the US format
for literal dates you concatenate into criteria strings. More about that in:
International Date Formats in Access
at:
http://allenbrowne.com/ser-36.html