Need help with "dLookup" function.

[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

 

[Wayne Morgan]

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""")

 

[Guest]

Wayne:
The single quote suggestion worked perfectly. Thank you very much for you
help.

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 Morgan
MS Access MVP

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