FindFirst problem

[Kiwi-Wombat]

Hi

I am having problems with FindFirst. I am trying to search for a word in a specific text field in a table. The text field is not a key field but is indexed.

Prior to calling the following code, varSearch (a string) has been assigned a value by user input. Assume that value is "England"

Dim db As DAO.Database
Dim rst As DAO.Recordset
Set db = CurrentDb
Set rst = db.OpenRecordset("MyTable", dbOpenDynaset)
rst.FindFirst "MyTableField = " & varsearch

It stops on FindFirst and gives a Run-time error 3070 - The Microsoft Jet database engine does not recognise 'England' as a valid field or expression.

However (and just for test purposes) if I hard code "England" like this instead of using a varaiable

Dim db As DAO.Database
Dim rst As DAO.Recordset
Set db = CurrentDb
Set rst = db.OpenRecordset("MyTable", dbOpenDynaset)
rst.FindFirst "[MyTableField] = 'England' "

It correctly stops on the first record that has 'England' in the text field

I obviously have a syntax problem but after 5 hours I can't figure it

Incidentally if varSearch is an integer and I use the same principle as my first example and search on the Key Field (an Autonumber), that works. It is only when the variable is a string that doesn't seem to work

Can anyone help please

Thanks

 

[frostysnoman]

Just a guess, but I think searchword needs to be more specific. IE. if you are requesting a searchword from the user in a form, I think it should look more like this. [form]![searchprompt].searchword. A little more detail would help.

 

[pbaldy]

You set a variable equal to your search word, but then didn't use it. You used the search word itself as if it was a variable. Access doesn't know what it is, so you get the error.

 

[Kiwi-Wombat]

Thanks frostysnoman & pbaldy.

I realise I was not specific enough with my query and also that I had an error in the code I had typed. I did search using a variable.

I have edited the post to explain myself better and corrected the code.

Would either of you of anyone else like to hazard a guess as to what is going on

Thanks

 

[pbaldy]

Try:

rst.FindFirst "MyTableField = '" & varsearch & "'"

 

[Kiwi-Wombat]

Oh OK.

Is that because it is a string variable as opposed to an integer?

 

[pbaldy]

Yes. You actually had the answer yourself; you surrounded the word England in single quotes in your successful test.

 

[Kiwi-Wombat]

Tried it last night & worked great

Thanks

 

[Kiwi-Wombat]

Hi again

I tried to expand on this by making MyTableField a variable as well. I assumed that I could just enclose varField in quotes but it never finds the item even though it exists.

Dim db As DAO.Database
Dim rst As DAO.Recordset
Set db = CurrentDb
Set rst = db.OpenRecordset("MyTable", dbOpenDynaset)
rst.FindFirst " ' " & varField & " ' " & " = " ' " & varValSrchField & " ' "

Note I have added spaces between the quotes to make it clearer

Using debug and looking at the code while stepping through it looks the same as when the actual field was coded instead of the variable.

What am I doing wrong?

 

[pbaldy]

Try

rst.FindFirst varField & " = '" & varValSrchField & "'"

 

[Kiwi-Wombat]

Again I owe you thanks

Worked well