Syntax for module with case statement using field name as variable

I want to write a generic module that I can reference in calculated fields in my queries that will help me segment field values from a table.
In pseudo code it would be like:
if ([field] >= 0 and [field] < 1)
then ("0.000 - 1.000")
else if ([field] >= 1 and [field] < 2)
then ("1.000 to 1.999")
etc...
I could apply this module in any query by creating a calculated field and referencing the module.
However, in looking through the help files I cannot figure out the syntax for this.  
sarieloveAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

hlarseCommented:
0
sarieloveAuthor Commented:
I don't think I want to display a message box - I want the result of the case statement to be the value of the calculated field for that record.  The point would be that then when I create a report from that query, I could use my calculated field to group the values.  
0
hlarseCommented:
Right, something like this?  

Select Case field

case 0 to 1
Group = 1

Case 1 to 2
Group = 2

Case Else
Group = 0

Or you could return a text depending on what your value is?
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

hlarseCommented:
Select Case field

case 0 to 1
Group = 1

Case 1 to 2
Group = 2

Case Else
Group = 0

End Select

(forgot to end)
0
sarieloveAuthor Commented:
Yes... so what is the correct syntax for referencing the field I want it to look at, and also the syntax for making the value of my calculated field a text value, not a numberic value?
0
SmilingPixieCommented:
I think this is the kind of thing you're looking for

Public Function CALC(FIELD)
If FIELD >= 0 And FIELD < 1 Then
    CALC = "0.0 - 1.0"
ElseIf FIELD >= 1 And FIELD < 2 Then
    CALC = "1.0 - 1.9"
Else
    CALC = ">2"
End If
End Function
0
sarieloveAuthor Commented:
This sounds right but I am such a beginner with VB code that I really don't even know what to put in place of (field).  What I am trying to get to is, rather than a field name, how do I put a place holder there to tell the function to look at whatever field I am referencing in my query calculation? I need exact syntax, unfortunately, because nothing I have tried works.
Thanks!
 
0
SmilingPixieCommented:
If you are able to create the public function...in your query grid you should be able to create a column that uses the function to populate it.  Once the result is in your query - you should be able to group on it for a report.  

For example:  In the FIEILD ROW OF YOUR QUERY

OUTPUT:CALC([Tablename]![ColumnName])

Where OUTPUT - is the column name you want to appear when the query runs, it's also what you would group by
CALC is the name of the function you created

[Tablename]![ColumnName] is how you specify what value to pass into the function.
0
sarieloveAuthor Commented:
So given that,  in the module I am writing, what is the syntax to tell it which field it should be using as the variable in the case statement? When you are writing a case statement there has to be some variable and I don't know how make a variable in the module that will be whichever field my calculated field in the query applies to.
0
SmilingPixieCommented:
If you put the function call in the query grid as the source - the query should automatically call the function passing in the table.column. The output from the function will be directed to the column in your query grid where you put the function, you don't need to specifically name the column for the output.

Otherwise it would be variable = calc(table.column) and this should call the function and return the result to the variable.

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
sarieloveAuthor Commented:
Thanks for your help!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.