access vba - DLookup 3075 Missing Operator Around NZ? -

this code:

private sub form_current()  set rs = currentdb.openrecordset("sites", dbopendynaset, dbseechanges) rs.requery rs.movefirst    if nz(me.site_id.value) > 0       me.h2obillingidlbl.caption = dlookup("h2obillingidnum", "sites", "h2obillingidnum = " & me.txthotelid)    else       me.h2obillingidlbl.caption = ""    end if  end sub 

the dlookup line throwing error.

me.txthotelid box text entry box on form , used enter numbers only.

the h2obillingidnum field in recordset long.

i have tried putting brackets around h2obillingidnum; .value @ end of h2obillingidnum , me.txthotelid alternatively , combined; entering data string in case data mismatch error.

i don't believe can use sql query because text entry field, if i'm wrong, i'll happily take information i've never heard of sql query , it's faster , more accurate method of pulling data.

i'm out of ideas. suggestions? nz? there better way of writing that? should not included @ all? if helps, dao db.

the error must me.txthotelid empty, therefore dlookup call incomplete

dlookup("foo", "bar", "myvalue = ") 

gives runtime error 3075: syntax error (missing operator) in 'myvalue = '

here guide on how debug problems this.

take code apart (one command per line), , use intermediate variables. values can seen in break mode hovering mouse on variable name, or in watch window.

recommended reading: debugging vba code

if use dlookup, there no need @ recordset, have removed it.

run (or step through) code, , error become clear:

private sub form_current()      dim siteid long     dim hotelid long     dim strcaption string      siteid = nz(me.site_id.value, 0)     if siteid > 0         ' intentionally without nz(), throw error         hotelid = me.txthotelid         ' nz() needed here too!         strcaption = dlookup("h2obillingidnum", "sites", "h2obillingidnum = " & hotelid)     else         strcaption = ""     end if     me.h2obillingidlbl.caption = strcaption  end sub