Hi everyone
i'm having an issue with pushing into Access DB numeric field empty
value from Excel cell using ADO
the following INSERT statement is built dymanicly picking values from
cells and is executed from Excel macro through ADO Execute command: if
a cell which related to field between #11/15/2004# and "blah" is empty
(excel format for that cell is general) the code throughs err message
that type mismatch..Access field set up:
DataType as number
Field size is Double
Required NO
NO Validation Rules , NO Validation Text

SQL statement gives err message:
INSERT INTO Tbl VALUES (99041838306,#11/15/2004#,,"blah")

this record will populate DB without problems:
INSERT INTO Tbl VALUES (99041838306,#11/15/2004#,0,"blah")
thank U in advance
Dim

this works for me..

oCN.Execute "INSERT INTO tmp_test (id,rank) VALUES ('ABC',Null)"

--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam

Dim wrote in message
om:

Hi everyone
i'm having an issue with pushing into Access DB numeric field empty
value from Excel cell using ADO
the following INSERT statement is built dymanicly picking values from
cells and is executed from Excel macro through ADO Execute command: if
a cell which related to field between #11/15/2004# and "blah" is empty
(excel format for that cell is general) the code throughs err message
that type mismatch..Access field set up:
DataType as number
Field size is Double
Required NO
NO Validation Rules , NO Validation Text

SQL statement gives err message:
INSERT INTO Tbl VALUES (99041838306,#11/15/2004#,,"blah")

this record will populate DB without problems:
INSERT INTO Tbl VALUES (99041838306,#11/15/2004#,0,"blah")
thank U in advance
Dim

From the Help entry on INSERT INTO

"... you can specify the value for each field in a single new record using
the VALUES clause. If you omit the field list, the VALUES clause *must*
include a value for every field in the table; otherwise, the INSERT
operation will fail.-- "

Note the use of the word "must"...

VALUES (99041838306,#11/15/2004#,,"blah") doesn't supply a value for the 3rd
field. Therefore, it fails.
VALUES (99041838306,#11/15/2004#,0,"blah") does supply a value for the 3rd
field. It suceeds.


HTH,

George Nicholson

Remove 'Junk' from return address.


"Dim" wrote in message
m...
Hi everyone
i'm having an issue with pushing into Access DB numeric field empty
value from Excel cell using ADO
the following INSERT statement is built dymanicly picking values from
cells and is executed from Excel macro through ADO Execute command: if
a cell which related to field between #11/15/2004# and "blah" is empty
(excel format for that cell is general) the code throughs err message
that type mismatch..Access field set up:
DataType as number
Field size is Double
Required NO
NO Validation Rules , NO Validation Text

SQL statement gives err message:
INSERT INTO Tbl VALUES (99041838306,#11/15/2004#,,"blah")

this record will populate DB without problems:
INSERT INTO Tbl VALUES (99041838306,#11/15/2004#,0,"blah")
thank U in advance
Dim