CODEWITHSUNDEEP
sqlms-accessvbaruntime-error
Muhnamana
Muhnamana

Reputation: 1044

Microsoft Access can't find the field '|1'

I keep getting a run time error '2465' when running a query via VBA in Access.

Error: Microsoft Access can't find the field '|1' referred to in your expression

I can't seem to find where this issue is occuring. Below is the VBA code that I'm currently using to requery a form.

Dim Test As String
Test = "*" & Combo161.Value

Dim strSQL As String
Dim strWhere As String
strWhere = (Chr(34) + Test + (Chr(34)))

'MsgBox (strWhere)

strSQL = "SELECT * FROM Test_Query WHERE TestID " & strWhere

'MsgBox (strSQL)
[Form_Test (subform)].RecordSource = strSQL
[Form_Test (subform)].Requery

The TestID had a field formatting of text, rather than a number. Does this matter at all?

Upvotes: 3

Views: 21406

Answers (4)

Jason T. Fleishman
Jason T. Fleishman

Reputation: 11

I, too, had the same error and confirm it is a double double quote issue. You dim'ed

strWhere = (Chr(34) + Test + (Chr(34)))

which produces ""*" & Combo161.Value" resulting in the bad MS Access error message "can't find ..."

I would try variations on the double quotes. The following solved the issue for me:

"[Business Card ID]= " & Forms![Letterhead]![N/A] & " And [L2] Like '*Competency Questions'"

Note I used single quotes. I believe using single quotes versus double quotes is a mere issue of preference.

Upvotes: 1

rob
rob

Reputation: 31

I have just fixed this error. I was referencing the subform's source object, rather than its name given in the form properties.

Upvotes: 3

Tahir
Tahir

Reputation: 21

I had the same error. What I missing was the double quotes around a string. This error is a bit misleading. Check the syntax etc and you will find the issue was related to comma or double quotes etc.

Upvotes: 2

Fionnuala
Fionnuala

Reputation: 91376

Try:

Dim Test As String
Test = "*" & Combo161.Value

Dim strSQL As String
Dim strWhere As String
strWhere = (Chr(34) & Test & (Chr(34)))

'MsgBox (strWhere)

strSQL = "SELECT * FROM Test_Query WHERE TestID Like " & strWhere

'To test
'Debug.print strSQL

If this is a subform, then:

Me.[Form_Test (subform)].Form.RecordSource = strSQL
''Not needed when changing record source
''Me.[Form_Test (subform)].Form.Requery

You did not have an equals sign / Like and the concatenator in VBA is &, not +, using + can lead to problems with nulls, but in this case, I reckon the problen is the missing Like, that is 

TestID Like "*something"

You can control the contents of a subform with a combo and a link field:

combo link

Upvotes: 1

Related Questions