Problem with my Dlookup

  • Thread starter Thread starter ryguy7272
  • Start date Start date
R

ryguy7272

I’m trying to get a simple Dlookup working and I seem to be struggling with
it. I have values that I pass to a query, using VBA, and populate TextBoxes
on a report. One TextBox is named ‘CUST’ for Customer. When CUST is
populated, I’d like to see a rate (this is fixed for each customer) in my
report. This is what I have now:
=DLookUp("Rate","Customers","Customer = " & [CUST])

Rate is the Rate that I want to lookup.

Customers is the name of the table.

CUST is the TextBox on the report.

What am I doing wrong?
Thanks!
Ryan--
 
ryguy7272,
Please describe the pertinent fields in your customer table.
Also discuss where Rate is kept.
If each customer has an associated Rate, it should be stored in the
Customer table, or related to that Cust via an external Rate table.
Therefore, you should be able to associate each Cust with their Rate in
the query behind the report.
No need for a Dlookup

Setting that aside for now... if [Cust] is a text value...
=DLookUp("[Rate]","Customers","[Customer] = ' " & [CUST] & " ' " )
I added spaces between the quotes... remove them in real code.

That might solve your immediate question, but the real solution would
be to re-evaluate your design... if you find yourself needing to do a
DLookup for data
that should be readily available.

--
hth
Al Campagna
Microsoft Access MVP 2007-2009
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."
 
Ken's solution worked perfect! I was so thrilled when I saw it come to life,
I immediately thought of another use for another Dlookup, and with a slight
modification, the second one worked equally as well.

Thanks everyone!
Ryan---

--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


Al Campagna said:
ryguy7272,
Please describe the pertinent fields in your customer table.
Also discuss where Rate is kept.
If each customer has an associated Rate, it should be stored in the
Customer table, or related to that Cust via an external Rate table.
Therefore, you should be able to associate each Cust with their Rate in
the query behind the report.
No need for a Dlookup

Setting that aside for now... if [Cust] is a text value...
=DLookUp("[Rate]","Customers","[Customer] = ' " & [CUST] & " ' " )
I added spaces between the quotes... remove them in real code.

That might solve your immediate question, but the real solution would
be to re-evaluate your design... if you find yourself needing to do a
DLookup for data
that should be readily available.

--
hth
Al Campagna
Microsoft Access MVP 2007-2009
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."


ryguy7272 said:
I'm trying to get a simple Dlookup working and I seem to be struggling
with
it. I have values that I pass to a query, using VBA, and populate
TextBoxes
on a report. One TextBox is named 'CUST' for Customer. When CUST is
populated, I'd like to see a rate (this is fixed for each customer) in my
report. This is what I have now:
=DLookUp("Rate","Customers","Customer = " & [CUST])

Rate is the Rate that I want to lookup.

Customers is the name of the table.

CUST is the TextBox on the report.

What am I doing wrong?
Thanks!
Ryan--
Click to expand...


.
Click to expand...
 
ryguy7272,
While Ken and my solution work... and solve your immediate problem, I
still question a design where it's necessary to determine a customer's Rate
with a DLookup.
The Dlookup can often turn into a work-around for improper design, and
on large databases, multiple users doing myriad Dlookups can really drag a
system down.
Please consider re-analyzing your customer/rate table design. I'm sure
you can get helpful feedback here on that...
Anywho... good luck...
--
hth
Al Campagna
Microsoft Access MVP 2007-2009
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."



ryguy7272 said:
Ken's solution worked perfect! I was so thrilled when I saw it come to
life,
I immediately thought of another use for another Dlookup, and with a
slight
modification, the second one worked equally as well.

Thanks everyone!
Ryan---

--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


Al Campagna said:
ryguy7272,
Please describe the pertinent fields in your customer table.
Also discuss where Rate is kept.
If each customer has an associated Rate, it should be stored in the
Customer table, or related to that Cust via an external Rate table.
Therefore, you should be able to associate each Cust with their Rate
in
the query behind the report.
No need for a Dlookup

Setting that aside for now... if [Cust] is a text value...
=DLookUp("[Rate]","Customers","[Customer] = ' " & [CUST] & " '
" )
I added spaces between the quotes... remove them in real code.

That might solve your immediate question, but the real solution would
be to re-evaluate your design... if you find yourself needing to do a
DLookup for data
that should be readily available.

--
hth
Al Campagna
Microsoft Access MVP 2007-2009
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your
life."


ryguy7272 said:
I'm trying to get a simple Dlookup working and I seem to be struggling
with
it. I have values that I pass to a query, using VBA, and populate
TextBoxes
on a report. One TextBox is named 'CUST' for Customer. When CUST is
populated, I'd like to see a rate (this is fixed for each customer) in
my
report. This is what I have now:
=DLookUp("Rate","Customers","Customer = " & [CUST])

Rate is the Rate that I want to lookup.

Customers is the name of the table.

CUST is the TextBox on the report.

What am I doing wrong?
Thanks!
Ryan--
Click to expand...


.
Click to expand...
Click to expand...
 
ryguy7272 said:
I’m trying to get a simple Dlookup working and I seem to be struggling
with
it. I have values that I pass to a query, using VBA, and populate
TextBoxes
on a report. One TextBox is named ‘CUST’ for Customer. When CUST is
populated, I’d like to see a rate (this is fixed for each customer) in my
report. This is what I have now:
=DLookUp("Rate","Customers","Customer = " & [CUST])

Rate is the Rate that I want to lookup.

Customers is the name of the table.

CUST is the TextBox on the report.

What am I doing wrong?
Thanks!
Ryan--
Click to expand...
 
Back
Top