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

What I have always done is add a space or a 0 to the empty feilds. Then when it goes to read it, if it finds a space or 0 ( in some cases you don't want to use 0) then it returns an empty field on your form. I'm no Database expert, but that is what I have done as a quick fix.

Knight Vision

[coox]

I've been having this problem, and someone suggested setting the default values of all your fields to "", which I did (using Access), but I haven't tested the result yet. Let me know if it works har har

So why Knight Vision - is that all you can see, little knights everywhere? (don't ask me why coox)

[ttingen]

I ran into the same problem.

What you can do is test to see if the field value is null before loading it into a text box.

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

This will test if the value is null and if it is it will return an empty string, if not it will return the value in the db table.

Good Luck.

[Edited by ttingen on 08-02-2000 at 10:53 AM]

The problem with a Database is that it has to have something in the fields. EVEN if its just a space. At least that is what I was tought. So if it reads a space it will not display anything.

This works in my projects. So take it with a grain of salt..

Knight Vision

[ttingen]

Knight Vision,

The fields can be null if you allow that in the table set up in Access. There is a selection in the table set up to allow null or zero length values.

I ran into this problem with a db app that I did. I would run the query to load the text boxes and would get the error "invalid use of null". The app worked fine after using the code that I posted my previous response.

[kl899]

just make the fields blank, as in "". remember, "" is NOT NULL!!!!! NULL is special!! NULL means that the memory for a variable has not bee allocated!

you could code:

with rs
Text1.Text = .Fields!DATA_FIELD & ""
end with

This code would eliminate the "Invalid use of null" error
even if the field in the database is null.

[Mark Sreeves]

A question IDENTICLE to this was posted yesterday attracting the same set of suggestions. I posted a reply very similar to sarun's. Which is definately the best way to do it.

Am I the only person who ever searches the forums before posting a question?

[ttingen]

I now agree with Mark. That does appear to be the best way to do it. Using the IIf function to test the field for it's value might take a little longer than what sarun posted.

[JHausmann]

If the storage of nulls is important, an effort should be made to examine the content of the field before changing it to a non-null value.

For example,

with rs
.Fields!DATA_FIELD = Text1.Text
end with

would change the stored value to a non-null value, which might not be desirable. A more appropriate method might be:

with rs
if len(Text1.Text)> 0 then .Fields!DATA_FIELD = Text1.Text
end with

This would only change the stored value if there's actual content in the field...

[HeSaidJoe]

same old same old...found this on a Q & A years ago
looks like a similar version of how to get aroung things

'Convert NULL Values to Empty Strings to Avoid Errors

'One way to avoid errors from occurring when retrieving NULL values 
from a recordset object is to inspect the field's value. 
'If it is NULL, then convert it to an empty string or zero. For example:

If isnull(rs("Field")) then tmp="" else tmp=rs("Field") 
form.textfield=tmp 

'An even simpler way is to use the format function, which will convert a NULL 
value to an empty string automatically, avoiding any error messages. 

'It will look like this:

form.textfield=format(rs("Field"))

[nutstretch]

Many thanks people I used

frmKennel.txtHealProb = "" & rs1("health_prob")


this made the function work . I appreciate you suggestions

nuts