Dlookup Problem

[Dave]

I want to be able to look up a markup rate depending on supplier and price
type, i have a table with these listed eg

SUPPLIER TYPE RATE

ROC-001 RETAIL 20%
ROC-001 TRADE 10%
ROC-001 INTERNAL 5%
HAR-001 RETAIL 25%
HAR-001 TRADE 20%

However when i use DlookUp on my form it always shows me the rate for my
first supplier, when i want it to show the rate for the supplier that i have
entered on the form.

code im using in text box is:
=DLookUp ("Rate", "tblPriceMatrix", "Type = 'Retail'")

not sure how to write code so it will return value WHERE Me.SupplerID =
SupplierID in tblPriceMatrix

Hope this makes sence
Regards David

 

[Tom van Stiphout]

On Sat, 8 Mar 2008 17:13:01 -0800, Dave

Try this:
=DLookUp ("Rate", "tblPriceMatrix", "Type = 'Retail' AND Supplier='" &
Me.SupplierID & "'")

I'm assuming SUPPLIER in your example is the same as Me.SupplierID.

-Tom.

 

[Tom Wickerath]

Hi David,

To add a little to Tom van Stiphout's answer, here is a web page that you
might want to check out for future reference:

General: DLookup Usage Samples
http://www.mvps.org/access/general/gen0018.htm


Note:
Both Type and Rate in your example are considered reserved words. You should
avoid using any reserved words or special characters when you assign names in
Access. Access MVP Allen Browne maintains an extensive listing of reserved
words, and even has a cool utility that you can download to scan your tables
for the use of reserved words.

Problem names and reserved words in Access
http://allenbrowne.com/AppIssueBadWord.html


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

:

Try this:
=DLookUp ("Rate", "tblPriceMatrix", "Type = 'Retail' AND Supplier='" &
Me.SupplierID & "'")

I'm assuming SUPPLIER in your example is the same as Me.SupplierID.

-Tom.
__________________________________________

:

I want to be able to look up a markup rate depending on supplier and price
type, i have a table with these listed eg

SUPPLIER TYPE RATE

ROC-001 RETAIL 20%
ROC-001 TRADE 10%
ROC-001 INTERNAL 5%
HAR-001 RETAIL 25%
HAR-001 TRADE 20%

However when i use DlookUp on my form it always shows me the rate for my
first supplier, when i want it to show the rate for the supplier that i have
entered on the form.

code im using in text box is:
=DLookUp ("Rate", "tblPriceMatrix", "Type = 'Retail'")

not sure how to write code so it will return value WHERE Me.SupplerID =
SupplierID in tblPriceMatrix

Hope this makes sence
Regards David