Count all the 6’s in field1

[TJVFree]

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:

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

 

[SkipVought]

hi,

How about...

TRANSFORM count(*)

Select Field1

From YourTable

Group by Field1

Pivot FORMAT([Expiration_date],'yyyymm')

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[dhookom]

Try:

JanApps: Sum(Abs([Expiration_date] Between #1/1/2010# And #1/31/2010# AND [field1]=9))

Duane
Hook'D on Access
MS Access MVP

 

[boblarson]

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:

http://www.access-programmers.co.uk/forums/showthread.php?t=203021

Bob Larson
FORMER Microsoft Access MVP (2008-2009, 2009-2010)
Free Tutorials/Samples/Tools:

http://www.btabdevelopment.com