Help! - calculated control

[Lori]

I have a field called Current Openings on a form based on a table called
tbl_Zip_Code. This field should count the number of active vendors within a
zip code, local agency combination.

I got this to work on my subform (it counts all records regardless of zip
code and LA Nbr):

=DCount("[Vendor_Nbr]","qry_Zip_Code_Vendors","[Status_Code] = 'Active' and
[Peer_Group_Code] = 11")

I need to make it work within the current openings field to narrow the count
to each zip code, LA Nbr combination (which is what each record in the form
is based on) - how can I do this? Do I add to the criteria portion of the
function?

Sub_frm_vendor_zip_code is on my form and is based on a query called
qry_vendor_zip_code (run off of the tbl_vendor). This subform displays all
vendors for the zip code displayed on the form by linking the p_zip_code and
LA_Nbr fields in tbl_vendor to the zip_code and LA_Nbr fields in tbl_zip_code.

Thanks for any help - I know this is a lot of info - I was trying to provide
any help I could give. Lori

 

[John Vinson]

I have a field called Current Openings on a form based on a table called
tbl_Zip_Code. This field should count the number of active vendors within a
zip code, local agency combination.

I got this to work on my subform (it counts all records regardless of zip
code and LA Nbr):

=DCount("[Vendor_Nbr]","qry_Zip_Code_Vendors","[Status_Code] = 'Active' and
[Peer_Group_Code] = 11")

I need to make it work within the current openings field to narrow the count
to each zip code, LA Nbr combination (which is what each record in the form
is based on) - how can I do this? Do I add to the criteria portion of the
function?

Just reference the name of the other control *outside* the quotemarks:

=DCount("[Vendor_Nbr]","qry_Zip_Code_Vendors","[Status_Code] =
'Active' AND [Peer_Group_Code] = 11 AND [LA Nbr] = " & [LA Nbr])

or, if LA Nbr is a Text field,

=DCount("[Vendor_Nbr]","qry_Zip_Code_Vendors","[Status_Code] =
'Active' AND [Peer_Group_Code] = 11 AND [LA Nbr] = '" & [LA Nbr] &
"'")

John W. Vinson[MVP]

 

[Lori]

I tried the code you provided and I got an error - #Name? Also, how would I
include the other part of the primary key? The zip code and local agency
code make up the primary key of the zip code table which is the source of the
main form. txt_Zip_Code (on the main form) needs to match with P_Zip_Code
(on the subform, based on the query listed in the code below)

Here's the code I tried (without the zip code included):

=DCount("[Vendor_Nbr]","qry_Zip_Code_Vendors","[Status_Code] = 'Active' AND
[Peer_Group_Code] = 11 AND [Local_Agency_Code] = " & [txt_Local_Agency_Code])

Thanks for your help!

I have a field called Current Openings on a form based on a table called
tbl_Zip_Code. This field should count the number of active vendors within a
zip code, local agency combination.

I got this to work on my subform (it counts all records regardless of zip
code and LA Nbr):

=DCount("[Vendor_Nbr]","qry_Zip_Code_Vendors","[Status_Code] = 'Active' and
[Peer_Group_Code] = 11")

I need to make it work within the current openings field to narrow the count
to each zip code, LA Nbr combination (which is what each record in the form
is based on) - how can I do this? Do I add to the criteria portion of the
function?

Just reference the name of the other control *outside* the quotemarks:

=DCount("[Vendor_Nbr]","qry_Zip_Code_Vendors","[Status_Code] =
'Active' AND [Peer_Group_Code] = 11 AND [LA Nbr] = " & [LA Nbr])

or, if LA Nbr is a Text field,

=DCount("[Vendor_Nbr]","qry_Zip_Code_Vendors","[Status_Code] =
'Active' AND [Peer_Group_Code] = 11 AND [LA Nbr] = '" & [LA Nbr] &
"'")

John W. Vinson[MVP]

 

[John Vinson]

I tried the code you provided and I got an error - #Name? Also, how would I
include the other part of the primary key? The zip code and local agency
code make up the primary key of the zip code table which is the source of the
main form. txt_Zip_Code (on the main form) needs to match with P_Zip_Code
(on the subform, based on the query listed in the code below)

Here's the code I tried (without the zip code included):

=DCount("[Vendor_Nbr]","qry_Zip_Code_Vendors","[Status_Code] = 'Active' AND
[Peer_Group_Code] = 11 AND [Local_Agency_Code] = " & [txt_Local_Agency_Code])

Thanks for your help!

Bear in mind, you know the names of the fields in your table and their
datatypes, and the names of the control on your form - I don't.

The principle is that the third argument of the DCount() function
needs to be constructed as a text string which is a valid SQL WHERE
clause (without the word WHERE). If you can build a Query that gets
the values you want, you can look at its SQL view to find the syntax.

In short, you need quote marks (either ' or ") around the criterion
for any Text field. Date fields need # as a delimiter; number or
currency fields get no delimiters. You can assemble the WHERE string
from a mixture of text literals in quotes and references to form
controls; if the control is not on the current form, you need a
reference to the form containing it.

GUESSING here - again, I can't see your database:

"[Status_Code] = 'Active' AND [Peer_Group_Code] = 11 AND
[Local_Agency_Code] = '" & Parent![txt_Local_Agency_Code] & "' AND
[P_Zip_Code] = '" & Parent![txt_Zip_Code] & "'")

If the local agency code and the zipcode are included in the
Master/Child Link Fields then they should also be available on the
subform so you might not need the Parent! - however, you will need to
use a name which is local to that subform.

John W. Vinson[MVP]

 

[Lori]

John,

I didn't take your code example literally, I did plug in my field names in
your example. I appreciate your help and if my previous email implied that I
expected you to magically know my fields, then I apologize. I'm not sure how
you got that impression. I only gave info that I thought would be helpful -
I just wanted you to be able to distinguish where each field was coming from
if it helped in explaining the code to me since the fields are named
similarly. I don't expect anyone to know my database - how on earth could
they!

I tried the code you provided and I got an error - #Name? Also, how would I
include the other part of the primary key? The zip code and local agency
code make up the primary key of the zip code table which is the source of the
main form. txt_Zip_Code (on the main form) needs to match with P_Zip_Code
(on the subform, based on the query listed in the code below)

Here's the code I tried (without the zip code included):

=DCount("[Vendor_Nbr]","qry_Zip_Code_Vendors","[Status_Code] = 'Active' AND
[Peer_Group_Code] = 11 AND [Local_Agency_Code] = " & [txt_Local_Agency_Code])

Thanks for your help!

Bear in mind, you know the names of the fields in your table and their
datatypes, and the names of the control on your form - I don't.

The principle is that the third argument of the DCount() function
needs to be constructed as a text string which is a valid SQL WHERE
clause (without the word WHERE). If you can build a Query that gets
the values you want, you can look at its SQL view to find the syntax.

In short, you need quote marks (either ' or ") around the criterion
for any Text field. Date fields need # as a delimiter; number or
currency fields get no delimiters. You can assemble the WHERE string
from a mixture of text literals in quotes and references to form
controls; if the control is not on the current form, you need a
reference to the form containing it.

GUESSING here - again, I can't see your database:

"[Status_Code] = 'Active' AND [Peer_Group_Code] = 11 AND
[Local_Agency_Code] = '" & Parent![txt_Local_Agency_Code] & "' AND
[P_Zip_Code] = '" & Parent![txt_Zip_Code] & "'")

If the local agency code and the zipcode are included in the
Master/Child Link Fields then they should also be available on the
subform so you might not need the Parent! - however, you will need to
use a name which is local to that subform.

John W. Vinson[MVP]

 

[John Vinson]

John,

I didn't take your code example literally, I did plug in my field names in
your example. I appreciate your help and if my previous email implied that I
expected you to magically know my fields, then I apologize. I'm not sure how
you got that impression. I only gave info that I thought would be helpful -
I just wanted you to be able to distinguish where each field was coming from
if it helped in explaining the code to me since the fields are named
similarly. I don't expect anyone to know my database - how on earth could
they!

I apologize for the tone, Lori.

I was guessing at some of the features of your form (were the fields
on the subform or the mainform, for example?) and I overreacted.

Were you able to get it to work?

John W. Vinson[MVP]

 

[Lori]

Hi John,

I really appreciate your help - this problem is out of my league and skill
set. I couldn't get it to work but had another idea, but need some help with
it. If I could get a count of the records currently displayed on my
subform with the criteria listed below, I could set the value of my field on
my parent form equal the to calculated control on the subform that does the
count. Does that make sense?

The subform displays vendor records within each zip code record on
my form. I would like a count of all records displayed on my subform that
are active with a group = 11. The code below counts all records fitting the
criteria for all zip codes. I need it to apply itself to each zip code
record so it recalculates on the fly for each zip code record. I would
store the count on the subform in an unbound field. Any ideas how I can do
this or where to place the code?

=DCount("[Vendor_Nbr]","qry_Zip_Code_Vendors","[Status_Code] = 'Active' and
[Peer_Group_Code] =11 ")

Thanks! Lori