Solved

DLookup Quesion

Posted on 2011-03-16
18
345 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 

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 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

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

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

Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Familiarize people with the process of utilizing SQL Server functions 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 Microsoft Ac…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

756 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