COUNTing records matching values in another column

[ErikRP]

(I posted this yesterday but my computer sneezed in the middle of composing my posting and I don't see it anywhere. I apologize in advance if I'm re-posting.)

I have a table with 3 fields: CITY, DATE and LOGIN_ID. I want to COUNT the number of LOGIN_ID's where CITY = "London" and the DATE is between December 1, 2000 and March 1, 2001.

I want to be able to put an unbound control box on the form and have the record source grab the information from the table.

This syntax is obviously wrong, but I'm thinking that I want something like this:

=COUNT(([LOGIN_ID]) WHERE [CITY]="London" AND ([DATE]>01/12/00 AND [DATE]<01/03/01))

If at all possible I don't want to have to make a query for this as I will need to have several (at least 30 or so) similar control boxes to reflect different cities and different dates.

Any suggestions are most welcome! Thanks!

 

[pdx_man]

Check out the DCOUNT function:

=DCOUNT("LOGIN_ID","A_Table","CITY = 'LONDON' AND DATE Between #12/01/00# And #03/01/01#")

I'd change my fieldname DATE to something else as it is a reserved keyword for grabbing the system date.

 

[ErikRP]

Wonderful! Thanks pdx_man! That worked perfectly, thanks for your suggestion.

Happy Holidays!