# If statement in query

##### New Member
I have one field that counts the number of a particular item. It is working great but a couple of the items have 6 in them and should be counted as one. For example:
Actual (current outcome right now in the query)
Field name=item
Kleenex 400
Cigarettes 50
Coffee maker 1
I wanted to create a If statement in the query if possible to say : If item is Kleenex divide by 200 and if Cigarettes divide by 25 and if anything else without stated if statement then leave it as is.
What I want it to show is:
Kleenex 2
Cigarettes 2
Coffee maker 1

### Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

#### Joe4

An IF statment would work fine if you only have a few products where you need to do this division by, but if you have a lot, it won't be feasible to use an IF statement.

A better solution may be to create another table that lists how many items are in one box/package.

Then link these two tables together in a query (on the product name), and divide the two numbers.

#### Phildaburn

##### Board Regular
Actual: iif([item]="Kleenex",[item_quatity_field_name]/200,iif([item]="Cigarettes",[item_quantity_field_name]/50,[item_quantity_field_name]))
Joe's solution is a better one, though, but this is what you were asking for.

Phil...

##### New Member

Sorry for the delay. I am stuck in a meeting.

Best regards!

##### New Member

My actual field name is "Kit Type" does the space cause problems. The query is a group by query with "kit type" with count. Can I place this in the query?
iif([item]="Kleenex",[item_quatity_field_name]/200,iif([item]="Cigarettes",[item_quantity_field_name]/50,[item_quantity_field_name]))

<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
Thanks

<TABLE border=0 cellSpacing=0 cellPadding=6 width="100%"><TBODY><TR><TD style="BORDER-BOTTOM: 1px inset; BORDER-LEFT: 1px inset; BORDER-TOP: 1px inset; BORDER-RIGHT: 1px inset" class=alt2>Actual: iif([item]="Kleenex",[item_quatity_field_name]/200,iif([item]="Cigarettes",[item_quantity_field_name]/50,[item_quantity_field_name])) </TD></TR></TBODY></TABLE>

#### Phildaburn

##### Board Regular
Spaces in field names can cause problems. Using an underscore is better than a space and still gives you a division between words. So, "Kit_Type" or even "KitType" is better than "Kit Type". You can place it in your query within the square brackets though. Also, I noticed that what I wrote for cigarettes should have divided them by 25 rather than 50. Sorry about that.

Phil...

#### Joe4

Spaces are not a problem if you use square brackets around the field name, though general programming practice is to avoid using spaces in object and field names (its just cleaner that way).

Also note that if one of your queries is an Aggregate Query, your field name may not be correct (if you are doing any Aggregate function on any field, the field name may be something like "SumOf..." or "CountOf...").

The best thing to do that would solve all these issues with field names is rather than copying the formula that is written and trying to edit it, try building it through the Expression Builder, selecting the fields from the appropriate sources. The Expression Builder will make sure all the field names are denoted properly.

Replies
5
Views
389
Replies
1
Views
159
Replies
4
Views
1K
Replies
1
Views
178
Replies
3
Views
2K

1,127,098
Messages
5,622,679
Members
415,921
Latest member
ExcelNoob28

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back