Solved

DLookup Quesion

Posted on 2011-03-16
18
342 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
 

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 119

Expert Comment

by:Rey Obrero
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
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.

 
LVL 119

Expert Comment

by:Rey Obrero
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 119

Expert Comment

by:Rey Obrero
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 119

Expert Comment

by:Rey Obrero
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 119

Assisted Solution

by:Rey Obrero
Rey Obrero 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 119

Expert Comment

by:Rey Obrero
ID: 35152509
steveL13,

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

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

Suggested Solutions

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

861 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

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now