If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
DCount And and Or syntax
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 |
#3
|
|||
|
|||
DCount And and Or syntax
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 |
#4
|
|||
|
|||
DCount And and Or syntax
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 |
#5
|
|||
|
|||
DCount And and Or syntax
Thanks, all!
VR/Lost |
Thread Tools | |
Display Modes | |
|
|