?
Solved

DLookup Quesion

Posted on 2011-03-16
18
Medium Priority
?
354 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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 - Microsoft MVP, Access and Data Platform) earned 1000 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 1000 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

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

Question has a verified solution.

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

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Suggested Courses

809 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