Solved

DLookup Quesion

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

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
try this:

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

Expert Comment

by:DoveTails
Comment Utility
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
Comment Utility
sorry DatabaseMX ...   you're too quick on the draw for me
0
 

Author Comment

by:SteveL13
Comment Utility
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

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
The syntax is correct ... IF ...CapCat is a Text field ... as you stated .

What error are you getting ?

mx
0
 

Author Comment

by:SteveL13
Comment Utility
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

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
Try moving the code to the Form Load event ...

mx
0
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
try this

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

Author Comment

by:SteveL13
Comment Utility
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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 119

Expert Comment

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

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
" 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility






or, better

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

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
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
Comment Utility
steveL13,

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

Featured Post

Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

Join & Write a Comment

In the article entitled Working with Objects – Part 1 (http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_4942-Working-with-Objects-Part-1.html), you learned the basics of working with objects, properties, methods, and events. In Work…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

744 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

9 Experts available now in Live!

Get 1:1 Help Now