DLookUp with Nz Function

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I'm had a DLookUp working on a report, but also need it to return a value of
zero if there is no info to return. Here is the code that worked before I
put the Nz( ) in.
[InvQty] is a number. Everything else is text. Pls help....

=Nz(DLookUp("[InvQty]","[PurchA]","[InventoryPN]= [PN] And
[InventoryLocation]= 'F Stor' "))
 
The NZ function will return any value that you want incase of a null, but you
have to specify what value you want it to return

That what you have
=Nz(DLookUp("[InvQty]","[PurchA]","[InventoryPN]= [PN] And
[InventoryLocation]= 'F Stor'"))

To return 0, change it to
=Nz(DLookUp("[InvQty]","[PurchA]","[InventoryPN]= [PN] And
[InventoryLocation]= 'F Stor'"),0)

To return a string saying no value
=Nz(DLookUp("[InvQty]","[PurchA]","[InventoryPN]= [PN] And
[InventoryLocation]= 'F Stor'"),"No Value")
 
Thank you, Thank you, Thank you!

Ofer said:
The NZ function will return any value that you want incase of a null, but you
have to specify what value you want it to return

That what you have
=Nz(DLookUp("[InvQty]","[PurchA]","[InventoryPN]= [PN] And
[InventoryLocation]= 'F Stor'"))

To return 0, change it to
=Nz(DLookUp("[InvQty]","[PurchA]","[InventoryPN]= [PN] And
[InventoryLocation]= 'F Stor'"),0)

To return a string saying no value
=Nz(DLookUp("[InvQty]","[PurchA]","[InventoryPN]= [PN] And
[InventoryLocation]= 'F Stor'"),"No Value")
--
I hope that helped
Good luck


neenmarie said:
I'm had a DLookUp working on a report, but also need it to return a value of
zero if there is no info to return. Here is the code that worked before I
put the Nz( ) in.
[InvQty] is a number. Everything else is text. Pls help....

=Nz(DLookUp("[InvQty]","[PurchA]","[InventoryPN]= [PN] And
[InventoryLocation]= 'F Stor' "))
Click to expand...
Click to expand...
 
Your welcome, Your welcome, Your welcome
--
I hope that helped
Good luck


neenmarie said:
Thank you, Thank you, Thank you!

Ofer said:
The NZ function will return any value that you want incase of a null, but you
have to specify what value you want it to return

That what you have
=Nz(DLookUp("[InvQty]","[PurchA]","[InventoryPN]= [PN] And
[InventoryLocation]= 'F Stor'"))

To return 0, change it to
=Nz(DLookUp("[InvQty]","[PurchA]","[InventoryPN]= [PN] And
[InventoryLocation]= 'F Stor'"),0)

To return a string saying no value
=Nz(DLookUp("[InvQty]","[PurchA]","[InventoryPN]= [PN] And
[InventoryLocation]= 'F Stor'"),"No Value")
--
I hope that helped
Good luck


neenmarie said:
I'm had a DLookUp working on a report, but also need it to return a value of
zero if there is no info to return. Here is the code that worked before I
put the Nz( ) in.
[InvQty] is a number. Everything else is text. Pls help....

=Nz(DLookUp("[InvQty]","[PurchA]","[InventoryPN]= [PN] And
[InventoryLocation]= 'F Stor' "))
Click to expand...
Click to expand...
Click to expand...
 
Back
Top