How can I insert NULL values in an in-line SQL statement executed in VBA?

How can I insert NULL values in an in-line SQL statement executed in VBA?

Post by teddysnip » Sat, 12 May 2007 02:45:32


I have an application that does some data manipulation of some tables
to format a table for a report.

1. First I open the table that gives me the parameters for some later
queries

strSQL = "SELECT DISTINCT tblMain.MeasureID, MeasureName, CountryName,
AxisID FROM tblMain;"
Set rstMeasure = CurrentDb.OpenRecordset(strSQL)
With rstMeasure

2. Now I do some number crunching

...
3. The numbers are crunched. I need to create an INSERT statement
and execute it.
' Create the Insert statement
strSQL = "INSERT INTO tblMeasureReportSummaryData ( MeasureID,
MeasureName, ProgrammeName, 2000, 2001, 2002, 2003, 2004, 2005,
CountryName, AxisID) " _
& "SELECT " & !MeasureID & " AS MeasureID, '" & !MeasureName & "' AS
MeasureName, '" & txtProgrammeName & "' AS ProgrammeName, " & dbl2000
& " as 2000, " _
& dbl2001 & " AS 2001, " & dbl2002 & " AS 2002, " & dbl2003 & " AS
2003, " & dbl2004 & " AS 2004, " & dbl2005 & " AS 2005, '" & !
CountryName & "' AS CountryName, " & !AxisID & " AS AxisID;

4.
This results in the following SQL:

INSERT INTO tblMeasureReportSummaryData ( MeasureID, MeasureName,
ProgrammeName, 2000, 2001, 2002, 2003, 2004, 2005, CountryName,
AxisID) SELECT 1 AS MeasureID, 'Productive Environment' AS
MeasureName, 'Abruzzo' AS ProgrammeName, 321428.57 as 2000, 0 AS 2001,
657427.86 AS 2002, 1397972.47 AS 2003, 3175499.2 AS 2004, 2716166.17
AS 2005, 'FR' AS CountryName, AS AxisID;

Access barfs because the value of !AxisID at Point 3 above is NULL.
Since it's NULL in the original table, I'd like it to be NULL in the
target table. But even if I put some code in such as:

& Nz(!AxisID, Null) & " AS AxisID;

it barfs with the same message.

Can I enter NULL values in inline SQL like this, or do I have to mess
about putting in token values such as -1 to stand for NULL?

All help gratefully received.

Edward
 
 
 

How can I insert NULL values in an in-line SQL statement executed in VBA?

Post by Matthias K » Sat, 12 May 2007 03:15:29


[...]


Edward

Try

... CountryName, " & IIf(IsNull(AxisID), "Null", AxisID) & " AS...

In place of the value just insert the text Null. Note that Null is not
quoted, even if the column has a string type,
i.e., do not use "'Null'"

HTH
Matthias Kl
--
www.kcc.ch

 
 
 

How can I insert NULL values in an in-line SQL statement executed in VBA?

Post by teddysnip » Sat, 12 May 2007 17:05:56


Excellent Matthias - that worked a treat. Many thanks

Edward