what's the difference between IsNull(value) and value = "" 2

[gerard1979]

Maybe this question is trivial for other users but I've never think about it,
Now I have to check many field if they are empty and I hope I have not to check double for each field
I now think: IF not (Isnull(value) and value = "&quot then

anyone have a idea of this?
Gerard

 

[DarenNM]

Not as obvious as it seems this one! IsNull will tell you if there is no data in a field. Although the empty string "" does not contain any text it is still considered to be data - it isn't null. Similarly a variant data type which is empty isn't the same null either.

Think of it as being like a bottle. If there is nothing in the bottle then it is empty (or blank), but it is not null (because there is a bottle there). If there was no bottle then it would be null.

This is one of these topics that gets more confusing the more you explain it. I hope this helps!



Daren

 

[gerard1979]

OK,
so always check double ;-)

Greetz and thanx
Gerard

 

[p27br]

I thought that isnull() was for fieldtypes other than text and "" was for empty text fieldtypes

 

[Junior1544]

one thing to note, this is what i do... on the table level, there is an option to allow zero lenth strings, i have that set to no... so if there is a zero lenth string (a "&quot it then becomes null... (i think)

just another idea for you...

--James
junior1544@jmjpc.net
Life is change. To deny change is to deny life.

 

[FoxProProgrammer]

Gerard,

If you are using Access 2000 or above, you might also look at the Nz function. Here's an example:

Dim varName As Variant

Nz(varName) returns a zero length value if varName is null. This function is useful when you want to convert a null value to an empty string or 0 numeric value. It seems like this would be useful in your application so you don't have to check for null values and empty strings.

Best regards, dz
dzaccess@yahoo.com

 

[DarenNM]

IsNull can check any field type to see if it has data in. You can use it to check the contents of a textbox regardless of the type of data it contains e.g.

If IsNull(Text1.Value) Then
  'No data
Else
  'Some data
End If

Null can be used to check if a variable contains data.

You have to be careful how you tie-up the "Allow Zero Length" with the "Required" property. Well worth checking the help for this.

Nz can be useful too

Must think of a witty signature

 

[PassingBy]

I have found this little Function to be very helpful in A97 to convert Null values to numeric

With any field on a form/in query/report just

Checknull([fieldname])

HTH in some way

Function CheckNull(p_no As Variant) As Double

Dim a_no As Double

If IsNull(p_no) Then
a_no = 0
Else
a_no = p_no
End If
CheckNull = a_no

End Function

 

[RJFost]

Speaking as a part-time college IT instructor, NULLs serve a purpose, albeit a rather esoteric one. NULL, in hardware terms, is usually the largest negative value your machine can handle (-FFFFFFF or the like). In programming terms, it's a headache- it's not equal to ANYthing, including itself.

I allow zero-length strings in Access text fields for a simple reason: there's a discernable difference between a NULL & a zero-string. When a new record is added, the text fields will store "" (Ø-length string); if I use a text field on a form & the user highlights the value & deletes it, now it's NULL. So, if I do a case like:

Select Case True
Case tbxText = "" 'zero-length
'do something
Case IsNull(tbxText) 'NULL
'do something else
Case Len(tbxText) 'entry in field
'do yet another thing
Case Else
End Select

For the record, the Len(tbxText) operation is a handy way to determine whether or not another control (like an OK button) is enabled or not. Say you've got 3 textboxes, each of which must have something in it for the cmdOK button to be enabled; you can put the following in the Change [or KeyPress] event for each textbox:

cmdOK.Enabled = Len(tbx1) * Len(tbx2) * Len(tbx3)

Any textbox with no data will return a length of Ø (zeroing out the right-side result), which VB will treat as False in a Boolean assignment.

 

[cheerio]

Strictly NULL in SQL terms means unknown. So if you have a list of people with birthdays including some NULLs then we do not know those birthdays.

"" is a zero length string. So if our list has provision for a middle name NULL would mean that we do not know the middle name and "" would mean that the person does not have a middle name.

Ken