Count Function in Query design view

[GATJA]

I have a query where I want to see a percentage of executed demos. I currently have Name of the promo (group by), # of demos (group by), Status (criteria = E for executed demos, count) and Percentage: Status/# of demos. Everytime I run this query I get this message: "Data type mismatch in criteria expression." The only data in Status is either a O for open or an E for executed. The E in Status is the only criteria I have in the whole query. If I take the count function off Status, it runs, but does not give the right results. Does anyone know what I am doing wrong?

 

[selenau837]

It will return a value in your calculated control if you take off the count fuction?

 

[GATJA]

No, it still gives me the same error, even without the Count function. When I take out my calculated field and the count funtion it gives me a Status of E, but the E won't help me when I try to calculate a percentage. When I add the count function back in (but not my calculated field), it comes back with the same data type mismatch error.

 

[GATJA]

Here is my SQL view, if that helps.

SELECT [Promo count].PromoNo, [Promo count].[# of Demos], Count(DemoOrder.Status) AS CountOfStatus, [DemoOrder]![Status]/[Promo count]![# of Demos] AS Percentage
FROM [Promo count] INNER JOIN DemoOrder ON [Promo count].PromoNo = DemoOrder.PromoNo
GROUP BY [Promo count].PromoNo, [Promo count].[# of Demos], [DemoOrder]![Status]/[Promo count]![# of Demos]
HAVING (((Count(DemoOrder.Status))="E"));

 

[selenau837]

No, it still gives me the same error, even without the Count function. When I take out my calculated field and the count funtion it gives me a Status of E, but the E won't help me when I try to calculate a percentage. When I add the count function back in (but not my calculated field), it comes back with the same data type mismatch error.

Hmmm, can you post a copy of your db? It will help some.

 

[GATJA]

No, I cannot do that due to company policy.

 

[selenau837]

I am a visual person, I have to see the code run inorder to determin what is wrong.

I would start a new Query, pull only the table necessary to get the count. See if it will ever give you a count with the criteria set to E. If it does, then slowly add in the other fields. THat should help determin which field is breaking it.

I am sorry I can't help further, other than advice on debugging. Hopefully one of the others will meander along soon and help out.

 

[GATJA]

I already tried a new query. It won't even give me a count with just Status and criteria set to E.

 

[selenau837]

I am stumped then as to why it will not count up the number of E's you have. Perhaps because it is a string datatype.

Sorry darlin', For I am unable to help further.

 

[GATJA]

Its ok, thanks for you help.

 

[gromit]

I suspect that the
HAVING (((Count(DemoOrder.Status))="E"));

is part of the problem.

Seems like this should just be DemoOrder.Status = "E".

(This is part of the selection criteria, rather than the results returned.)

 

[selenau837]

One last shot, have the status field in the query twice. One normal and one as count and see what that gives you.

 

[GATJA]

Selena - I tried the 2 Status fields and it still gives me the data type mismatch error.

Gromit - Which part of the SQL statement? I changed the bold part to just DemoOrder.Status = "E", but it didn't like that at all. I have no clue what I am doing really, so try to stay with me.

SELECT [Promo count].PromoNo, [Promo count].[# of Demos], Count(DemoOrder.Status) AS CountOfStatus, [DemoOrder]![Status]/[Promo count]![# of Demos] AS Percentage
FROM [Promo count] INNER JOIN DemoOrder ON [Promo count].PromoNo = DemoOrder.PromoNo
GROUP BY [Promo count].PromoNo, [Promo count].[# of Demos], [DemoOrder]![Status]/[Promo count]![# of Demos]
HAVING (((Count(DemoOrder.Status))="E"));

 

[gromit]

Yeah, the bold part is what I was suggesting that you modify.

Can you provide the relevant fields of each of the involved tables?

e.g.
Promo Count
---------------
Promo No Number
# of Demos Number
Status String

etc?

I'll take another look and see if I can figure out what is up.

- g

 

[selenau837]

Once resolved, I would like to know why it has it as 'HAVING' instead of a 'Where' Clause.

*steps back and waits for Gromit*

 

[GATJA]

Promo Count is a Query
---------------
PromoNo
# of Demos

DemoOrder is a table
--------------
Status
PromoNo (linked by this)
(plus many other fields I am not using)

Is this what you meant?

 

[GATJA]

I am trying to get the count of E's so I can divide the number of demos by the count of E's to get the percentage of executed demos. You have probably already figured this out by now though, heh.

 

[gromit]

GATJA -

I have just tried duplicating the SQL syntax using some mocked up tables. It seems to work okay (although I doubt my table syntax is exactly the same).

Take a look at the attached and see what the difference might be.

Selena - I'm not fully conversant with the distinctions between WHERE and HAVING. My reference book (Building Microsoft Accesss Applications by J Viescas) states that "WHERE applies to single rows before they are grouped and HAVING applies to groups of rows".

- g

 

[GATJA]

I populated the fields with mock data PromoNo = 1 thru 10, # of Demos each have 10, PromoNo 1 and 5 were O, the rest E. Then I ran the query and it gave the same data type mismatch error.

 

[selenau837]

Selena - I'm not fully conversant with the distinctions between WHERE and HAVING. My reference book (Building Microsoft Accesss Applications by J Viescas) states that "WHERE applies to single rows before they are grouped and HAVING applies to groups of rows".

- g

Thanks darlin'!