Data Type Mismatch in Criteria Expression in MS-Access

Question

I am using Excel-VBA to insert data into a table. I have a .csv file that was parsed and used to create an insert string. I am using it on multiple tables in a row. The first table processes fine, but the second one gives me an error:

Run-time error '-2147217913 (8004e07)': Data type mismatch in criteria expression.

After researching this, it appears that I am trying to pass data in a bad form, but I'm not seeing where or how, as the first table processed. The final insert string that fails looks like:

INSERT INTO Table_Name VALUES ('Text_entry', 'Long_Integer_entry', 'Double_entry', '')

EDIT: I just tried another test and found the problem: any field that is a Double or Single Integer that gets passed the null '' causes the error. How do I pass a null value to these data types?

Have you tried with double quotes? Is `Table_Name` expecting strings for `Long_Integer_entry` and `Double_entry`? If not, remove the quotes there - quotes are used to delimit `String` values, not numbers. — Mathieu Guindon, Aug 19 '16 at 18:14
Does that mean if it is a number I have to omit all kinds of quotes? — SandPiper, Aug 19 '16 at 18:15
It is expecting the data type that I pseudo named the values being passed to it. I just tried another test and found the problem: any field that is a Double or Single Integer that gets passed the null '' causes the problem. How do I pass a null value to these data types? — SandPiper, Aug 19 '16 at 18:18
change your table fields to allow nulls or set the nulls as zero before inserting. — cyboashu, Aug 19 '16 at 18:24
Is that something I have to do in Access or can I do that through VBA as well while I am creating a new table? Is this only a problem for Doubles or all Number data types? — SandPiper, Aug 19 '16 at 18:35
"set the nulls as zero before inserting" is a terrible idea. Zero is a value — random_answer_guy, Aug 19 '16 at 20:01
The first question you should be asking is "is a null value in my input data valid, or is the CSV file I am using corrupt"? — YowE3K, Aug 19 '16 at 20:45
You can apply the function here to take care of this: [CSql](http://stackoverflow.com/questions/38728268/ms-access-2007-update-number-with-commas-fails/38740064#38740064) — Gustav, Aug 19 '16 at 21:26
The data I am using is not corrupt. The null values are either records that have no applicable entry for that column, or there is no current entry on it. Does it matter if I use Null instead of a zero length string for string data type entries? — SandPiper, Aug 22 '16 at 19:48

score 1 · Accepted Answer · answered Aug 20 '16 at 01:51

In SQL, you can append and update NULL in any data type column including string, numeric, or datetime values. So simply pass it in your append query. Be sure not to quote it as it is neither a string or numeric value. The NULL entity means an unknown value.

INSERT INTO Table_Name VALUES ('Text_entry', 'Long_Integer_entry', 'Double_entry', NULL)

Specifically, your attempted '' is actually a zero-length string (a non-NULL value) which is why it failed to work in a number column.

Should you use parameterized queries, also pass VBA's Null to bind to SQL statement:

strSQL = "INSERT INTO Table_Name VALUES (?, ?, ?, ?)"

Set cmd = New ADODB.Command

With cmd
     .ActiveConnection = conn
     .CommandText = strSQL
     .CommandType = adCmdText
     .CommandTimeout = 15
End With

' BINDING PARAMETERS
cmd.Parameters.Append _
      cmd.CreateParameter("textparam", adVarChar, adParamInput, 255, "Text_entry")
cmd.Parameters.Append _
      cmd.CreateParameter("longtxtparam", adVarChar, adParamInput, 255, "Long_Integer_entry")
cmd.Parameters.Append _
      cmd.CreateParameter("doubleparam", adDouble, adParamInput, , "Double_entry")
cmd.Parameters.Append _
      cmd.CreateParameter("nullparam", adDouble, adParamInput, , Null)

Using the "Null" keyword got the Doubles and Long Integers to process correctly, thank you! Is there a downside to using Null for strings instead of using a zero length string? — SandPiper, Aug 22 '16 at 19:47
Great to hear! Storage-wise, I don't know if it makes a difference. `NULL` tend to symbolize uninitialized values where as a zero-length string is a legitimate initialized value. Queries will differ and are not interchangeable: `[str] IS NULL` vs `[str] = ''`. All in all, it depends on your operations, akin to storing 0 or NULL for numbers. — Parfait, Aug 22 '16 at 20:26

Data Type Mismatch in Criteria Expression in MS-Access

1 Answers1