Showing a value from a query on a form

[floyd33 via AccessMonster.com]

I have a count value from a crosstab query that I need to show on a form,
matching up id's from both. I have tried several different dcounts but can't
seem to get the correct value. This was the last formula I tried. Any help
is appreciated.

=DCount("[Total Of AttendeeCount]","[qryAttendeeCount_Crosstab]","
[qryAttendeeCount_Crosstab]![MeetingID]"="[frmMeeting]![MeetingID]")

 

[Chegu Tom]

if meeting ID is numeric
=DCount("[Total Of
AttendeeCount]","[qryAttendeeCount_Crosstab]","[MeetingID]=" &
[frmMeeting]![MeetingID] )


if meeting ID is text
=DCount("[Total Of
AttendeeCount]","[qryAttendeeCount_Crosstab]","[MeetingID]='" &
[frmMeeting]![MeetingID]) & "'"


if you are doing this on frmMeeting form then [frmMeeting]![MeetingID] could
be replaced by Me.MeetingID

 

[John W. Vinson]

I have a count value from a crosstab query that I need to show on a form,
matching up id's from both. I have tried several different dcounts but can't
seem to get the correct value. This was the last formula I tried. Any help
is appreciated.

=DCount("[Total Of AttendeeCount]","[qryAttendeeCount_Crosstab]","
[qryAttendeeCount_Crosstab]![MeetingID]"="[frmMeeting]![MeetingID]")

The syntax is wrong. You need the *value* of the MeetingID, not the name of
the control, in your search criterion. Try

=DCount("[Total Of AttendeeCount]", "[qryAttendeeCount_Crosstab]",
"[MeetingID]=" & [Forms]![frmMeeting]![MeetingID])

The first argument of DCount (or any domain function) is the name of the field
to be retrieved; the second argument is the name of a table or query, the
domain containing the field; and the third is an optional query criterion in
the form of a valid SQL WHERE clause (without the word WHERE). In this case
you want a WHERE clause like

[MeetingID] = 123

and you'll need to construct that WHERE clause from pieces - the fieldname
within qryAttendeeeCount_Crosstab, and the value from the form.

It's not clear whether you want to *count the number of records* in
qryAttendeeCount_Crosstab which match the criterion - in which case DCount()
is the correct function - or to *display the value of the field [Total of
AttendeeCount]* - in which case you should use DLookUp instead of DCount.

 

[floyd33 via AccessMonster.com]

I am getting this in the field "#Name?"

if meeting ID is numeric
=DCount("[Total Of
AttendeeCount]","[qryAttendeeCount_Crosstab]","[MeetingID]=" &
[frmMeeting]![MeetingID] )

if meeting ID is text
=DCount("[Total Of
AttendeeCount]","[qryAttendeeCount_Crosstab]","[MeetingID]='" &
[frmMeeting]![MeetingID]) & "'"

if you are doing this on frmMeeting form then [frmMeeting]![MeetingID] could
be replaced by Me.MeetingID

 

[floyd33 via AccessMonster.com]

John, thank you so much! That worked like a charm. I have two fields on the
form, both using formulas like the one you posted (pulling different id
criteria). Now I need to add both of those displayed fields. Is there an
easy way to do this?

I have a count value from a crosstab query that I need to show on a form,
matching up id's from both. I have tried several different dcounts but can't

[quoted text clipped - 3 lines]

=DCount("[Total Of AttendeeCount]","[qryAttendeeCount_Crosstab]","
[qryAttendeeCount_Crosstab]![MeetingID]"="[frmMeeting]![MeetingID]")

The syntax is wrong. You need the *value* of the MeetingID, not the name of
the control, in your search criterion. Try

=DCount("[Total Of AttendeeCount]", "[qryAttendeeCount_Crosstab]",
"[MeetingID]=" & [Forms]![frmMeeting]![MeetingID])

The first argument of DCount (or any domain function) is the name of the field
to be retrieved; the second argument is the name of a table or query, the
domain containing the field; and the third is an optional query criterion in
the form of a valid SQL WHERE clause (without the word WHERE). In this case
you want a WHERE clause like

[MeetingID] = 123

and you'll need to construct that WHERE clause from pieces - the fieldname
within qryAttendeeeCount_Crosstab, and the value from the form.

It's not clear whether you want to *count the number of records* in
qryAttendeeCount_Crosstab which match the criterion - in which case DCount()
is the correct function - or to *display the value of the field [Total of
AttendeeCount]* - in which case you should use DLookUp instead of DCount.

 

[John W. Vinson]

John, thank you so much! That worked like a charm. I have two fields on the
form, both using formulas like the one you posted (pulling different id
criteria). Now I need to add both of those displayed fields. Is there an
easy way to do this?

Just put the two DLookUps in the expression with a + operator between them,
I'd guess.

You didn't answer the question about what it is you're counting - do you in
fact want Dcount, or DLookUp?

 

[floyd33 via AccessMonster.com]

I just want to display the records, so I used DLookUp. I think I have it all
worked out, so thanks again!

 

[scottyboyb]

Greetings,

I am also trying to get a query value onto a form that does not use the
query as a data source. I want to display a count of the number of records
matching the form's criteria. The form has two subforms. The first subform
displays people who contribute and the second what they contribute. The form
shows this by type of contribution. I am trying to use the example you
provided above and I am getting something wrong.

Here is what I am trying:
=DCount("[PledgeId]", "[pledgeIDquery]",
"[PledgeID]=" & [Forms]![Campaigns]![CampaignName])

that is:
=DCount("[field from query -primary key autonumber of contributions]",
"[query name]", "[here is my mistake? - I don't understand what goes here]="
& [Forms]![My Form Name]![Form field name that contains form's criteria of
type of contribution])

Thanks in advance,
Scott B

 

[John W. Vinson]

Here is what I am trying:
=DCount("[PledgeId]", "[pledgeIDquery]",
"[PledgeID]=" & [Forms]![Campaigns]![CampaignName])

that is:
=DCount("[field from query -primary key autonumber of contributions]",
"[query name]", "[here is my mistake? - I don't understand what goes here]="
& [Forms]![My Form Name]![Form field name that contains form's criteria of
type of contribution])

Well, we can't help either if you don't describe the structure of your table.

What field in your table identifies the type of contribution? What is its
datatype? What form control contains the value that can be used?

 

[scottyboyb]

The field I am trying to count records for is an autonumber primary key of
the "Pledges" table which holds the data about the pledges made and paid by
contributors (different table). The field is called PledgeID. Datatype
autonumber.

The form control that provides the form's data criteria is on a form called
"Campaigns" and the form control is a text box that gets it's data from a
field whose datatype is text in a table called "Donation Campaign Setup". The
text box's control source is a field in "Donation Campaign Setup" called
"CampaignName" and the text box's name is CampaignNameField.

The control for the DCount expression is a text box on the same form
"Campaigns" with the name "Total Number of Pledges".

Is there anythng else you need to know?

Best,
Scott

Here is what I am trying:
=DCount("[PledgeId]", "[pledgeIDquery]",
"[PledgeID]=" & [Forms]![Campaigns]![CampaignName])

that is:
=DCount("[field from query -primary key autonumber of contributions]",
"[query name]", "[here is my mistake? - I don't understand what goes here]="
& [Forms]![My Form Name]![Form field name that contains form's criteria of
type of contribution])

Well, we can't help either if you don't describe the structure of your table.

What field in your table identifies the type of contribution? What is its
datatype? What form control contains the value that can be used?

 

[John W. Vinson]

The field I am trying to count records for is an autonumber primary key of
the "Pledges" table which holds the data about the pledges made and paid by
contributors (different table). The field is called PledgeID. Datatype
autonumber.

Well, if you are just counting records, it's not essential to count the
Primary Key field: you could use =DCount("*"...) to count records. Should give
the same results though.

The form control that provides the form's data criteria is on a form called
"Campaigns" and the form control is a text box that gets it's data from a
field whose datatype is text in a table called "Donation Campaign Setup". The
text box's control source is a field in "Donation Campaign Setup" called
"CampaignName" and the text box's name is CampaignNameField.

The control for the DCount expression is a text box on the same form
"Campaigns" with the name "Total Number of Pledges".

Is there anythng else you need to know?

I believe so: which field in "Pledges" are you trying to match to the value in
CampaignNameField? Assuming that it's Campaign and that it's a Text field,
then

=DCount("[PledgeID]", "Pledges", "[Campaign] = '" &
Forms!Campaigns![CampaignNameField] & "'")

That's two possibly incorrect assumptions there (fieldname and datatype) but
hopefully you can adapt...

The trick is that the third argument to any domain function needs to be a SQL
WHERE clause without the word WHERE: in my suggestion,

[Campaign] = 'Halloween Party'