?
Solved

DCount criteria not working properly

Posted on 2009-04-15
4
Medium Priority
?
334 Views
Last Modified: 2013-11-27
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

0
Comment
Question by:LizzJ
  • 2
  • 2
4 Comments
 
LVL 77

Accepted Solution

by:
peter57r earned 2000 total points
ID: 24146212
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
 

Author Comment

by:LizzJ
ID: 24146254
dun quite get it though. why need extra brackets?
0
 

Author Comment

by:LizzJ
ID: 24146264
it works though.... weird
0
 
LVL 77

Expert Comment

by:peter57r
ID: 24146363
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

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Suggested Courses

807 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question