Can a DCount criteria argument be a variable? 1

[Vie]

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

 

[RoyVidar]

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

 

[Vie]

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

 

[RoyVidar]

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

 

[Vie]

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!