can you use a case statement behind a report field?

[fxplorr]

I'm trying to clean up an Access program designed by someone else and I'm having some difficulty (regarding syntax) creating a condition for a field on a report.

I have the following case statement created and I wanted to be able to apply this logic to the field. It had been a nested IIf, but the previous guy's logic didn't work correctly. I tried putting this case in the "On Open" event of the report but it keeps telling me there's no value in [txtUNIT] so I'm assuming that the statement is running before the data is loaded on to the form, because I know there are values in that field. They display on the report...and it really didn't like it when I put it in the "Control Source" of the field, because I don't know the syntax or maybe it's just not possible.

Select Case [txtUNIT]
Case "ASST"
Report_CATALOG.PACK = ([PCS PER PACK] & " Asst"),
Case "BOX"
If([UsePCSPerPack] = -1 Then
(Report_CATALOG.PACK = ([PCS PER PACK] & " Pcs")
Else
(Report_CATALOG.PACK = ([PCS PER PACK] & " Box")
End If
Case "ROLL"
Report_CATALOG.PACK = ([PCS PER PACK] & " Roll(s)")
Case "CASE"
Report_CATALOG.PACK = ([PCS PER PACK] & " Case")
Case "CARD"
Report_CATALOG.PACK = ([PCS PER PACK] & " Card(s)")
Case "LBS"
Report_CATALOG_PDF.PACK = ([PCS PER PACK] & " lbs")
Case Else
Report_CATALOG.PACK = ([PCS PER PACK] & " Pc(s)")
End Select
--------------
Any help would be greatly appreciated.
Thanks
Justin

 

[Golom]

Why it's not working in "On Open" event I'm not sure but you do have some syntax problems.

Dim st As String
Select Case [txtUNIT]
    Case "ASST":            st = "Asst"
    Case "BOX":             st = IIf([UsePCSPerPack] = -1, "Pcs", "Box")
    Case "ROLL":            st = "Roll(s)"
    Case "CASE":            st = "Case"
    Case "CARD":            st = "Card(s)"
    Case "LBS":             st = "lbs"
    Case Else:              st = "Pc(s)"
End Select
Report_CATALOG.PACK = [PCS PER PACK] & " " & st

 

[AlexCuse]

golom's syntax looks good.

Try moving your event to the 'OnLoad' rather than 'OnOpen' spot and updating your syntax and it should run fine.

HOpe this helps,

Alex

A wise man once said
"The only thing normal about database guys is their tables".

 

[fxplorr]

I eventually got it figured out. I had to create one condition in the query the report used and one condition in the control source of the field.

AlexCuse - OnLoad wasn't an event option for the report. It's there for a form, but not a report....at least on this one.

Thanks for your help! (Golom, AlexCuse)