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!

 

[Pat Hartman]

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]

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)