sarielove
asked on
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.
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.
ASKER
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.
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?
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?
Select Case field
case 0 to 1
Group = 1
Case 1 to 2
Group = 2
Case Else
Group = 0
End Select
(forgot to end)
case 0 to 1
Group = 1
Case 1 to 2
Group = 2
Case Else
Group = 0
End Select
(forgot to end)
ASKER
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?
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
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
ASKER
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!
Thanks!
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]![C
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.
For example: In the FIEILD ROW OF YOUR QUERY
OUTPUT:CALC([Tablename]![C
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.
ASKER
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks for your help!
http://support.microsoft.com/kb/141691