Solved

DCount using "OR"

Posted on 2016-11-28
4
53 Views
Last Modified: 2016-11-28
I am trying to use a dcount function with an "OR" statement and I am not having much luck.  I am not even sure if or is permissible.

Here is my statement.

Me.SessionNumber = Nz(DMax("SessionNumber", "tblSessions", "[VisId]=" & [Forms]![frmVisits00]![frmVisits01].[Form]![visid] & " AND [SessionType] = 'Group' OR 'Group-Other'")) + 1

For just GROUP it works fine.  for GROUP or GROUP-OTHER it then counts across the entire domain instead of just the VisId.

Thanks
0
Comment
Question by:vmccune
  • 2
4 Comments
 
LVL 47

Accepted Solution

by:
Dale Fye (Access MVP) earned 250 total points
ID: 41904059
When I'm using domain functions outside of a ControlSource property, I generally split the critieria and the function itself into two parts, which makes it easier to debug.  In your case, you have to explicitly use the reference to the [SessionType] field.  I also like to wrap each criteria in a set of parenthesis just to make sure the logic is processed the way I expect it to be.

Dim strCriteria as string
strCriteria = "([VisId]=" & [Forms]![frmVisits00]![frmVisits01].[Form]![visid] & ") AND " _
                   & ([SessionType] = 'Group' OR [SessionType] = 'Group-Other')"
debug.print strCriteria
Me.SessionNumber = Nz(DMax("SessionNumber", "tblSessions", strCriteria), 0) + 1
0
 
LVL 36

Assisted Solution

by:PatHartman
PatHartman earned 250 total points
ID: 41904597
When you combine AND and OR in a single expression, you MUST understand the order of precedence.  Your expression will only work correctly if you use parentheses correctly to control the execution order.  Dale gave you the answer but he equivocated rather than making a firm point so I am repeating his suggestion as a requirement.  AND is evaluated before OR so the expression as written was being evaluated as:

(A AND B) OR C

But what you want is:

A AND (B OR C)
0
 

Author Comment

by:vmccune
ID: 41904620
Pasted your code and the strCriteria = section is all red.
0
 

Author Comment

by:vmccune
ID: 41904661
The two answers combined gave it to me.  Thanks!
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
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.

685 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