Text Box Calculations - Totals

[marsh888]

Looking to create a text box that counts a field between to paramaters. I got this far but doesn't seem to be working

Select (store)
From (All Info)
Where store between waves "9" and "12"

There are a certain amount of stores in each wave - I am trying to count the number of stores.

Marsh

[ChipR]

You can set the data source on the text box control to something like:

=DCount("store", "All Info", "wave >= 9 and wave <= 12")

[ajalwaysus]

What ChipR is suggesting should work, but I have one question, is "Wave" a text or number field, because you are using it as a text field by wraping it in double quotes, but ChipR's code assumes that "Wave" is a number. Just keep that in mind when using ChipR's code.

-AJ

[marsh888]

It is a number, but does not work.
Table = All Info
Field = Store (Number of stores that needed to be Counted)
Field = Wave (Between "9" and "12")
Only want the total number of stores between ( 9 and 12

[ajalwaysus]

Well try Chip's code, since his response will work for your needs, he went through the effort of writing it up for you.

Also, if you want something for a query, here is how your query should look:

Select Count(*) as Count_Of_Stores
From [All Info]
Where [All Info].waves between 9 and 12

Both codes should work, the key is you need to drop the double quotes as you have already indicated that Waves is a number not text.

-AJ

[marsh888]

Chip - Your code worked.

Thanks for the help

[marsh888]

What if the fields are Date fields ?

[ajalwaysus]

What if the fields are Date fields ?

Where MyDate Between #1/1/2009# and #12/31/2009#

or

Where MyDate >= #1/1/2009# and MyDate <= #12/31/2009#

Take the Where out if you put it in the DCount function ChipR provided.

-AJ

[NeoPa]

Reading through this is a bit like watching sand :S Having said that, Quotes (') and Double-Quotes (") - Where and When to use them & Literal DateTimes and Their Delimiters (#) may be of some use.

[marsh888]

I am trying to use the dcount like chip suggested. It worked when counting number of fields with values. However, I am trying to count total number of date fields that are not blank. Therefore, trying to manipulate the following line:

=Dcount("1st Review","All Info", "wave >= 9 and wave <= 12")

Thanks,

marsh

[NeoPa]

Please notice the Edit Comments I keep having to add to your posts. I'd rather not issue you with a site warning for something so easy to resolve.

PS. Tags (generally) are done as matching pairs where the opening one is surrounded by [...] and the closing one by [/...]. A set of buttons is available for ease of use in the Standard Editor (Not the Basic Editor). The one for the [ CODE ] tags has a hash (#) on it. You can choose which editor to use in your Profile Options (Look near the bottom of the page). You must select the code in your post, and hit the button on the tool bar which looks like a hash (#). This will automatically format the post such that the [ CODE ] tags surround the code you're posting. This will display the code in a Code Box, quite differently from the standard formatting of a post.

[NeoPa]

Assuming you want what you've requested, then try :

=DCount('[Wave]','[All Info]')