Passing Variables inot 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


--
Regards

Graham

Graham Haines

 

[Allen Browne]

Answered in m.p.a.

 

[John W. Vinson]

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

Your strsearch needs to be a valid SQL WHERE clause without the word WHERE. If
the value of the variable strcol is the fieldname, then you need to build the
strsearch string by appending the VALUE of the variable, not its name:

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

This will set strsearch to

[MyField] = 'targetvalue'

or the like. I'd recommend including the brackets, they'll protect you if you
have fieldnames that contain blanks or other special characters, and they
won't do any harm.

Not sure what you intended by appending an empty string "" to the end of your
expression - that's like adding zero to a number, it does nothing whatsoever!