Go Premium for a chance to win a PS4. Enter to Win

x
Solved

# DCount using "OR"

Posted on 2016-11-28
Medium Priority
66 Views
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
Question by:vmccune
• 2

LVL 49

Accepted Solution

Dale Fye earned 1000 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 40

Assisted Solution

PatHartman earned 1000 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

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

Author Comment

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

## Featured Post

Question has a verified solution.

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

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
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.
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
###### Suggested Courses
Course of the Month8 days, 6 hours left to enroll