VBA with Access runtime error 3070

1
votes

I have a database with a multiple fields 3 of which are: ID which is an autonumber IDtype which is text COMPANY which is text as well

with the variable: Dim rs As DAO.Recordset

when I do rs.FindFirst on the table I get the following results

rs.FindFirst "ID=367" -> code runs fine but this way only allows me to get that one specific record

rs.FindFirst "IDtype='71'" -> code runs fine

rs.FindFirst "COMPANY=XDRT" -> runtime error '3070': Access db engine does not recognize 'COMPANY' as a valid field name or expression (I also tried putting single quotes around XDRT and still same error)

The only difference between COMPANY field and IDtype field is the format is set to "@" for the COMPANY field

Any help on this would be much appreciated. Thanks in advance

2
vbams-access
The correct syntax is: rs.FindFirst "COMPANY='XDRT'", have you tried it? - jacouh

2 Answers

1
votes

As my comment noted, the correct syntax is: 

rs.FindFirst "COMPANY='XDRT'"

, have you tried it?

Moreover, you probably have error when creating DAO.Recordset.

Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("MyTable", dbOpenDynaset)

As such all the tree fields are selected: ID, IDtype and COMPANY.

0
votes

If Company has a datatype of Text, you need to use single quotes to search for a string in that case. Try this:

rs.FindFirst "COMPANY='XDRT'"