DCount Criteria - Easy Question!

[Robin]

I cannot get this to work in a calculated control on the form 'fmCData':

=DCount("[ClientID]","ClientX","[ClientX]![ClientID]='Me![ClientID]'")

Result = 0 when it shoud be 3
It works when I hard code a ClientID in place of Me![ClientID] (Result = 3)

I've also tried:
[Form]![fmCData]![ClientID]
Forms![fmCData]![ClientID]
[fmCData]![ClientID]
[ClientID]

Any help please. And where can I find "rules" on when you use ! vs . and
brackets around (ie) [Form] rather than Forms! etc...

Thank you,
Robin

 

[Allen Browne]

Concatenate the value of the ClientID into the 3rd string:

If ClientID is a Number field (when you open ClientX in design view), try:
=DCount("ClientID","ClientX", "ClientID = " & Nz([ClientID],0))

If it's a Text type field, you need extra quotes:
=DCount("ClientID","ClientX", "ClientID = """ & [ClientID] & """")

For an explanation of the quotes, see:
Quotation marks within quotes
at:
http://allenbrowne.com/casu-17.html

The Nz() suggested for the number, is so that it doesn't fail when ClientID
is blank. In that case, the 3rd string would evaluate to just:
ClientID =
which is clearly invalid.

You can't use "Me" outside of VBA code.
You could probably use a full reference such as:
=DCount("ClientID","ClientX", "ClientID = [Forms]![Form1]![ClientID]")

As for bang verses dot, that's a long story.
You must use dot when referring to a property e.g. [ClientID].Visible.
You should use dot in queries when referring to fields of a table.
You must use bang in code that refers to fields of a recordset.
You can use dot or bang when referring to controls on a form/report.
For a detailed response, see Andy Baron's article:
Cleaner Coding: Bang vs. Dot
at:
http://advisor.com/doc/05352

HTH

 

[fredg]

I cannot get this to work in a calculated control on the form 'fmCData':

=DCount("[ClientID]","ClientX","[ClientX]![ClientID]='Me![ClientID]'")

Result = 0 when it shoud be 3
It works when I hard code a ClientID in place of Me![ClientID] (Result = 3)

I've also tried:
[Form]![fmCData]![ClientID]
Forms![fmCData]![ClientID]
[fmCData]![ClientID]
[ClientID]

Any help please. And where can I find "rules" on when you use ! vs . and
brackets around (ie) [Form] rather than Forms! etc...

Thank you,
Robin

What is the datatype of the [ClientID] field?
If it is Text datatype....

=DCount("*","ClientX","[ClientID]= '" & Me![ClientID] & "'")
The quotes, for clarity are:
"[ClientID]= ' " & Me![ClientID] & " ' ")

If it is Number datatype...

=DCount("*","ClientX","[ClientID]= " & Me![ClientID])

 

[tina]

I cannot get this to work in a calculated control on the form 'fmCData':

=DCount("[ClientID]","ClientX","[ClientX]![ClientID]='Me![ClientID]'")

your syntax is more correct for use in a VBA module (though not quite
right). since this expression is hard-coded in the ControlSource property of
a textbox control on a form, change the syntax to

=DCount("[ClientID]","ClientX","[ClientID]=" & [ClientID])

the above syntax assumes the ClientID is a Number data type. if it is a Text
data type, change the syntax to

=DCount("[ClientID]","ClientX","[ClientID]='" & [ClientID] & "'")

if you were using the expression in a VBA module, the syntax would be

DCount("ClientID","ClientX","ClientID = " & Me!ClientID)

or, if text ClientID,

DCount("ClientID","ClientX","ClientID = '" & Me!ClientID & "'")

suggest you read up on the domain aggregate functions (DCount, DSum,
DLookup, etc) in Access Help, so you'll understand better how the arguments
work.

where can I find "rules" on when you use ! vs . and
brackets around (ie) [Form] rather than Forms! etc...

for info on "dot vs bang", try googling these newsgroups for some variation
of that phrase - it's been discussed many times. re the brackets: brackets
tell the system that what's inside of it is a single name, basically. you
usually only need brackets in VBA when there is a space in the name you're
referencing (poor naming convention bites you in the a** there), or you're
using an Access Reserved word such as Name or Date (ditto poor naming
convention...), or perhaps if you're using a special character - something
other than letters, numbers, or an underscore character ( _ ) in a name
(once again, poor...). the brackets do not replace the bang (!), or the dot
(.), for that matter - completely separate issue. outside of VBA, i usually
don't include brackets automatically. in some places in the Access
development environment, the system will add brackets automatically after
you enter a reference; if my expression errs and i can't find any syntax
errors, my next step might be to try adding brackets around names to see if
it makes things more clear to the system.

hth

I cannot get this to work in a calculated control on the form 'fmCData':

=DCount("[ClientID]","ClientX","[ClientX]![ClientID]='Me![ClientID]'")

Result = 0 when it shoud be 3
It works when I hard code a ClientID in place of Me![ClientID] (Result = 3)

I've also tried:
[Form]![fmCData]![ClientID]
Forms![fmCData]![ClientID]
[fmCData]![ClientID]
[ClientID]

Any help please. And where can I find "rules" on when you use ! vs . and
brackets around (ie) [Form] rather than Forms! etc...

Thank you,
Robin

 

[Robin]

That works! Thank you for that and for your references to usage of quotes
and dot bang. I really thought I was doing it right with the single quotes
within the double quotes because I use that a lot within my VBA coding.

I don't mind researching my own questions but I just couldn't find any
concise references to those usage rules in MS Help. Thank you again, those
references and your explanation will be a BIG help as I progress with my
application.

Robin

Concatenate the value of the ClientID into the 3rd string:

If ClientID is a Number field (when you open ClientX in design view), try:
=DCount("ClientID","ClientX", "ClientID = " & Nz([ClientID],0))

If it's a Text type field, you need extra quotes:
=DCount("ClientID","ClientX", "ClientID = """ & [ClientID] & """")

For an explanation of the quotes, see:
Quotation marks within quotes
at:
http://allenbrowne.com/casu-17.html

The Nz() suggested for the number, is so that it doesn't fail when ClientID
is blank. In that case, the 3rd string would evaluate to just:
ClientID =
which is clearly invalid.

You can't use "Me" outside of VBA code.
You could probably use a full reference such as:
=DCount("ClientID","ClientX", "ClientID = [Forms]![Form1]![ClientID]")

As for bang verses dot, that's a long story.
You must use dot when referring to a property e.g. [ClientID].Visible.
You should use dot in queries when referring to fields of a table.
You must use bang in code that refers to fields of a recordset.
You can use dot or bang when referring to controls on a form/report.
For a detailed response, see Andy Baron's article:
Cleaner Coding: Bang vs. Dot
at:
http://advisor.com/doc/05352

HTH

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I cannot get this to work in a calculated control on the form 'fmCData':

=DCount("[ClientID]","ClientX","[ClientX]![ClientID]='Me![ClientID]'")

Result = 0 when it shoud be 3
It works when I hard code a ClientID in place of Me![ClientID] (Result =
3)

I've also tried:
[Form]![fmCData]![ClientID]
Forms![fmCData]![ClientID]
[fmCData]![ClientID]
[ClientID]

Any help please. And where can I find "rules" on when you use ! vs . and
brackets around (ie) [Form] rather than Forms! etc...