addnew results in "invalid use of null" message

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

jsccorps - 28 Aug 2005 02:13 GMT

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?

Reply to this Message

Allen Browne - 28 Aug 2005 05:03 GMT

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

Signature

Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

> 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?

Reply to this Message

jsccorps - 28 Aug 2005 23:28 GMT

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

> What line of code generates this error?
>
[quoted text clipped - 15 lines]
> > Null
> > into the blank text field and bombs out. what's going on?

Reply to this Message

David C. Holley - 29 Aug 2005 01:05 GMT

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

> Field is set to Allow Zero Length: yes. Code follows:
>
[quoted text clipped - 31 lines]
>>>Null
>>>into the blank text field and bombs out. what's going on?

Reply to this Message

Douglas J. Steele - 29 Aug 2005 01:23 GMT

Try

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

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

> Field is set to Allow Zero Length: yes. Code follows:
>
[quoted text clipped - 34 lines]
>> > Null
>> > into the blank text field and bombs out. what's going on?

Reply to this Message

jsccorps - 29 Aug 2005 04:33 GMT

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.

> Try
>
[quoted text clipped - 38 lines]
> >> > Null
> >> > into the blank text field and bombs out. what's going on?

Reply to this Message

Douglas J Steele - 29 Aug 2005 13:52 GMT

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

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

> The problem is occuring at the line:
>
[quoted text clipped - 51 lines]
> > >> > Null
> > >> > into the blank text field and bombs out. what's going on?

Reply to this Message

David C. Holley - 29 Aug 2005 14:09 GMT

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?

> I would have expected it to fail on numeric fields as well.
>
[quoted text clipped - 15 lines]
> RsFinal(fld.Name) = Nz(RsTemp(fld.Name), 0)
> End Select

Reply to this Message

jsccorps - 30 Aug 2005 02:31 GMT

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

> I would have expected it to fail on numeric fields as well.
>
[quoted text clipped - 76 lines]
> > > >> > Null
> > > >> > into the blank text field and bombs out. what's going on?

Reply to this Message

David C. Holley - 30 Aug 2005 04:20 GMT

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

> DC Holley: There may be hundreds of records that I will be appending.
>
[quoted text clipped - 107 lines]
>>>>>>>Null
>>>>>>>into the blank text field and bombs out. what's going on?

Reply to this Message

jsccorps - 02 Sep 2005 21:14 GMT

Thanks everyone. The following code (for date) works:

If fld.Type = dbDate Then
If IsNull(rsTemp(fld.Name)) = True Then
rsFinal(fld.Name) = Null
Else
rsFinal(fld.Name) = rsTemp(fld.Name)
End If
End If

> 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
[quoted text clipped - 118 lines]
> >>>>>>>Null
> >>>>>>>into the blank text field and bombs out. what's going on?

Reply to this Message