DCount criteria not working properly

Hi experts,

I dun know why the following Dcount function is not giving me the correct count:

When I manually check from the table, I realized the criteria [Status] = 'a1' OR [Status] = 'a2' is not being read.

Can anyone help me resolve the issue here?
Many Thanks
myCount = Nz(DCount("[Number]", "TempOthers2", "[Type] = 'XFER' AND [Booking] = 'bbbb' " _
    & "AND ([Status] = 'a1' OR [Status] = 'a2') AND (Left([aField], 13) = 'UBSSG.LON.OTC' OR Left([aField], 15) = 'BARCSSG.LON.OTC' " _
    & "OR Left([aField], 13) = 'BNPSG.PAR.OTC') OR (Left([bField], 13) = 'UBSSG.LON.OTC' OR Left([bField], 15) = 'BARCSSG.LON.OTC' " _
    & "OR Left([bField], 13) = 'BNPSG.PAR.OTC')"), 0)

Open in new window

LizzJAsked:
Who is Participating?
 
peter57rConnect With a Mentor Commented:
I'm guessing that what you really want is..

myCount = Nz(DCount("[Number]", "TempOthers2", "[Type] = 'XFER' AND [Booking] = 'bbbb' " _
    & "AND ([Status] = 'a1' OR [Status] = 'a2') AND ((Left([aField], 13) = 'UBSSG.LON.OTC' OR Left([aField], 15) = 'BARCSSG.LON.OTC' " _
    & "OR Left([aField], 13) = 'BNPSG.PAR.OTC') OR (Left([bField], 13) = 'UBSSG.LON.OTC' OR Left([bField], 15) = 'BARCSSG.LON.OTC' " _
    & "OR Left([bField], 13) = 'BNPSG.PAR.OTC'))"), 0)
I've added extra brackets around the big Or clauses.
'a2') AND ((Left.............OTC'))"), 0)
0
 
LizzJAuthor Commented:
dun quite get it though. why need extra brackets?
0
 
LizzJAuthor Commented:
it works though.... weird
0
 
peter57rCommented:
Your structure was:

a=1 and b=2 and (c=3 or d=4) and (e=5 or f=6) or (g=7 or h=8)
AND has precedence over OR so this was read as:

( a=1 and b=2 and (c=3 or d=4) and (e=5 or f=6) ) or (g=7 or h=8)

so all of the first conditions must be true Or one of the last

I assumed yu always wanted the first set of conditions to be true and at least one of the big OR clauses to be true so you need to force  Access to read it that way.
a=1 and b=2 and (c=3 or d=4) and ( (e=5 or f=6) or (g=7 or h=8) )
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.