Calculated Field

[BlaineR]

Hi,

I'm sure this is a very simple question, but as I've never done it before I don't know where to start. I have a tabular form that shows continuous records of basic information. To simplify, just say it's like this:



FirstName LastName City <HEADER>

Joe Smith New York
Mary Poppins Atlanta
Alex Zander Miami
Paul Oldman New York

CountNewYork <FOOTER>



I want to make CountNewYork a calculated field in the footer that simply counts the number of records where City = &quot;New York&quot;. In my example, that should be 2. The number just has to appear on the form -- no need for storing it in a table or anything like that.

Could you tell me how to do this...that is, what syntax to I use, and where do I put it? Feel free to explain it to me like I have ABSOLUTELY no idea how to begin!

Thanks!
B.

 

[natatbh]

Hi Blainer,

Create a listBox on your form and set the row source to the query that whould output the results you are looking for.

natatbh

 

[BlaineR]

Hi natatbh,

Thanks for the input. A few questions/issues, though:

1. Why does it need to be a list box? Why not just a regular text box?

2. I kinda hoped -- and thought -- that this could be done without using a &quot;query&quot;. Isn't there a way to do it with a calculation defined within the text box, rather than creating a separate query? (Part of my motivation here is that I want the user to be able to apply a filter (ad hoc, not pre-defined) and have the count accurately reflect the results. If I use a query, it will not dynamically reflect the filtered results.

Thanks again.

B.

 

[JerrySpringer]

hi

=Sum(iif([City]=&quot;NewYork&quot;,1,0))

This should work. Good Luck

Mosmas

 

[BlaineR]

Hi Mosmas,

Thanks for your suggestion. However, I tried it and the result is 0, even though there are records with &quot;New York&quot; on the form.

Any other ideas I can try?

Thanks,
B.

 

[BlaineR]

Ooooops. My apologies, Mosmas. Your suggestion actually did work. My mistake. Thanks very very much!

B.