Count all the 9’s in field1

TBC · Dec 22, 2010

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))[/code[/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3] [/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3]Thanks for taking the time to help me learn  TCB [/SIZE][/FONT][FONT=Times New Roman][SIZE=3] [/SIZE][/FONT]:confused:

boblarson · Dec 22, 2010

1. Why hard code the year? You can make this reusable by using dateserial with Year(Date()) as the year part. Then you can use this next year too.

2. So the SQL would be something like this:

Select Count([Field1]) As JanApps Where [Field1]= 9 And [Expiration_date] Between DateSerial(Year(Date()), 1, 1) And DateSerial(Year(Date()), 1, 31)

vbaInet · Dec 22, 2010

I think what the OP is trying to do is count all the 9s, 8s and 7s when field is simply 2010, Feb 2010 and March 2010.

So he/she needs to set criteria to those months/year by putting criteria under the date field to this:

Code:

Format([DateField], "mmyyyy") In ('012010', '022010', '032010')

But like Bob mentioned, you don't need to hard code the criteria so why not create a form that will have combo boxes from which you can set the three months? If you go with this idea, then this is what the criteria under the date field will then look like:

Code:

Format([DateField], "mmyyyy") = [Forms]![FormName]![Combobox1] &  Year(Date) Or Format([DateField], "mmyyyy") =  [Forms]![FormName]![Combobox2] & Year(Date) Or Format([DateField],  "mmyyyy") = [Forms]![FormName]![Combobox3] & Year(Date)

You can further set criteria to only pull in the 7s to 9s by putting this under the field's criteria:

Code:

In (7,8,9)

For the Counts you do this (taking 9s as an example):

Code:

Count9s: Count(IIF([Field] = 9, 1, Null))

TBC · Dec 22, 2010

I’m getting a Data type mismatch in criteria expression

I’m not sure what causes this type of error, could you please help me understand why I’m getting this error and what I need to do to fix it?

Thank you again for your time and help

Code:

SELECT Pull_Through_1.orig_code, Pull_Through_1.orig_name, Count(IIf([Expiration_date] Between #1/1/2010# And #1/31/2010# And [OMNI_Status]=9,1,0)) AS JanApps[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]FROM Pull_Through_1[/FONT][/SIZE]
[FONT=Times New Roman][SIZE=3]GROUP BY Pull_Through_1.orig_code, Pull_Through_1.orig_name;

boblarson · Dec 22, 2010

TBC said:
I’m getting a Data type mismatch in criteria expression

I’m not sure what causes this type of error, could you please help me understand why I’m getting this error and what I need to do to fix it?

All of the code you posted looks NOTHING like what I posted. What's the deal?

TBC · Dec 22, 2010

Code:

Select Count([OMNI_Status]) As JanApps 
from Pull_Through_1
Where [OMNI_Status]= 9 And [Expiration_date] Between DateSerial(Year(Date()), 1, 1) And DateSerial(Year(Date()), 1, 31)

boblarson said:
[/size][/font]
All of the code you posted looks NOTHING like what I posted. What's the deal?

boblarson · Dec 22, 2010

TBC said:

Code:

Select Count([OMNI_Status]) As JanApps 
from Pull_Through_1
Where [OMNI_Status]= 9 And [Expiration_date] Between DateSerial(Year(Date()), 1, 1) And DateSerial(Year(Date()), 1, 31)

Okay, so are you getting the type mismatch there? If so, is OMNI_Status numeric or text? And if numeric does it have a lookup defined at table level?

TBC · Dec 22, 2010

OMNI_Status is text, no lookup

boblarson · Dec 22, 2010

If OMNI_STATUS is text then you need quotes around the 9:

Code:

Select Count([OMNI_Status]) As JanApps 
from Pull_Through_1
Where [OMNI_Status]= [COLOR=red][B]'[/B][/COLOR]9[B][COLOR=red]'[/COLOR][/B] And [Expiration_date] Between DateSerial(Year(Date()), 1, 1) And DateSerial(Year(Date()), 1, 31)

Count all the 9’s in field1

TBC

Registered User.

boblarson

Smeghead

vbaInet

AWF VIP

TBC

Registered User.

boblarson

Smeghead

TBC

Registered User.

boblarson

Smeghead

TBC

Registered User.

boblarson

Smeghead

Similar threads

Users who are viewing this thread