Dlookup with a Date (1 Viewer)

[Bigmo2u]

I have searched and searched because I was afraid someone would say use the search button on the forum.

I am trying to do a Dlookup on a date to see if there is one or not, it returns 12:00:00 AM when no date is present. here is my code:

adate = Nz(DLookup("[ArrivalDate]", "tblSerialNumber", "[SerialNumberID] = '" & Me.txtSerialNumberID & "'"))

adate is a variable that it is declared as a Date.

 

[John Big Booty]

I've been unable to duplicate the behaviour you describe. Are you able to post a copy of your DB?

 

[Bigmo2u]

Here it is, Be nice this is still in the build process, but any suggestion is greatlt appreciated.

The code is on the form frmTruckPickup in the afterUpdate of SerialNumber.

Sorry, I must not have enough posts, so I had to zip the file.

 

[pr2-eugin]

Nz is a function that allows you to choose a value, if in case the value is null. So based on my assumption the DLookUp is returning a NULL value i.e. there is no record that matches the criteria.. That is the reason you get the 12:00:00 AM as result..

Now you have to think what value do you wish to assign to 'adate' if there is nothing in the table tblSerialNumber that matches the condition.. Something like..

adate = Nz(DLookup("[ArrivalDate]", "tblSerialNumber", "[SerialNumberID] = '" & Me.txtSerialNumberID & "'"),#[COLOR=Blue]SomeDate[/COLOR]#)

As the general syntax of Nz is as follows..

Nz( [I][B]someFieldName , valueIf_someFieldName_IsNull [/B][/I])

EDIT : So seeing your file.. I think you have to replace all Nz(DLookUp) with some value..

 

[Bigmo2u]

Now I see how use Nz properly, thank you so much for the help!

 

[pr2-eugin]

You are welcome.. Glad to help..