dblookup question

  • Thread starter Thread starter Jeremy Dove
  • Start date Start date
J

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]"))
 
Jeremy said:
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]"))
Click to expand...


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] )
 
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-----
Jeremy said:
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]"))
Click to expand...


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

=DLookUp
Click to expand...
("UNIT_COST", "SUPPLIER_PRICING", "[MASTER_ITEM_ID]
= " & [MASTER_ID] & " AND [SUPPLIER_ID] = " & [SupplierID] )
Click to expand...
 
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]




Jeremy said:
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-----
Jeremy said:
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]"))
Click to expand...


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] )
Click to expand...
Click to expand...
 
Back
Top