UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Dlookup, Access 2016    
 
   
mike60smart
post Oct 7 2019, 09:21 AM
Post#1


UtterAccess VIP
Posts: 13,422
Joined: 6-June 05
From: Dunbar,Scotland


Hi Everyone


Edit Please ignore this as I modified the Lookup to this:-

=DLookUp("Amount","qryTotalExpenseVoucher","SchoolID = " & [SchoolID])

I cannot for the life of me get the syntax right in the following lookup:-

=DLookUp("Amount","qryTotalExpenseVoucher","SchoolID = " & [SchoolID] And [M]=[Forms]![frmPayrollManagement]![txtMY])

It should pull up Amount values for only 2 Schools but is showing the same value for every school ??

Any help appreciated

--------------------
Hope this helps?

Mike

Get happiness out of your work or you may never know what happiness is.

Go to the top of the page
 
DanielPineault
post Oct 7 2019, 09:44 AM
Post#2


UtterAccess VIP
Posts: 6,904
Joined: 30-June 11



CODE
=DLookUp("Amount","qryTotalExpenseVoucher","SchoolID = " & [SchoolID] & "And [M]=" & [Forms]![frmPayrollManagement]![txtMY])

but if [M] is a text field then it should be
CODE
=DLookUp("Amount","qryTotalExpenseVoucher","SchoolID = " & [SchoolID] & "And [M]='" & [Forms]![frmPayrollManagement]![txtMY] & "'")

--------------------
Daniel Pineault (2010-2019 Microsoft MVP, UA VIP, EE Distinguished Expert 2018)
Professional Help: https://www.cardaconsultants.com
Free MS Access Code, Tips, Tricks and Samples: https://www.devhut.net

* Design should never say "Look at me". It should always say "Look at this". -- David Craib
* A user interface is like a joke, if you have to explain it, it's not that good! -- Martin LeBlanc


All code samples, demonstration databases, links,... are provided 'AS IS' and are to be used at your own risk! Take the necessary steps to check, validate ...(you are responsible for your choices and actions)
Go to the top of the page
 
mike60smart
post Oct 7 2019, 11:46 AM
Post#3


UtterAccess VIP
Posts: 13,422
Joined: 6-June 05
From: Dunbar,Scotland


Hi Danielle

The first returns Error and the 2nd returns nothing ??

M is a Text Data Type ie Jan 2019


--------------------
Hope this helps?

Mike

Get happiness out of your work or you may never know what happiness is.

Go to the top of the page
 
DanielPineault
post Oct 7 2019, 12:10 PM
Post#4


UtterAccess VIP
Posts: 6,904
Joined: 30-June 11



CODE
=DLookUp("Amount", "qryTotalExpenseVoucher", "([SchoolID] = " & Me.SchoolID & ") And ([M] = '" & [Forms]![frmPayrollManagement]![txtMY] & "')")


It can also be helpful at times to build the criteria seperately so you can debug.print it to review exactly what filter you are applying.

CODE
Dim sFilter as String
sFilter = "([SchoolID] = " & Me.SchoolID & ") And ([M] = '" & [Forms]![frmPayrollManagement]![txtMY] & "')"
Debug.Print sFilter
=DLookUp("Amount", "qryTotalExpenseVoucher", sFilter )

--------------------
Daniel Pineault (2010-2019 Microsoft MVP, UA VIP, EE Distinguished Expert 2018)
Professional Help: https://www.cardaconsultants.com
Free MS Access Code, Tips, Tricks and Samples: https://www.devhut.net

* Design should never say "Look at me". It should always say "Look at this". -- David Craib
* A user interface is like a joke, if you have to explain it, it's not that good! -- Martin LeBlanc


All code samples, demonstration databases, links,... are provided 'AS IS' and are to be used at your own risk! Take the necessary steps to check, validate ...(you are responsible for your choices and actions)
Go to the top of the page
 
mike60smart
post Oct 7 2019, 12:20 PM
Post#5


UtterAccess VIP
Posts: 13,422
Joined: 6-June 05
From: Dunbar,Scotland


Hi Daniel

Perfect just Perfect

Many thanks yet again

cheers.gif

--------------------
Hope this helps?

Mike

Get happiness out of your work or you may never know what happiness is.

Go to the top of the page
 
DanielPineault
post Oct 7 2019, 12:27 PM
Post#6


UtterAccess VIP
Posts: 6,904
Joined: 30-June 11



Any time. thumbup.gif

--------------------
Daniel Pineault (2010-2019 Microsoft MVP, UA VIP, EE Distinguished Expert 2018)
Professional Help: https://www.cardaconsultants.com
Free MS Access Code, Tips, Tricks and Samples: https://www.devhut.net

* Design should never say "Look at me". It should always say "Look at this". -- David Craib
* A user interface is like a joke, if you have to explain it, it's not that good! -- Martin LeBlanc


All code samples, demonstration databases, links,... are provided 'AS IS' and are to be used at your own risk! Take the necessary steps to check, validate ...(you are responsible for your choices and actions)
Go to the top of the page
 
tina t
post Oct 7 2019, 02:15 PM
Post#7



Posts: 6,125
Joined: 11-November 10
From: SoCal, USA


QUOTE
=DLookUp("Amount","qryTotalExpenseVoucher","SchoolID = " & [SchoolID] & "And [M]=" & [Forms]![frmPayrollManagement]![txtMY])

but if [M] is a text field then it should be

=DLookUp("Amount","qryTotalExpenseVoucher","SchoolID = " & [SchoolID] & "And [M]='" & [Forms]![frmPayrollManagement]![txtMY] & "'")

actually, i think your first expressions were fine, Daniel, except for leaving out the space before And.

hth
tina

--------------------
"the wheel never stops turning"
Go to the top of the page
 
mike60smart
post Oct 7 2019, 02:30 PM
Post#8


UtterAccess VIP
Posts: 13,422
Joined: 6-June 05
From: Dunbar,Scotland


Hi Tina

Tried your suggestion and it gives Error same as Daniel's previous option.

Thanks for looking
kisses.gif

--------------------
Hope this helps?

Mike

Get happiness out of your work or you may never know what happiness is.

Go to the top of the page
 
tina t
post Oct 7 2019, 04:08 PM
Post#9



Posts: 6,125
Joined: 11-November 10
From: SoCal, USA


hmm, that's interesting. i wonder why adding the two sets of parentheses made the difference. well, anyway, his second solution works fine for you, so it's all good. :) tina

--------------------
"the wheel never stops turning"
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    22nd October 2019 - 09:22 AM