Solved

DCount criteria not working properly

Posted on 2009-04-15
4
318 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 500 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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

895 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now