=IIF or VBA code?

[krberube]

I've seen several threads on using =IIF in a report field. I am wondering if VBA would be a better way to do the following?

report with a field evaluating a 2nd field. In my control source for the 1st field:
=IIf([CFSN] Like "EG1*","1 GB",IIf([CFSN] Like "FN1*","2 GB",IIf([CFSN] Like "RQ1*","4 GB")))

This has now expanded to acouple of more options and IF no eveluation is true then show the actual data in the 2nd field.

Would a Case select module be better for this?

Thanks
Kevin

 

[KenHigg]

I would do it in a function simply because you may need it somewhere else, say in a report...

 

[krberube]

I believe I found a post by Pat Hartman that answered my question - yes it would be better to do this in a module.

Now a quick question, I have 10 fields in my report using the IIF function. Can I do 1 module with 10 different case statement sections, and call this module in my 10 fields in the report? This might make it easier to maintain as well?

Thanks

 

[KenHigg]

Are all ten fields dependant on the value [CFSN]?

 

[krberube]

no, each of the 10 has their own "source".

I was thinking of a mudule kinda like this, 1 select case for each of the 10 fields.

Function
select case [CFSN]
Case IS......
Case IS......
Case Else
End case

Select case [field2]
Case IS.....
Case IS.....
Case Else
End Case

End Function

 

[KenHigg]

I'd bite the bullet and do a seprate function for each...

 

[krberube]

I guess the bullet isn't really that bad. If I do the same evaluation for these fields in other reports, I can then call the functions as needed.

Thanks Ken

 

[KenHigg]

One last question - How stable are values you have in CFSN, etc. If you ever need to add/change any of these you may want to put them in a table with their related values...

 

[krberube]

The fields are pretty stable, we won't add to these to often.
I started to create the module for the CFSN. I need a bit of help on the CASE ELSE line if you don't mind. IF no matching case then show the value of CFSN in the CFModel field.

Function CFModel()

Select Case CFModel
Case Is = "EG1*"
CFModel = "1 GB"
Case Is = "FN1*"
CFModel = "2 GB"
Case Is = "RQ1*"
CFModel = "4 GB"
Case Else
CFModel = [CFSN] 'if no matching case display the actual CFSN value
End Select

End Function

EDIT:
What i have above doesn't seem to match my cases at all, i get no values in my CFModel field at all. is there a way to do a CASE LIKE "FN1*" statement?

 

[KenHigg]

Function CFModel(myString as string) as string

Select Case myString
    Case like "EG1*"
        CFModel = "1 GB"
    Case like "FN1*"
        CFModel = "2 GB"
    Case Like "RQ1*"
        CFModel = "4 GB"
    Case Else
        CFModel = myString
End Select

End Function

I think this is correct...

 

[krberube]

i thought that also, but i doesn't like the "like". what i just found in another post fixed all:

Function CFModel()

Select Case CFSN
Case Left(CFSN, 3) = "EG1"
CFModel = "1 GB"
Case Left(CFSN, 3) = "FN1"
CFModel = "2 GB"
Case Left(CFSN, 3) = "RQ1"
CFModel = "4 GB"
Case Else
CFModel = CFSN
End Select

EDIT: I take that back, it reports every record as 1 GB. HMMMMM
End Function

Thanks for all your time!!!
Kevin

 

[KenHigg]

Hum... Something is still amuck - but if it works

 

[krberube]

Its been a long day... I get a syntax error on the CASE LIKE lines.

EDIT: from the Access help file "Note that Is and Like can't be used as comparison operators in a Select Case statement."

Function CFModel(CFSN As String) As String

Select Case CFSN
Case like "EG1*"
CFModel = "1 GB"
Case like "FN1*"
CFModel = "2 GB"
Case Like "RQ1*"
CFModel = "4 GB"
Case Else
CFModel = CFSN
End Select

End Function

 

[KenHigg]

Function CFModel(CFSN As String) As String

Select Case Left(CFSN, 3)
    Case "EG1"
        CFModel = "1 GB"
    Case "FN1"
        CFModel = "2 GB"
    Case "RQ1"
        CFModel = "4 GB"
    Case Else
        CFModel = "CFSN"
End Select

End Function

???

 

[krberube]

tried that and now in my report i get "#NAME?" in my CFModel field ??? ideas why?

In CFModel control source in the report i have =CFModel() this should call the function correctly right?

 

[KenHigg]

I would think something more like:

=CFModel([CFSN])

 

[krberube]

YAAHHHOOOOO!.

Thanks again for all you time!!!!!!

 

[KenHigg]

Cool - I was about to get winded