DLookup in VBA code (1 Viewer)

Lkwdmntr

Registered User.
Local time
Today, 13:36
Joined
Jul 10, 2019
Messages
277
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")​
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 13:36
Joined
Aug 30, 2003
Messages
36,118
Your parentheses look out of whack. Take out any not directly required for a DLookup(). They aren't necessary with only addition and subtraction.
 

Lkwdmntr

Registered User.
Local time
Today, 13:36
Joined
Jul 10, 2019
Messages
277
Thanks, that did it.
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 13:36
Joined
Aug 30, 2003
Messages
36,118
Happy to help!
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 16:36
Joined
Feb 19, 2002
Messages
42,975
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.
 

isladogs

MVP / VIP
Local time
Today, 20:36
Joined
Jan 14, 2017
Messages
18,186
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)
 

Users who are viewing this thread

Top Bottom