Go Back   Access World Forums > Microsoft Access Discussion > Modules & VBA

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 11-09-2019, 08:46 PM   #1
Lkwdmntr
Newly Registered User
 
Join Date: Jul 2019
Posts: 105
Thanks: 2
Thanked 0 Times in 0 Posts
Lkwdmntr is on a distinguished road
DLookup in VBA code

Hi Guys,

I am working on a point system and made several queries to count items for the points. I tried this VBA code and am getting a syntax error. DLookup is a little different in the code than in access itself. Can anyone help me get this right. CountMonSTC = 21, CountMonStMeals = 9, CountAllBA = 12 which should make Me.MonPotAcntPts = 33 (9 + (21 - 9) + 12)
Me.MonPotAcntPts = (DLookup("Monday", "CountMonMeals") + ((DLookup("Monday", "CountMonSTC") - (DLookup("Monday", "CountMonMeals")) + DLookup("Monday", "CountAllBAMon")

Lkwdmntr is offline   Reply With Quote
Old 11-09-2019, 08:54 PM   #2
pbaldy
Wino Moderator
 
pbaldy's Avatar
 
Join Date: Aug 2003
Location: Nevada, USA
Posts: 33,263
Thanks: 13
Thanked 4,110 Times in 4,042 Posts
pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold
Re: DLookup in VBA code

Your parentheses look out of whack. Take out any not directly required for a DLookup(). They aren't necessary with only addition and subtraction.
__________________
Paul
Microsoft Access MVP 2007-2019

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
pbaldy is offline   Reply With Quote
The Following User Says Thank You to pbaldy For This Useful Post:
Lkwdmntr (11-09-2019)
Old 11-09-2019, 09:32 PM   #3
Lkwdmntr
Newly Registered User
 
Join Date: Jul 2019
Posts: 105
Thanks: 2
Thanked 0 Times in 0 Posts
Lkwdmntr is on a distinguished road
Re: DLookup in VBA code

Thanks, that did it.

Lkwdmntr is offline   Reply With Quote
Old 11-09-2019, 10:02 PM   #4
pbaldy
Wino Moderator
 
pbaldy's Avatar
 
Join Date: Aug 2003
Location: Nevada, USA
Posts: 33,263
Thanks: 13
Thanked 4,110 Times in 4,042 Posts
pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold
Re: DLookup in VBA code

Happy to help!
__________________
Paul
Microsoft Access MVP 2007-2019

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
pbaldy is offline   Reply With Quote
Old 11-11-2019, 07:31 PM   #5
Pat Hartman
Super Moderator
 
Join Date: Feb 2002
Location: Stratford,Ct USA
Posts: 28,249
Thanks: 15
Thanked 1,592 Times in 1,512 Posts
Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all
Re: DLookup in VBA code

If any of the lookups return null, the result of the expression will be null. if there is any possibility of nulls in an expression, use Nz() to convert them to zeros.

Me.MonPotAcntPts = Nz(DLookup("Monday", "CountMonMeals"),0) + Nz(DLookup("Monday", "CountMonSTC"),0) - Nz((DLookup("Monday", "CountMonMeals"),0) + Nz(DLookup("Monday", "CountAllBAMon"),0)

PS, there's probably a better way to do this. You might want to look into using a crosstab rather than a bunch of separate queries. Just include a sum in the crosstab.

PPS, if you are storing this calculated data, you might want to reconsider that also.
__________________
Bridge Players Still Know All the Tricks
Pat Hartman is offline   Reply With Quote
Old 11-12-2019, 12:20 AM   #6
isladogs
Part time moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 10,951
Thanks: 114
Thanked 2,993 Times in 2,721 Posts
isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold
Re: DLookup in VBA code

Something not mentioned so far...
You have two identical DLookups for CountMonMeals with + & - which cancel each other out.
Omit both!

Code:
Me.MonPotAcntPts = Nz(DLookup("Monday", "CountMonSTC"),0) + Nz(DLookup("Monday", "CountAllBAMon"),0)

__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin (Mendip Data Systems)
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
isladogs is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Using DLOOKUP in VBA Code MatthewH Modules & VBA 4 05-11-2017 03:14 PM
VBA Code for DLOOKUP MatthewH Modules & VBA 8 05-04-2017 04:35 AM
DLookup code not working in VBA abzalali Modules & VBA 10 03-19-2014 05:29 PM
Why isn't this Dlookup code firing? papic1972 Forms 9 10-05-2010 05:13 PM
Dlookup code need help eholtman Macros 1 02-06-2005 07:45 PM




All times are GMT -8. The time now is 08:35 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World