By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
428,816 Members | 2,053 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 428,816 IT Pros & Developers. It's quick & easy.

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

P: n/a
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

May 10 '07 #1
Share this Question
Share on Google+
2 Replies


P: n/a
te********@hotmail.com wrote:
>I have an application that does some data manipulation of some tables
to format a table for a report.
[...]
>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:
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šy
--
www.kcc.ch
May 10 '07 #2

P: n/a
On 10 May, 19:15, Matthias Klaey <m...@hotmail.comwrote:
teddysn...@hotmail.com wrote:
I have an application that does some data manipulation of some tables
to format a table for a report.

[...]
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:

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'"
Excellent Matthias - that worked a treat. Many thanks

Edward

May 11 '07 #3

This discussion thread is closed

Replies have been disabled for this discussion.