Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations TouchToneTommy on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Can a DCount criteria argument be a variable? 1

Status
Not open for further replies.

Vie

Technical User
Jan 22, 2004
115
US
I have a kind of stupid question. I'm trying to use the DCount function and can't seem to make it work with a variable in the criteria argument.

=DCount(<<&quot;expr>>&quot;,<<&quot;domain&quot;>>,<<&quot;criteria>> = variable&quot;)

Do variables work with domain aggregate functions? If so, what's the syntax?

THX!
Vie
 
No problem. If youre using code:

[tt]MyVal=DCount(<<&quot;expr>>&quot;,<<&quot;domain&quot;>>,<<&quot;criteria>> = &quot; & MyVariable)[/tt]

For a numeric variable. If other remember the qualifiers, hash (#) for dates, single quote for text (').

If you're using it as controlsource, you'll need to create a user defined function to retrieve the value.

[tt]Public Function GetMyVariable() as <datatype you return>
GetMyVariable = MyVariable
End Function[/tt]

Then just use GetMyVariable() as criteria.

Should you want to use form controls, check F1 (help) on dcount (and perhaps the other domain aggregate functions), where examples are found.

Roy-Vidar
 
Thanks Roy

I've never used functions in a module before so this is breaking new ground for me. It is the controlsource of a textbox that I'm trying to set. What you suggested works great. I just have one other problem. At first I didn't realize I would need this but my criteria actually has to have two arguments. As far as I know, Domain aggregate functions are written more or less like SQL, so I have been trying to get the second argument to work using the Like operator. I need something like:

=DCount(&quot;[NoticeID]&quot;,&quot;qryDates&quot;,&quot;[ResidentID] = &quot; & GetintResidentID()) And [NoticeDescription] Like &quot;Mandatory*&quot;)

This doesn't return an error but it doesn't return the correct value either. Any advice?

Vie
 
Hi!

Yes the criteria part of the domain aggreagate functions is the equivalent of a sql where clause without the keyword Where (from the help files).

Without testing your line, I'd assume the following should work (text qualifiers etc...)

[tt]=DCount(&quot;[NoticeID]&quot;,&quot;qryDates&quot;,&quot;[ResidentID] = &quot; & GetintResidentID() & &quot; And [NoticeDescription] Like 'Mandatory*'&quot;)[/tt]

- all on one line

If mandatory is a text. Should it be a control on a form (holding text), perhaps something like this as the last part:

[tt]...&quot; And [NoticeDescription] Like '&quot; & [Mandatory] & &quot;*'&quot;)[/tt]

Roy-Vidar
 
Wow. That does work. I feel like I dinked around with every possible permutation of qualifiers but that one I missed. I had looked at MS KnowledgeBase Article 285866 (for some reason my help menu returns a blank page on some subjects, including &quot;domain aggregate&quot; and &quot;DCount&quot;) and it gives so many versions of the syntax that it's difficult to tell which one fit my situation.

Thanks again, I'm really grateful for your help!
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top