DCount And and Or syntax

[Lostguy]

Hello.

I am having problems with the OR portion of this DCount statement:

=DCount("*","qryDSR","[ServiceEntityName]='Army' AND [Paygrade] Like
'O-*' or Like 'W-*'")


(Count the number of Army and O- (O-1,O-2,O-3,etc.) or W- (W-1, W-2,
etc.)

Where is the syntax wrong?


Also, is is true that it is better to use double-double quotes ("")
(four lines) rather than a single quote(')(one line)?

??

VR/Lost

 

[Wayne-I-M]

Hi

Use Like instead of =

This would work if you table was called TableName and your primary field was
called ID

Note that I have shown the full sytax so you can cut it down if you like but
I thought you may find this usfl
Also I have used >0 to get rid of anything that isn't what your looking for
- but you could realy get rd of that bit !!
So - of course - this is way more than you need but I hope you find it
helpful and it gives you a lead to how DCount works

Enjoy ;-)

SELECT DCount([ID],"TableName",[TableName]![ServiceEntityName]="Army" And
[TableName]![Paygrade] Like "O-*" Or [TableName]![Paygrade] Like "W-*") AS
SomeName
FROM TableName
GROUP BY DCount([ID],"TableName",[TableName]![ServiceEntityName]="Army" And
[TableName]![Paygrade] Like "O-*" Or [TableName]![Paygrade] Like "W-*")
HAVING (((DCount([ID],"TableName",[TableName]![ServiceEntityName]="Army" And
[TableName]![Paygrade] Like "O-*" Or [TableName]![Paygrade] Like "W-*"))>0));

 

[Douglas J. Steele]

You need to repeat the field name for each value to which you're comparing.
Also, because And gets evaluated before Or, you need parentheses:

=DCount("*","qryDSR","[ServiceEntityName]='Army' AND ([Paygrade] Like 'O-*'
or [Paygrade] Like
Like 'W-*'")")

 

[John W. Vinson]

Hello.

I am having problems with the OR portion of this DCount statement:

=DCount("*","qryDSR","[ServiceEntityName]='Army' AND [Paygrade] Like
'O-*' or Like 'W-*'")


(Count the number of Army and O- (O-1,O-2,O-3,etc.) or W- (W-1, W-2,
etc.)

Where is the syntax wrong?

The OR operator LOOKS like the English language conjunction... but it isn't.
It's an operator in Boolean algebra, which returns TRUE if either of its
arguments is TRUE, and false otherwise.

You're comparing the expression

[Paygrade] LIKE 'O-*'

which might be either true or false for any given record, with the expression

LIKE 'W-*'

which isn't meaningful to Access at all.

You need to use the fieldname twice, so you're comparing two true-or-false
expressions. You also need to enclose the entire paygrade portion in
parentheses so that you're looking just at Army folks - otherwise you'll be
using

[ServiceEntityName]='Army' AND [Paygrade] Like 'O-*'

OR

[Paygrade] LIKE 'W-*'

i.e. all Army O- values, and all W- values regardless of service.

The correct syntax would be

=DCount("*","qryDSR","[ServiceEntityName]='Army' AND ([Paygrade] Like
'O-*' or [Paygrade] Like 'W-*')")

Also, is is true that it is better to use double-double quotes ("")
(four lines) rather than a single quote(')(one line)?

Only if the value being searched for might contain an apostrophe: this comes
up if you're looking for names ("O'Brien" or "Fred's Bait Shop" for example).
If you're dealing with text such as pay grades that you can be sure won't have
an apostrophe, then singlequotes are simpler.

 

[John Spencer]

DCount("*","qryDSR","[ServiceEntityName]='Army' AND ([Paygrade] Like
'O-*' or Like 'W-*')")

OR

DCount("*","qryDSR","[ServiceEntityName]='Army' AND [Paygrade] Like
'[OW]-*'")

And both quotes and apostrophes work. As far as I know there is no
difference in performance. There can be some confusion if your criteria
contains an apostrophe - O'Connor Or if you have quotes in the criteria.


'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[John Spencer]

Whoops! Missed adding the full comparison in the first example.

DCount("*","qryDSR","[ServiceEntityName]='Army' AND ([Paygrade] Like
'O-*' or [Paygrade] Like 'W-*')")

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

DCount("*","qryDSR","[ServiceEntityName]='Army' AND ([Paygrade] Like
'O-*' or Like 'W-*')")

OR

DCount("*","qryDSR","[ServiceEntityName]='Army' AND [Paygrade] Like
'[OW]-*'")

And both quotes and apostrophes work. As far as I know there is no
difference in performance. There can be some confusion if your criteria
contains an apostrophe - O'Connor Or if you have quotes in the criteria.


'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

Hello.

I am having problems with the OR portion of this DCount statement:

=DCount("*","qryDSR","[ServiceEntityName]='Army' AND [Paygrade] Like
'O-*' or Like 'W-*'")


(Count the number of Army and O- (O-1,O-2,O-3,etc.) or W- (W-1, W-2,
etc.)

Where is the syntax wrong?


Also, is is true that it is better to use double-double quotes ("")
(four lines) rather than a single quote(')(one line)?

??

VR/Lost

 

[Lostguy]

Thanks, all!

VR/Lost