Solved

DCount criteria not working properly

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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

Suggested Solutions

A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
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…

803 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