Access Query - correct Format for a statement in the MS Access query

Question

I have VBA codes in Access Database. I'm getting an error message "Run Time Error 3464 - Data Type Mismatch in Expression" with the following lines in my codes. What is the correct format of this line? I'm sure its a simple quotation mark or something missing from the line.

Within the Database, there is column called APIC Members. I want only the Records that have "1" listed in the cells.

Table$ = "SELECT * From WHY_Open_Cases_YTD WHERE WHY_Open_Cases_YTD.[APIC Member] = 1;"
Set RST = myDB.OpenRecordset(Table$)

Please advise what I'm doing wrong. When I Debug the message, I get the Set RST = myDB.OpenRecordset (Table$) is highlighted.

HansUp · Answer 1 · 2012-04-05T05:46:27.200

1

On my Access 2003 and 2007 systems, the complete description for error #3464 is "Data type mismatch in criteria expression." The db engine is complaining about the SQL statement you're asking it to use.

If [APIC Member] is text data type instead of numeric data type, add quotes around the value you compare against.

Table$ = "SELECT * From WHY_Open_Cases_YTD WHERE [APIC Member] = '1';"

edited Apr 05 '12 at 05:46

answered Apr 05 '12 at 05:40

HansUp

95,961
11
77
135

Thanks. The data type is not text. It is a number (the number will be either one or zero. Should I still add quotes? – JenP Apr 05 '12 at 11:07
In that case, try it just one time with the quotes and tell us what error message you get. You can remove them later. Something seems odd about this, and the error message may shed some light. – HansUp Apr 05 '12 at 13:49

score 0 · Answer 2 · edited May 23 '17 at 11:50

0

"Data Type Mismatch" sounds to me as if you are opening the wrong type of Recordset.
myDB.OpenRecordset() expects a DAO.Recordset, and your RST is probably an ADODB.Recordset.

See this answer for a more in-depth explanation:
Open recordset in Access 2003/2007

edited May 23 '17 at 11:50

Community

1
1

answered Apr 05 '12 at 05:10

Christian Specht

35,843
15
128
182

score 0 · Answer 3 · edited Apr 15 '16 at 16:03

0

Try

Table$ = "SELECT * From WHY_Open_Cases_YTD WHERE '[APIC Member]' = '1';"

I had same issue with DAO, and it turns out that it will not accept the SQL query until you have quoted the field name as well.

edited Apr 15 '16 at 16:03

Paul Roub

36,322
27
84
93

answered Apr 15 '16 at 16:01

René Mouritsen

5
1

This is wrong and will not work. Also, you are aware that the question is 4 years old? – Andre Apr 16 '16 at 21:14

Access Query - correct Format for a statement in the MS Access query

3 Answers3