INSERT INTO syntax error

[Guest]

I'm writing from memory so I may have some syntax incorrect.

I'm executing sql statements in VBA along the lines of:

strSQL = "Insert Into myTable Values (rst!text1, rst!number1, ....)"
execute strSQL

On a number of records in the recordset, blanks or nulls values are in the
rst!number1 field, which of course is defined as a decimal in myTable.

VBA returns a syntax error.

Is there anyway to these "stuff" blank or null values into a field defined
as decimal?

See, the record set is from a large database and I really don't want to
check data types, but rather want to pass it to the new table (myTable) with
out messing with what is there.

Thank you,
Mark

 

[Andi Mayer]

I'm writing from memory so I may have some syntax incorrect.

I'm executing sql statements in VBA along the lines of:

strSQL = "Insert Into myTable Values (rst!text1, rst!number1, ....)"
execute strSQL

On a number of records in the recordset, blanks or nulls values are in the
rst!number1 field, which of course is defined as a decimal in myTable.

use Nz(rst!number1,0) this function changes Nulls to 0 or whatever you
want

If you expect an answer to a personal mail, add the word "manfred" to the first 10 lines in the message
MW

 

[Tim Ferguson]

strSQL = "Insert Into myTable Values (rst!text1, rst!number1, ....)"
execute strSQL

On a number of records in the recordset, blanks or nulls values are in
the rst!number1 field, which of course is defined as a decimal in
myTable.

As far as I know, the execute statement won't see the rst! members, so your
memory should be telling you:

strSQL = "INSERT INTO MyTable (FieldOne, FieldTwo) " & _
"VALUES (""" & rst!text1 & "", " & rst1!number1 & ")"

but you are quite right that this will fail if the rst! members are null.

VBA returns a syntax error.

You need a valid string to stuff into the command string -- you need a NULL
without any delimiters:

strSQL = "INSERT etc etc )" & _
"VALUES (""Eric"", NULL)"


The simplest was is to make yourself a function something like

public function SQLDate(SomeDate as Variant) as string
' this is the Jet format: it's different for MSDE!
const c_strFormat = "\#yyyy\-mm\-dd\#"
if isnull(somedate)
sqldate = "NULL"
elseif isdate(somedate)
sqldate = format$(somedate, c_strFormat)
else ' can't coerce illegal value
err.raise SomeUsefulErrorNumber
end if
end if

and the same sort of things for real numbers and strings, etc.

Is there anyway to these "stuff" blank or null values into a field
defined as decimal?

See above. Remember that Access/VBA has major problems with decimal fields
-- they sort and compare wrong. Google on this group for more details.

Hope that helps


Tim F

 

[Guest]

Thanks for the comments!!!!