addnew results in "invalid use of null" message

[Guest]

I'm using addnew to append records to a table. It steps across the fields
and works ok until it gets to a text field that is blank, then it gives the
message "invalid use of null" and quits. I guess it is tying to to put Null
into the blank text field and bombs out. what's going on?

 

[Allen Browne]

What line of code generates this error?

Perhaps you are trying to assign a zero-length string (ZLS) to a field that
has its Allow Zero Length property set to No (as it should).

Or perhaps you are trying to assign a null to a variable? Only the Variant
type can accept a null.

For further suggestions, see:
Common errors with Null
at:
http://allenbrowne.com/casu-12.html

 

[Guest]

Field is set to Allow Zero Length: yes. Code follows:

RsTemp.MoveFirst
Do Until RsTemp.EOF
RsFinal.AddNew
For Each fld In RsFinal.Fields
MsgBox (RsTemp(fld.Name)) ‘ at the first text field with
no data, get e error message
RsFinal(fld.Name) = RsTemp(fld.Name)
Next fld
RsFinal.Update
RsTemp.MoveNext
Loop
RsFinal.Close
RsTemp.Close

 

[David C. Holley]

What is the SPECIFIC line of code that is crapping out?

 

[Douglas J. Steele]

Try

RsFinal(fld.Name) = RsTemp(fld.Name) & ""

 

[Guest]

The problem is occuring at the line:

RsFinal(fld.Name) = RsTemp(fld.Name)

The record has the following field types: numbers, text, and dates.

I tried

RsFinal(fld.Name) = nz(RsTemp(fld.Name),"")

It works fine for numbers and text. But I get a "conversion error" when it
gets to a blank Date field.

 

[Douglas J Steele]

I would have expected it to fail on numeric fields as well.

You'll have to check the field type, then, before assigning the value:

If rsFinal.Fields(fld.Name).Type = dbDate Then
RsFinal(fld.Name) = Nz(RsTemp(fld.Name), 0)
Else

RsFinal(fld.Name) = Nz(RsTemp(fld.Name), "")
End If

although, more correctly, it should probably be:

Select Case rsFinal.Fields(fld.Name).Type
Case dbText, dbMemo
RsFinal(fld.Name) = Nz(RsTemp(fld.Name), "")
Case Else
RsFinal(fld.Name) = Nz(RsTemp(fld.Name), 0)
End Select

 

[David C. Holley]

How many records do you have in the RsTemp table/query? If you have more
than 1 record, on which record does the code crap out?

 

[Guest]

DC Holley: There may be hundreds of records that I will be appending.

DJ Steele et al: the following works (not elegant - but it works). Also,
setting the date to zero gives me 12/30/1899. How can I make the date blank?

Do Until RsTemp.EOF
RsFinal.AddNew
For Each fld In RsFinal.Fields
If fld.Type = dbText Or fld.Type = dbMemo Then
RsFinal(fld.Name) = Nz(RsTemp(fld.Name), "")
ElseIf fld.Type = dbDate Then
RsFinal(fld.Name) = Nz(RsTemp(fld.Name), 0) ' when blank,
sets date to 12/30/1899
Else
RsFinal(fld.Name) = Nz(RsTemp(fld.Name))
End If
Next fld
RsFinal.Update
RsTemp.MoveNext
Loop

 

[David C. Holley]

Insert an If...then to check if the code is on the field for the Date
within the If...then test for 0 and override it to Null as in

If fld.Name = "date" then
If rsTemp(fld.name) = 0 then
rsFinal(fld.Name) = Null
else
rsFinal(fld.name) = rsTemp(fld.Name)
end if
end if