DCount on report detail gets #Error

[sohunter]

Hello
I'm trying to get a value into a text box within the detail section of a report using DCount.

ProviderName is the name of another control (it's text box, control source is providerName, which is text) on a report.

=DCount("CampaignID","qryDistinctCampaigns","Name='" & [ProviderName] & "'")

I know there's data in the underlying query.

=DCount("*","qryDistinctCampaigns","Name='" & [ProviderName] & "'")

also doesn't work.

Is there a trick I'm missing to referring to the ProviderName control. I keep getting Error in the control !!

 

[LittleSmudge]

You listed two DCount examples - Do they both produce the same error ?

Are you sure that there is a field called CampaignId that is provided by the qryDistinctCampaigns.

CampaignId might exist in the table - but does it get through qryDistinctCampaigns ?

Is the field name in qryDistinctCampaigns really called "Name"

Using system words for field names is generally a bad idea

Try putting square brackets around Name and spacing the = ( for clarity ).

Ie
=DCount("CampaignID","qryDistinctCampaigns","[Name] = '" & ProviderName & "'")

That might help.

The square brackets around ProviderName are unnecessary if Providername is a text box control on the form.



'ope-that'elps.



G LS
spsinkNOJUNK@yahoo.co.uk
Remove the NOJUNK to use.

 

[dhookom]

LittleSmudge offers great advice. Also, make sure the name of the text box with your expression is not also the name of a field.

If you don't place []s around the ProviderName, then Access will put them there.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[sohunter]

thank you much

- Yes, they get the same error message

- I'm sure the field CampaignID exists in the query.

- I changed the name of "name" in the query to "Pname".

Here's my revision

=DCount("CampaignID","qryDistinctCampaigns","ProviderName='" & [PName] & "'")

My qryDistinctCampaigns has date parameters which match exactly the date parameters in the query underlying the report. I can pull the qryDistinctCampaigns just fine. And the report opens fine as well, with data looking good, except for this Dcount field.

I'm stumped.

 

[sohunter]

Oh goodness, now I'm really confusing you. I actually changed the control the expression refers to (within the same detail section) to PName - although that was probably not necessary - and the field in the query is now call ProvName.

=DCount("CampaignID","qryDistinctCampaigns","ProvName='" & [PName] & "'")

Looked over all my naming/syntax. Looks good. But I still get #error.

 

[dhookom]

1) is qryDistinctCampaigns a parameter query?
2) is qryDistinctCampaigns the record source of the report?
3) does qryDistinctCampaigns contain:
field named CampaignID
Text field named ProvName
4) does your report's record source contain a text field named PName?
5) is there a text box in the same section of the report named PName?
6) what is the name of the text box containing this expression?

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[sohunter]

Thanks Dhookum for hookin' up some good questions.

<< 1) is qryDistinctCampaigns a parameter query?>>

YES - there are date parameters. And I declare them at the top of the SQL: PARAMETERS [Enter start date:] DateTime, [Enter end date:] DateTime;
These match exactly the parameters in the underlying query for the report. Are parameters allowed for DCounts' record sources?

<<2) is qryDistinctCampaigns the record source of the report?>>

NO

<<3) does qryDistinctCampaigns contain:
field named CampaignID>> YES, a number
<<Text field named ProvName>> YES

<<4) does your report's record source contain a text field named PName?>>

NO, There is a text box/control called PName. The record source for the control is called ProviderName.

<< 5) is there a text box in the same section of the report named PName?>>

YES (see above.)

<<6) what is the name of the text box containing this expression? >>

PName


Re: questions 4 - 6 ...should I just refer to the field name "providerName" in my Dcount expression, instead of the control name "PName"?

 

[dhookom]

You can't use a parameter query in any domain aggregate function such as DCount(). This is just one of the many reasons for never using parameter queries.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[sohunter]

Thank you.

Learn something new everyday. Didn't see that important fact in Access VBA Help files, nor any Access book I have. Appreciate your help.