DLookup in VBA code

[Lkwdmntr]

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]

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]

Thanks, that did it.

 

[pbaldy]

Happy to help!

 

[isladogs]

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

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