If Isnull() function

[L'apprentis]

Hello,
I have a text box bound to a Dlookup function which works pretty well. If the Dlookup return no value or 'null value' no values are displayed in the text box.
I was trying to get the text box to show 0 if the dlookup function returned no value but I don't know if i am using the right function or the apropriate method:

I have added a code on the load event of my form:

If isnull([dlookup1]) then
   [Dlookup1]=0
   End if

I don't know if what i am trying to do makes any sense but if anybody feels like putting me back on the right path i would be gratefull.

 

[jamesWP]

I think this will work,

If isnull(me.dlookup1.value) then me.dlookup.value = "0"

 

[jamesWP]

Also it may be better to place the code inside the OnCurrent event or the OnDirty event depending on how you're going to use it.

(OnLoad will only affect the first record in your recordset)

 

[modest]

I think this would work even better, it tests for null and empty strings:

If Me.DLookup1 & "" = "" Then
    Me.DLookup1 = 0
End if

 

[jamesWP]

Ignore me if I've become confused (been trying to read as much access stuff as possible in the past few days, so then again, you probably are right what with me having less than a week's experience ) but I swear I read somewhere that in access a null field couldn't be compared to "" but only =null or isnull(), please correct me if i'm wrong, I probably look stupid now .

 

[L'apprentis]

Cheers guys for your advice,
I have done what you have said but I am getting the following error now:

Run time error '2448'
You can't assign a value to this object

 

[jamesWP]

Try changing the textbox's name to something else which isn't being used by any tables/queries etc. such as TextBox12, then change the code accordingly

If Me.textbox12 & "" = "" Then
    Me.textbox12 = "0"
End if

 

[Bat17]

You cannot set a value to a control that has a Function in it.
You could move you lookup in to a function in a module and handle the null issue there, and return whatever is appropriate to the form.

If Me.DLookup1 & "" = "" Then

using & to join a null to an empty string returns an empty string, and joining an empty string to an empty string still leaves an empty string

(Null + "" would still return Null though)

HTH

Peter

 

[Mile-O]

Use the Nz() function to surround your DLookup domain aggregate function.