Help Writing VBA and call to query

[brianb]

I'm not a VBA expert, but I'm looking for some help to write VBA code and call that code to a query. I've done it once before a while back with some basic code but can't seem to remember how.



What I'm wanting the code to do is basically replace a long IIF statement I would make in a query. So what I have is a list of about 35 items that I want to put into about 7 different groups.

For example a couple of the items are: "Approval", "Approved", which I would want to make a group called Approved, and anything in my data that says "Approval" or "Approved" would be in that group. Some of the groups would only have 2 likes like just shown and some may have as many a 10 things. Can someone assist me w/this? Thanks.

[RuralGuy]

You may want to look at a Select Case structure.

[brianb]

Ok, is there a sample I can look at or can someone assist in writing it?

[ajetrumpet]

the other problem with your post is the fact that you only said what you wanted grouped, but so what? A query displays data in a structure that you specify. Grouping records by groups based on criteria doesn't mean anything unless you specify how you want the output to look.

for instance, I'm guessing that this:

approved
approved
approved
approval
approval
approval
approval
approval

would need to look something like this for you in an output:

TYPE OF RECORD	COUNT
"approved"	8

But see, the grouping is the first part of this. What good is a grouping if you don't know what to do with it? Or what you want done with it?

[brianb]

I want the output to show just the Group, I don't need the count of how many. So if I were to put it in an IIF statement it would look like this. IIF([table].[field] = "Approval", "Approval", IIF([table].[field] = "Approved", "Approval", ...)) were ... is just more IIF statements for other groups.

[ajetrumpet]

show the board a sample dataset of 5 records, as you would expect it to look.

[brianb]

Sorry for the late repsonse, but below is a table of how I'd want this

[brianb]

Approval Received
Approvals File Received
Approved File Setup
Approval New File
File Review





Sorry couldn't figure out how to copy the actualy table as you have above.