Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Count all the 6’s in field1

Status
Not open for further replies.

TJVFree

Technical User
Nov 22, 2010
236
US
I’m working on a query that will count all the 9,8,7 that fall between the expiration date

Count all the 9’s in field1
Count all the 8’s in field1
Count all the 7’s in field1
Count all the 6’s in field1

That fall between #01/01/10# and #01/31/10# “Jan”
That fall between #02/01/10# and #02/31/10# “Feb”
That fall between #03/01/10# and #03/31/10# “March”

I think it would look something like this, but I cant get it to work:
Code:
JanApps: Sum(IIf([Expiration_date] Between #1/1/2010# And #1/31/2010#,Count[field1]=9,0))

So it would look something like this
Jan/Feb/Mar
5 / 8/ 10
Thanks for taking the time to help me learn
 


hi,

How about...
Code:
TRANSFORM count(*)

Select Field1

From YourTable

Group by Field1

Pivot FORMAT([Expiration_date],'yyyymm')


Skip,
[sub]
[glasses]Just traded in my old subtlety...
for a NUANCE![tongue][/sub]
 
We solved this problem for the OP over on Access World Forums (where they posted a sample database). It turns out that Field1 was text and the entries were not consistent. There were some with spaces before the number and so using TRIM to compare worked.

You can see the entire thread here:

Bob Larson
FORMER Microsoft Access MVP (2008-2009, 2009-2010)
Free Tutorials/Samples/Tools:
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top