Solved

DLookup Quesion

Posted on 2011-03-16
18
343 Views
Last Modified: 2013-11-05
What is wrong with this code?  

Me.txtDailyCapLimit = DLookup("[Qty]", "[tblCapacityCategory]", "[CapCat] ='" & txtCapCat & "'")

(CapCat is a text field)

--Steve
0
Comment
Question by:SteveL13
  • 6
  • 6
  • 4
  • +1
18 Comments
 
LVL 75
ID: 35151521
try this:

Me.txtDailyCapLimit = DLookup("[Qty]", "[tblCapacityCategory]", "[CapCat] = " & Chr(34)  & ME.txtCapCat & Chr(34) )
0
 
LVL 5

Expert Comment

by:DoveTails
ID: 35151559
May be an issue with quotes and double quotes. Try this acii character....Chr(34)...ie:

Me.txtDailyCapLimit = DLookup("[Qty]", "[tblCapacityCategory]", "[CapCat] = " & Chr(34) & txtCapCat & Chr(34))
0
 
LVL 5

Expert Comment

by:DoveTails
ID: 35151567
sorry DatabaseMX ...   you're too quick on the draw for me
0
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 

Author Comment

by:SteveL13
ID: 35151608
This did not work.  Doesn't it need single quote marks in there somewhere?

Me.txtDailyCapLimit = DLookup("[Qty]", "[tblCapacityCategory]", "[CapCat] = " & Chr(34) & Me.txtCapCat & Chr(34))
0
 
LVL 75
ID: 35151632
The syntax is correct ... IF ...CapCat is a Text field ... as you stated .

What error are you getting ?

mx
0
 

Author Comment

by:SteveL13
ID: 35151677
I'm not getting an error.  This is in an onopen event of a form attempting to fill a field, "txtDailyCapLimit" with a qty.  I am getting nothing in that field when I open the form.
0
 
LVL 75
ID: 35151694
Try moving the code to the Form Load event ...

mx
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 35151755
try this

Me.txtDailyCapLimit = nz(DLookup("[Qty]", "tblCapacityCategory", "[CapCat] ='" & me.txtCapCat & "'"),0)
0
 

Author Comment

by:SteveL13
ID: 35151799
To capricorn1:  That gave me a "0".  The answer should be 1,000

To DatabaseMX:  That did not work.  I still got an empty field.
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 35151837
well, the dlookup wont lie to you, why do you say that it should be 1,000?

also, there could be no data in me.txtCapCat at the moment..

try using the form's current event

private sub form_current()


Me.txtDailyCapLimit = nz(DLookup("[Qty]", "tblCapacityCategory", "[CapCat] ='" & me.txtCapCat & "'"),0)

end sub


after the form have opened, scroll to records using the record naviagation button below the form to see the data displayed
0
 
LVL 75
ID: 35151844
" I still got an empty field."
That means your data is missing, because if Nz gives you a zero, then DLookup is return a Null ....

mx
0
 

Author Comment

by:SteveL13
ID: 35152016
I have you both totally messed up.  Is my fault and I apologize.   I've been looking at this thing way too long and have myself confused.  This code worked in the onopen event of the form:

Me.txtDailyCapLimit = Nz(DLookup("[DailyCap]", "tblCapacityCategory", "[CapCat] ='" & Me.txtCapCat & "'"), 0)

But now I am trying to do this and this syntax must be wrong in the afterupdate event of a qty field:

    If Me.txtDailyCapLimit < (Me.txtQty + DSum("Qty", "tblProjectDetail", "'CapCat = me.txtcapcat'")) Then
        MsgBox "This quantity makes the total quantity for this product type over the daily capacity limit of " & [txtDailyCapLimit]
    End If

What am I doing wrong with this one?

0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 35152042
change this

If Me.txtDailyCapLimit < (Me.txtQty + DSum("Qty", "tblProjectDetail", "'CapCat = me.txtcapcat'")) Then

to


If Me.txtDailyCapLimit < (Me.txtQty + DSum("Qty", "tblProjectDetail", "CapCat ='" & me.txtcapcat & "'")) Then
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 35152052
or, better

If Me.txtDailyCapLimit < (Me.txtQty + nz(DSum("Qty", "tblProjectDetail", "CapCat ='" & me.txtcapcat & "'"),0)) Then
0
 
LVL 75

Accepted Solution

by:
DatabaseMX (Joe Anderson - Access MVP) earned 250 total points
ID: 35152069
Then it's still a syntax problem:

    If Me.txtDailyCapLimit < (Me.txtQty + Nz(DSum("Qty", "tblProjectDetail", "[CapCat] = " & Chr(34) & Me.txtcapcat & Chr(34)), 0)) Then
        MsgBox "This quantity makes the total quantity for this product type over the daily capacity limit of " & [txtDailyCapLimit]
    End If
0
 
LVL 120

Assisted Solution

by:Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1) earned 250 total points
ID: 35152089






or, better

If Me.txtDailyCapLimit < (Me.txtQty + nz(DSum("Qty", "tblProjectDetail", "CapCat ='" & me.txtcapcat & "'")),0) Then
0
 
LVL 75
ID: 35152495
SteveL13:

I don't believe the Assisted Answer code compiles as is ... did you try that ?  It does not compile on my system.

mx
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 35152509
steveL13,

you should have accepted the FIRST correct posted comment at http:#a35152052 and not the modified/copied comment
0

Featured Post

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

808 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question