IIF and InStr

[MonsterCR7]

Hi All,
I am in query mode and I am trying to create a new column that will show an IIF statement to find certain words in the item number to return a result.
For example, If the item number contains the words “SER” then multiply the quantity times 20 to give a result of the cost of the labor hours.
The expression itself is working correctly, but it will only allow me to count values or records for the field.
How do I get it so that I can do a sum of the total cost of the labor hours?

 

[Joe4]

Take a look at creating Aggregate (Totals) Queries, in Access' built-in Help Files (or through a Google Search).
You will find lots of information, tutorials, and examples on how to do a Totals Query.
This one looks like it could be similar to your question: Create Queries with Calculated Fields and Aggregate Functions - YouTube

 

[MonsterCR7]

Didn't help. Still need to solve how to make it SUM rather than COUNT

 

[xenou]

It should work. Can you post your SQL text?

Also, for what its worth, an easy solution that I also will implement is to:

1. write the query with the IIF statement in it (don't use any aggregate functions here)
2. write a query on the query you just wrote - this is where you sum up values using SUM().

 

[Joe4]

SUM rather than COUNT

Note that there are a variety of Aggregate Functions to choose from. COUNT is just one of them. SUM is another option (as well as others).