dblookup question

[Jeremy Dove]

What I am trying to do is this.

I have a group of information being pulled in regards to
product that will be sold. This particular form is to
make the enduser able to edit pricing and other
information. The below code will pull the unit cost from
the supplier pricing table which is fine. The problem
though is that the code doesn't make sure that it gets
the right vendor or supplier. I am trying to add another
crtieria but I can't get it to work. I have included what
I have first. Then I will include the code I am trying to
get to work. Please help if you can.

Thanks

=DLookUp("UNIT_COST","SUPPLIER_PRICING"," [MASTER_ITEM_ID]
=[MASTER_ID]")

New Code:
=DLookUp("UNIT_COST","SUPPLIER_PRICING",("
[MASTER_ITEM_ID]=[MASTER_ID]"AND"[SUPPLIER_PRICING]!
[SUPPLIER_ID]"))

 

[Marshall Barton]

What I am trying to do is this.

I have a group of information being pulled in regards to
product that will be sold. This particular form is to
make the enduser able to edit pricing and other
information. The below code will pull the unit cost from
the supplier pricing table which is fine. The problem
though is that the code doesn't make sure that it gets
the right vendor or supplier. I am trying to add another
crtieria but I can't get it to work. I have included what
I have first. Then I will include the code I am trying to
get to work. Please help if you can.

Thanks

=DLookUp("UNIT_COST","SUPPLIER_PRICING"," [MASTER_ITEM_ID]
=[MASTER_ID]")

New Code:
=DLookUp("UNIT_COST","SUPPLIER_PRICING",("
[MASTER_ITEM_ID]=[MASTER_ID]"AND"[SUPPLIER_PRICING]!
[SUPPLIER_ID]"))

Not sure where those values are coming from, but I think it
should be more like this:

=DLookUp("UNIT_COST", "SUPPLIER_PRICING", "[MASTER_ITEM_ID]
= " & [MASTER_ID] & " AND [SUPPLIER_ID] = " & [SupplierID] )

 

[Jeremy Dove]

Sorry about that here is the form source query.

The code I previously gave is a dblookup for a textbox
that calls from the supplier_pricing table.

SELECT BUDGET_TAKEOFFS.HOMETYPEID,
BUDGET_TAKEOFFS.ELEVATION, BUDGET_TAKEOFFS.SITING,
BUDGET_TAKEOFFS.NET_FROM_OPTIONID,
BUDGET_TAKEOFFS.ENTITYID, BUDGET_TAKEOFFS.ID,
BUDGET_TAKEOFFS.COST_CODE, BUDGET_TAKEOFFS.PACKAGE,
BUDGET_TAKEOFFS.SUBPACKAGE, BUDGET_TAKEOFFS.QUANTITY,
BUDGET_TAKEOFFS.MASTER_ID, MASTER_ITEM_CATALOG.ITEM_NAME,
BUDGET_TAKEOFFS.UOM, BUDGET_TAKEOFFS.ID1,
BUDGET_TAKEOFFS.MAJOR_VERSION, BUDGET_TAKEOFFS.REVISION,
BUDGET_TAKEOFFS.LASTUPDATEDBY, BUDGET_TAKEOFFS.VENDOR_ID,
BUDGET_TAKEOFFS.LASTUPDATEDON, [MASTER_ITEM_CATALOG]!
[ITEM_NAME] AS I_NAME FROM MASTER_ITEM_CATALOG INNER JOIN
BUDGET_TAKEOFFS ON MASTER_ITEM_CATALOG.MASTER_ITEM_ID =
BUDGET_TAKEOFFS.MASTER_ID WHERE (((BUDGET_TAKEOFFS.STATUS)
="CURRENT"));

-----Original Message-----

What I am trying to do is this.

I have a group of information being pulled in regards to
product that will be sold. This particular form is to
make the enduser able to edit pricing and other
information. The below code will pull the unit cost from
the supplier pricing table which is fine. The problem
though is that the code doesn't make sure that it gets
the right vendor or supplier. I am trying to add another
crtieria but I can't get it to work. I have included what
I have first. Then I will include the code I am trying to
get to work. Please help if you can.

Thanks

=DLookUp("UNIT_COST","SUPPLIER_PRICING"," [MASTER_ITEM_ID]
=[MASTER_ID]")

New Code:
=DLookUp("UNIT_COST","SUPPLIER_PRICING",("
[MASTER_ITEM_ID]=[MASTER_ID]"AND"[SUPPLIER_PRICING]!
[SUPPLIER_ID]"))

Not sure where those values are coming from, but I think it
should be more like this:

=DLookUp

("UNIT_COST", "SUPPLIER_PRICING", "[MASTER_ITEM_ID]

= " & [MASTER_ID] & " AND [SUPPLIER_ID] = " & [SupplierID] )

 

[Marshall Barton]

That explains where [MASTER_ITEM_ID] comes from, but I still
don't know if [SupplierID] is a text box on the form or
what?

Did you try what I suggested? If so, what happened?
--
Marsh
MVP [MS Access]

Sorry about that here is the form source query.

The code I previously gave is a dblookup for a textbox
that calls from the supplier_pricing table.

SELECT BUDGET_TAKEOFFS.HOMETYPEID,
BUDGET_TAKEOFFS.ELEVATION, BUDGET_TAKEOFFS.SITING,
BUDGET_TAKEOFFS.NET_FROM_OPTIONID,
BUDGET_TAKEOFFS.ENTITYID, BUDGET_TAKEOFFS.ID,
BUDGET_TAKEOFFS.COST_CODE, BUDGET_TAKEOFFS.PACKAGE,
BUDGET_TAKEOFFS.SUBPACKAGE, BUDGET_TAKEOFFS.QUANTITY,
BUDGET_TAKEOFFS.MASTER_ID, MASTER_ITEM_CATALOG.ITEM_NAME,
BUDGET_TAKEOFFS.UOM, BUDGET_TAKEOFFS.ID1,
BUDGET_TAKEOFFS.MAJOR_VERSION, BUDGET_TAKEOFFS.REVISION,
BUDGET_TAKEOFFS.LASTUPDATEDBY, BUDGET_TAKEOFFS.VENDOR_ID,
BUDGET_TAKEOFFS.LASTUPDATEDON, [MASTER_ITEM_CATALOG]!
[ITEM_NAME] AS I_NAME FROM MASTER_ITEM_CATALOG INNER JOIN
BUDGET_TAKEOFFS ON MASTER_ITEM_CATALOG.MASTER_ITEM_ID =
BUDGET_TAKEOFFS.MASTER_ID WHERE (((BUDGET_TAKEOFFS.STATUS)
="CURRENT"));

-----Original Message-----

What I am trying to do is this.

I have a group of information being pulled in regards to
product that will be sold. This particular form is to
make the enduser able to edit pricing and other
information. The below code will pull the unit cost from
the supplier pricing table which is fine. The problem
though is that the code doesn't make sure that it gets
the right vendor or supplier. I am trying to add another
crtieria but I can't get it to work. I have included what
I have first. Then I will include the code I am trying to
get to work.

=DLookUp("UNIT_COST","SUPPLIER_PRICING"," [MASTER_ITEM_ID]
=[MASTER_ID]")

New Code:
=DLookUp("UNIT_COST","SUPPLIER_PRICING",("
[MASTER_ITEM_ID]=[MASTER_ID]"AND"[SUPPLIER_PRICING]!
[SUPPLIER_ID]"))

Not sure where those values are coming from, but I think
it should be more like this:

=DLookUp("UNIT_COST", "SUPPLIER_PRICING", "[MASTER_ITEM_ID]= " & [MASTER_ID] & " AND [SUPPLIER_ID]= " & [SupplierID] )