DCount And and Or syntax

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

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));



--
Wayne
Manchester, England.



"Lostguy" wrote:

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

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-*'")")


--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Lostguy" wrote in message
...
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

On Sat, 29 Aug 2009 10:41:00 -0700 (PDT), Lostguy wrote:

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 W. Vinson [MVP]
??

VR/Lost

Thanks, all!

VR/Lost