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?

[Webinar] Streamline your web hosting managementRegister Today

x
 
SmilingPixieConnect With a Mentor Commented:
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
 
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
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
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
 
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
 
sarieloveAuthor Commented:
Thanks for your help!
0
All Courses

From novice to tech pro — start learning today.