?
Solved

DCount using "OR"

Posted on 2016-11-28
4
Medium Priority
?
62 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 48

Accepted Solution

by:
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 38

Assisted Solution

by:PatHartman
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

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

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

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Suggested Courses

777 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