Showing a value from a query on a form

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]")

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/200910/1

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

On Tue, 20 Oct 2009 18:38:01 GMT, "floyd33 via AccessMonster.com" u17874@uwe
wrote:

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.
--

John W. Vinson [MVP]

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

Chegu Tom wrote:
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

--
Message posted via http://www.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?

John W. Vinson wrote:
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.

--
Message posted via http://www.accessmonster.com

On Tue, 20 Oct 2009 19:24:25 GMT, "floyd33 via AccessMonster.com" u17874@uwe
wrote:

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?

--

John W. Vinson [MVP]

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

John W. Vinson wrote:
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?


--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/200910/1

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

"floyd33 via AccessMonster.com" wrote:

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

John W. Vinson wrote:
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?


--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/200910/1

.

On Thu, 22 Oct 2009 05:32:01 -0700, scottyboyb
wrote:

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 [MVP]

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


"John W. Vinson" wrote:

On Thu, 22 Oct 2009 05:32:01 -0700, scottyboyb
wrote:

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 [MVP]
.