Dlookup Problem

D

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
 
T

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.
 
T

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top