Need help with "dLookup" function.

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

Guest

Background:
I am trying to look up a value in a table based on the criteria. The
criteria is static and will not change, so I would like to type it in as a
literial string.

I have tried:
=dlookup("[UnitCost]","[tblInvMasterWarehouse]","[StockCode] =06-46-0-00-10"
resulting in nothing but an error.

I have also tried:
=DLookUp("[UnitCost]","[tblInvMasterWarehouse]","[StockCode]=" &
[Forms]![frmDailyProduction]![txtFreshExtendStockCode]). This returned
nothing (not even an error) but when a stockcode is entered in the control
that is not in the table it will return an error.

Where:
[UnitCost] is the field to be returned.
[tblInvMasterWarehouse] is the Table containing both the field to be
returned and the field the criteria is based on.
[StockCode] is the field the criteria is based on
[txtFreshExtendStockCode] is the control on the form holding the criteria.
As I mentioned above this value will be a constant and I would like to enter
it as a literal.

If there is anyone out there that can help me with this I would greatly
appreciate it. I believe the problem most be in the criteria argument as
without this argument it will return the "UnitCost" for the first stock code
in the table.

Thanks,
FatMan
 
Since the value is a string, it needs to be delimited. Try placing single
quotes around the value itself. If that doesn't work, you can put in double
quotes by using 2 of them.

=dlookup("[UnitCost]","[tblInvMasterWarehouse]","[StockCode]
='06-46-0-00-10'")
or
=dlookup("[UnitCost]","[tblInvMasterWarehouse]","[StockCode]
=""06-46-0-00-10""")
 
Wayne:
The single quote suggestion worked perfectly. Thank you very much for you
help.

Thanks,
FatMan

Wayne Morgan said:
Since the value is a string, it needs to be delimited. Try placing single
quotes around the value itself. If that doesn't work, you can put in double
quotes by using 2 of them.

=dlookup("[UnitCost]","[tblInvMasterWarehouse]","[StockCode]
='06-46-0-00-10'")
or
=dlookup("[UnitCost]","[tblInvMasterWarehouse]","[StockCode]
=""06-46-0-00-10""")

--
Wayne Morgan
MS Access MVP


FatMan said:
Background:
I am trying to look up a value in a table based on the criteria. The
criteria is static and will not change, so I would like to type it in as a
literial string.

I have tried:
=dlookup("[UnitCost]","[tblInvMasterWarehouse]","[StockCode]
=06-46-0-00-10"
resulting in nothing but an error.

I have also tried:
=DLookUp("[UnitCost]","[tblInvMasterWarehouse]","[StockCode]=" &
[Forms]![frmDailyProduction]![txtFreshExtendStockCode]). This returned
nothing (not even an error) but when a stockcode is entered in the control
that is not in the table it will return an error.

Where:
[UnitCost] is the field to be returned.
[tblInvMasterWarehouse] is the Table containing both the field to be
returned and the field the criteria is based on.
[StockCode] is the field the criteria is based on
[txtFreshExtendStockCode] is the control on the form holding the criteria.
As I mentioned above this value will be a constant and I would like to
enter
it as a literal.

If there is anyone out there that can help me with this I would greatly
appreciate it. I believe the problem most be in the criteria argument as
without this argument it will return the "UnitCost" for the first stock
code
in the table.

Thanks,
FatMan
Click to expand...
Click to expand...
 
Back
Top