invalid use of null

[nutstretch]

I have a program where data can be entered to a database and it is not neccessary for all text boxes to contain data. On the database these are set to not required and allow zero length. when I call this data to put onto a form in text boxes, the fields that have no data in caused an invalid use of null message. how can i get round this or do i have to enter data even though data is not always required. Thanks in anticipation

nuts

[DrewDog_21]

How about posting yoru code? That doesn't sound right and I have never had that problem with text boxes. Are you sure the problem is coming from a text box and not some other control on your form?

[ttingen]

The problem is Access doesn't like null values. It's kind of funny because I built an app and while testing it entered several records from the GUI and some of the fields were empty. Saving and retrieving these records didn't generate the error message. But I had to move hundreds of records that were in an Excel spreadsheet to the db. I made a procedure that looped through the spreadsheet dumping the data into the db. When I ran the app and tried to load one of the records that came from the spreadsheet it threw the error message.

The error was on the line:

txtReferral = .Fields!REFERRAL

It came up as "Invalid use of Null". It took me a while to find a work around because I couldn't understand why the new records threw the error with their null fields but the records I had originally placed in the db from the GUI wouldn't throw the error when selecting them even though some of their fields had null values.

I came up with a workaround, check the value of the field to see if it is null prior to loading it in the textbox by using the IIf function:


Text1.Text = IIf(IsNull(.Fields!DATA_FIELD), " ", .Fields!DATA_FIELD)


When I ran the app with this code it worked fine when selecting the records that were added from the spreadsheet.

Sorry I wrote a book. It confused me when I ran into it.

PS - I know I posted an abbreviated answer to your same question in General Questions section but I'm trying to get as many posts as I can to escape the label, "New Member". I wanna be a Guru!

[nutstretch]

Thanks folks. I got round the problem by putting frmKennel.txtHelProbs = "" & rs1("helprob")

This worked fine.

nuts

a happier bunny now