How to read and process a null value in a table - handling nulls

[Karen Middleton]

Based on the code Albert (Access MVP) provided I implemented VBA code
to read a table ACTStage and insert the data from ActStage into
ActFact please do not ask me why I am doing it this way I am just
practising the code for a more complex scenario please do not drive
the problem into a different direction.

In ActStage I have a numeric field called Sales some records in
ActStage can have a null value for Sales field when I run the
following code it bombs saying invalid value use of Null or something
like it.

Please advise in the following code how can I handle a Null value in
the below scenario or generally how do we handle nulls in VBA

Thanks
Karen


Dim rstACTStage As DAO.Recordset
Dim strSql As String

Set rstACTStage = CurrentDb.OpenRecordset("select * from ACTStage")
' process untill end of file
Do While rstACTStage.EOF = False
' CurrentDb.Execute "delete from otherTable where au_id = " & _
rstAuthors!au_id
strCuststor = rstACTStage!custstor
strCustomer = rstACTStage!customer
strwhSales = rstACTStage!Sales
strFiscalYearPeriod = rstACTStage!FiscalYearPeriod
strLastFiscalYearPeriod = rstACTStage!LastFiscalYearPeriod
strsq = "Insert into ACTFact(custstor, customer, Sales, &_
Fiscalyearperiod, Lastfiscalyearperiod) values ( "
+ _
Chr$(34) + strCuststor + Chr$(34) + "," + _
Chr$(34) + strCustomer + Chr$(34) + "," + _
strwhSales + "," + _
Chr$(34) + strFiscalYearPeriod + Chr$(34) + ","
+ _
Chr$(34) + strLastFiscalYearPeriod + Chr$(34) +
")"
CurrentDb.Execute strsq
rstACTStage.MoveNext
Loop
rstACTStage.Close
Set rstACTStage = Nothing

 

[Brian]

Based on the code Albert (Access MVP) provided I implemented VBA code
to read a table ACTStage and insert the data from ActStage into
ActFact please do not ask me why I am doing it this way I am just
practising the code for a more complex scenario please do not drive
the problem into a different direction.

In ActStage I have a numeric field called Sales some records in
ActStage can have a null value for Sales field when I run the
following code it bombs saying invalid value use of Null or something
like it.

Please advise in the following code how can I handle a Null value in
the below scenario or generally how do we handle nulls in VBA

Thanks
Karen


Dim rstACTStage As DAO.Recordset
Dim strSql As String

Set rstACTStage = CurrentDb.OpenRecordset("select * from ACTStage")
' process untill end of file
Do While rstACTStage.EOF = False
' CurrentDb.Execute "delete from otherTable where au_id = " & _
rstAuthors!au_id
strCuststor = rstACTStage!custstor
strCustomer = rstACTStage!customer
strwhSales = rstACTStage!Sales
strFiscalYearPeriod = rstACTStage!FiscalYearPeriod
strLastFiscalYearPeriod = rstACTStage!LastFiscalYearPeriod
strsq = "Insert into ACTFact(custstor, customer, Sales, &_
Fiscalyearperiod, Lastfiscalyearperiod) values ( "
+ _
Chr$(34) + strCuststor + Chr$(34) + "," + _
Chr$(34) + strCustomer + Chr$(34) + "," + _
strwhSales + "," + _
Chr$(34) + strFiscalYearPeriod + Chr$(34) + ","
+ _
Chr$(34) + strLastFiscalYearPeriod + Chr$(34) +
")"
CurrentDb.Execute strsq
rstACTStage.MoveNext
Loop
rstACTStage.Close
Set rstACTStage = Nothing

Probably all you need is the Nz() function to convert the nulls into
something else. For example, to get a zero-length string, you would do
this:

strwhSales = Nz(rstACTStage!Sales,"")

 

[John Vinson]

when I run the
following code it bombs saying invalid value use of Null or something
like it.

It kind of helps if you post the actual error message and indicate the
line that triggers it.

One suggestion: dim strSales as a Variant; and use the & operator
rather than the + operator in your concatenations. The + operator
"propagates nulls" - if any one of the values you're concatenating is
NULL, then the entire string becomes NULL (and you cannot assign a
NULL to a String value). The & operator treats a NULL as a zero length
string. You may still get an error when you run the query -
NZ(strStore, "Null") might be preferable.

John W. Vinson[MVP]