IF Statement

[tmulrooney]

I have a point group on my form which stores a value into my table. It is setup to store 1,2,3,4,5, or 6. On My report I would like to show the results. BUT, instead of showing the number, I would like for "1" to show "Active" and "2" as "Not Active" and so on. Can I write an IF Statement? If value for <field>=1, then "Active"?

Thanks!

 

[Tiro Cupidus]

The syntax of an If function in Access is like this: IIf(condition,if true,if false). So the Control Source of the textbox should be

=IIf([field]=1,"Active",IIf([field]=2,"Not Active",IIf([field]=3, ......)))

With something like this, though, I would use a Select Case. In a module, you would write a function like this:

fSomeFunction(field as Integer)
  Select Case field
    Case 1
      fSomeFunction = "Active"
    Case 2
      fSomeFunction = "Not Active"
    etc...
  End Select
End Function

Then in your Control Source you would have

=fSomeFunction([field])

 

[Uncle Gizmo]

Another alternative would be to make a new table named "tblPointGroup" for example.
Have an id field "pointGrpID" with values 1, 2,3,4,5,6 and a description field "pointGrpDesc" with Active, Not Active and so on.

Now make a new query "qryRptListPointGrp" where you link your table with the new table on pintGrpID then base your report on that.

 

[tmulrooney]

I like the case function but I am new to modules. I have not been able to get it working.

Is this the exact code for the module?

fSomeFunction(field as Integer)
Select Case field
Case 1
fSomeFunction = "Active"
Case 2
fSomeFunction = "Not Active"
etc...
End Select
End Function

I edited it to fit my fields. Is this correct?

fsomefunction(status as integer)
select Case status
Case 1
fsomefunction = "Active"
etc.
End Select
End Function

I saved the module as fsomefunction

In my control source, I put: =fsomefunction ([status])

Am I missing something? My field name is "status"

 

[Tiro Cupidus]

I think it's because you named the module the same thing as the function you're referring to. Right now when you try to view the report, does it pop up and ask you for the value of fsomefunction? Try renaming the module to something like "functions" and see if that works. And by the way, you can name the function anything you want, I was just using that as an example.

edit: Also, Tony's idea is something to consider. Basically, you're already storing ID numbers into the table. All you'd have to do is have a related table with the appropriate names for those ID numbers.

 

[Tiro Cupidus]

Oh, also you can have more compact code by writing it like this:

Case 1: fSomeFunction = "Active"
Case 2: fSomeFunction = "Not Active"

 

[Dragon]

If I may continue a line of thought…

Currently I have a field in a report that has this as the Control Source:

=IIf([Control_Source_A]<>"","Show some text,"")

I want to look to see if Control_Source_B has some text. If Control_Source_B has text, then use the above code, if it doesn’t have text, use some other code.

In other words, a type of nested if statement.

Thanks.