calculated field

[Guest]

I need to calculate the number of active vendors within a zip code area. Two
tables store this data - Zip Code and Vendor.

I have a form called Zip Code, the data source is a table where Zip Code and
LA# form the primary key. A subform on the form displays records from the
Vendor table showing all vendors within the zip code-LA# area. How do I
calculate the number of active vendors within this area? Basically, I need
the count and am not sure how to derive this on the form and the code to use.
I have gone to the field properties and am not sure what code to write.

Thanks!

 

[Wayne Morgan]

You could use a calculated textbox with the DCount() function to count the
records.

Example Control Source:
=DCount("*", "TableName", "ZipCode = '" & [txtZipCode] & "' And [LA#]=" &
[txtLA#])

The syntax above assumes ZipCode to be a text data type and LA# to be a
number data type.

 

[Guest]

Here is the code I tried:

=DCount("*","tbl_Vendor","P_Zip_Code = '" & [Zip_Code] & "' And
[Local_Agency_Code]=" & [Local_Agency_Code])

P_Zip_Code is the field in Vendor linked to Zip_Code in the Zip Code table.
Local Agency Code has the same name in both tables. Together the zip code
and local agency number form the key in the Zip Code table.

My field on the form, Current Vendors, has show Error? in it.

My criteria, which is not in this formula needs to count where Status =
Active. I didn't put this in because I couldn't get an unrestricted count to
work.

Do you know what I am doing wrong? Thanks...Lori

You could use a calculated textbox with the DCount() function to count the
records.

Example Control Source:
=DCount("*", "TableName", "ZipCode = '" & [txtZipCode] & "' And [LA#]=" &
[txtLA#])

The syntax above assumes ZipCode to be a text data type and LA# to be a
number data type.

--
Wayne Morgan
MS Access MVP

I need to calculate the number of active vendors within a zip code area.
Two
tables store this data - Zip Code and Vendor.

I have a form called Zip Code, the data source is a table where Zip Code
and
LA# form the primary key. A subform on the form displays records from the
Vendor table showing all vendors within the zip code-LA# area. How do I
calculate the number of active vendors within this area? Basically, I
need
the count and am not sure how to derive this on the form and the code to
use.
I have gone to the field properties and am not sure what code to write.

Thanks!

 

[Wayne Morgan]

[Zip_Code] and [Local_Agency_Code] need to be values that are available on
the same form that the textbox is in. It will take the current values from
there and use them in the DCount(). Also, the newsreader wraps the code
line, but it all needs to be on one line in the textbox's control source.

--
Wayne Morgan
MS Access MVP

Here is the code I tried:

=DCount("*","tbl_Vendor","P_Zip_Code = '" & [Zip_Code] & "' And
[Local_Agency_Code]=" & [Local_Agency_Code])

P_Zip_Code is the field in Vendor linked to Zip_Code in the Zip Code
table.
Local Agency Code has the same name in both tables. Together the zip code
and local agency number form the key in the Zip Code table.

My field on the form, Current Vendors, has show Error? in it.

My criteria, which is not in this formula needs to count where Status =
Active. I didn't put this in because I couldn't get an unrestricted count
to
work.

Do you know what I am doing wrong? Thanks...Lori

You could use a calculated textbox with the DCount() function to count
the
records.

Example Control Source:
=DCount("*", "TableName", "ZipCode = '" & [txtZipCode] & "' And [LA#]=" &
[txtLA#])

The syntax above assumes ZipCode to be a text data type and LA# to be a
number data type.

--
Wayne Morgan
MS Access MVP

I need to calculate the number of active vendors within a zip code area.
Two
tables store this data - Zip Code and Vendor.

I have a form called Zip Code, the data source is a table where Zip
Code
and
LA# form the primary key. A subform on the form displays records from
the
Vendor table showing all vendors within the zip code-LA# area. How do
I
calculate the number of active vendors within this area? Basically, I
need
the count and am not sure how to derive this on the form and the code
to
use.
I have gone to the field properties and am not sure what code to write.

Thanks!