Data Type Mismatch in Criteria Expression Binary to Integer VBA

Question

I'm writing an Excel Macro which works with an existing Access DB. Several of the queries I run will require input from the user as to which users they remove from a table, but seeing as the number will be the same each time I'd rather capture it once at the beginning of the macro and use that variable in my SQL statements. The number is for a quarter number of the year. So it'll either be 1,2,3 or 4 I can capture that from the user ok, but when I try to use the variable in an SQL statement I get the "Data Type Mismatch in Criteria error"

Here is what I'm trying:

Dim Qrtnum As Integer

Qrtnum = Application.InputBox("Enter Quarter to Sample")

sql1 = "DELETE SampledEmailAddressesByQuarter.*, SampledEmailAddressesByQuarter.Quarter FROM SampledEmailAddressesByQuarter WHERE SampledEmailAddressesByQuarter.Quarter = "

appAccess.DoCmd.RunSQL sql1 & Qrtnum

Now in the table in my database the data type is listed as "Binary" and I'm trying to pass an Int variable into my SQL, could this be the problem? Or is the fact I have an Int concatenated with my SQL String?

Any help would be appreciated.

try using cstr to convert Qrtnum to a string ie,: & cstr(Qrtnum) — Mark Moore
Does your DELETE work if you change the datatype of SampledEmailAddressesByQuarter.Quarter to Integer or Long Integer? — HansUp
In response to Mark Moore, sadly that didn't do it for me. One thing I have tried is that it will work if I prompt for the quarter number as part of my SQL though with that approach I'd have to prompt each time which I want to avoid. i'll try HansUp's suggestion now. — Nexus490
@PaulFrancis - Access does support binary. The name of the column is masked as "Attachment" (I believe), but the underlying column type is binary. — Jason Faulkner
@PaulFrancis Re binary, see Microsoft Access Data Types. Although binary is available, I don't think it's a good choice for storing integer values. — HansUp

Nexus490 Nexus490 · Accepted Answer · 2015-01-22T15:25:40

Bit of a cop out answer, but the credit should go to @HansUp. In the end I converted the data type of the field to number and managed to copy in all the data after. My code works fine now it's not trying to deal with a Binary data type. Still interested to know how I'd deal with this though. Thanks for all the help.

Data Type Mismatch in Criteria Expression Binary to Integer VBA

1 Answers