Zero default value after Dlookup

[ria4life]

Any help is appreciated:
Id like to modify this code so if the dlookup returns no value, then i want to default the value in I1=0.

If Me.Sig1 = "" Then
Me.I1 = ""
Else
Me.I1 = DLookup("Occurance", "[Qry_Availability1]", "Date_Selected = Sig1 And [title] = '" & Text89 & "'")
End If

 

[theDBguy]

Hi,

Try wrapping it with the Nz() function. For example,

Nz(DLookup(...),0)

Hope it helps...

 

[isladogs]

Use the Nz function

Me.I1 = Nz(DLookup("Occurance", "[Qry_Availability1]", "Date_Selected = Sig1 And [title] = '" & Text89 & "'"),0)

Does the date selected part work?

 

[plog]

That's some very inconsistent coding you got going on there:

If Me.Sig1 = "" Then...
...DLookup("Occurance", "[Qry_Availability1]", "Date_Selected = Sig1 And [title] = '" & Text89 & "'")

1. You use brackets around the data source name and one field [title], but not the other 2 fields.

2. You reference Sig1 with a Me. identifier in the initial line, but in the Dlookup you just use it.

3. You use single quotes to insert the value from Text89 into your Dlookup, but not for Sig1.

4. You escape out of the string to insert the value of Text89 into your Dlookup, but not for Sig1.

Those last 2 are what is going to ensure that your Dlookup always returns 0.

Additionally:

A. Do you really want the entire thing to return an empty string if Sig1 is an empty string, but a 0 if Sig1 isn't in Qry_Availability1? Seems a 0 would be more appropriate, especially since you used the term "default".

B. Null isn't equal to "". If Sig1 is null it will hit the Dlookup. Could it be possible that Sig1 be Null? And you want that treated different than if it was an empty string?

 

[ria4life]

thanks guys:

isladogs response worked on the first go