Access Count function Question 1

[Cordury2]

I apologize in advance for the lame question.

I have one table where I want to count the number of transactions over certain amounts (25, 50, 75).

I have a query that is counting the TOTAL number of Transactions but when I enter ">25" in the criteria box, I receive a "Data Type Mismatch in Criteria Expression" error.

Is this something that has to be done through SQL?

 

[TheAceMan1]

how are ya Cordury2 . . .

Remove both double quotes:

[blue]    [red][b]"[/b][/red]>25[red][b]"[/b][/red][/blue]

See Ya! . . . . . .

 

[Cordury2]

Thank you for the response TheAceMan1. I tried that and it returns the total number of transactions in the table not matter what amount I type after the greater than symbol. I checked the table and that field is a number (not sure if that would matter though).

 

[randy700]

Under which column are you putting your criteria?


Randy

 

[Cordury2]

Hi Randy,
The dollar amount is in the Sales column and that is where I am placing the criteria.

 

[Cordury2]

Here is the SQL

SELECT ByTran.CDay, Count(ByTran.Sales) AS CountOfSales
FROM ByTran
GROUP BY ByTran.CDay
HAVING (((Count(ByTran.Sales))>50));

 

[randy700]

Something like...

SELECT ByTran.CDay, Count(ByTran.Sales) AS CountOfSales
FROM ByTran
GROUP BY ByTran.CDay
[b][COLOR=red]WHERE ByTran.Sales > 25[/color][/b]

Randy

 

[PHV]

You wanted this ?
SELECT CDay, Count(Sales) AS CountOfSales
FROM ByTran
WHERE Sales>50
GROUP BY CDay

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Cordury2]

Syntax Error (missing operator) in query expression 'ByTran.CDay WHERE By.Tran.Sales > 25'

 

[Cordury2]

Now if I do this:

SELECT ByTran.CDay, ByTran.Sales
FROM ByTran
GROUP BY ByTran.CDay, ByTran.Sales
HAVING (((ByTran.Sales)>"50"));

it returns all records over 50 which I could throw into Excel and a Pivot table but I am looking to get it all through Access.

 

[randy700]

Yours

WHERE By.Tran.Sales > 25

Corrected

WHERE ByTran.Sales > 25

Randy

 

[Cordury2]

Randy,
First off, thank you for your patience.

But the SQL is

SELECT ByTran.CDay, Count(ByTran.Sales) AS CountOfSales
FROM ByTran
GROUP BY ByTran.CDay
WHERE ByTran.Sales > 25

and I am stillg getting the same error.

 

[PHV]

Seems you haven't tried my suggestion ...

 

[Cordury2]

PHV

SELECT bytran.CDay, Count(bytran.Sales) AS CountOfSales
FROM ByTran
WHERE bytran.Sales>50
GROUP BY bytran.CDay

I am sorry, I did try your suggestion and the following error occured:
"Data type mismatch criteria in expression

 

[PHV]

What the data type of ByTran.Sales ?

 

[randy700]

PHV gave you a corrected version of my suggestion. USE HIS. My WHERE and GROUP BY clauses are reversed and won't work.

What is the data type of your Sales field?

In your 14:13 post, you say

Syntax Error (missing operator) in query expression 'ByTran.CDay WHERE By.Tran.Sales > 25'

Look at the section in red. You have an extra DOT.

Randy

 

[Cordury2]

It was Number but I changed it to Text. Now when I try and change it back to Number and save I get a Not enough disk space or memory error so it is stuck at text.

I am a nightmare, I know.

 

[randy700]

For text you need quotes around the criteria. However, you really need to change it back to a number data type. Have you tried doing a Compact and Repair?


Randy

 

[Cordury2]

The Compact and Repair won't work either. I am working on getting the original table.

 

[Cordury2]

I got the original table and PV's code seems to be working.

Again, thank you both so much for your time and patience as well as expertise.