Is Select Case the correct way to do this

[macca]

I wonder if any one can help with this one, shown below are 2 examples of strings in a field called PROCOM



Occurred: EnterMeterReadings; Reason: Reading greater than upper limit; Session: 0A52BE4293.worker1

MSPSubmitMeterReadings; Reason: CANNOT BILL LATER THAN 29/02/08, DATE AMENDED FROM 13/03/08

I ususally nest IIf statements to abbreviate these in another field (PROC), as PROC: IIf ([PROCOM] like Occurred: EnterMeterReadings; Reason: Reading greater than upper limitMRE-Upper limit",[PROCOM])

The problem I have is there now 30 permutations of the error and IIF can only nest 8 statements (and it looks very messy). I thought that Select Case would do the job but I cannot use

Select Case

Case Is Like "Occurred: EnterMeterReadings; Reason: Reading greater than upper limit*"

Hope someone can help with this one

Thanks in advance

 

[boblarson]

No, you can't use Select Case like that. But you can create a function, which can use the select case, to return the value based on what you feed it.

 

[macca]

Sorry bob, maybe I have not explained it too well. the Select case was being used in a function. The problem was I could not get it to find a string with a wildcard (*) at the end of it

 

[Brianwarnock]

From access help, see last sentence

comparison operator
A character or symbol indicating a relationship between two or more values or expressions. These operators include less than (<), less than or equal to (<=), greater than (>), greater than or equal to (>=), not equal (<>), and equal (=). Additional comparison operators include Is and Like. Note that Is and Like can't be used as comparison operators in a Select Case statement.

Brian

 

[Brianwarnock]

Having read your first post again I get the impression that you do not do this in a function, if you used a function then the If.. Then block form can have an unlimited number of Elseif and it will not look untidy.

Brian

 

[boblarson]

You can use the Instr function to check for it by looking for CHR(42) which is the Asterisk.

 

[Brianwarnock]

You can use the Instr function to check for it by looking for CHR(42) which is the Asterisk.

I maybe wrong but i think that you misunderstood him Bob, i don't think he wants to find the asterisk he wants the case to belike

Case Like wil* to find wilson williams etc , but you cannot use Like and I therefore assume that you cannot use wild cards or whatever they are called.

brian

 

[boblarson]

Actually Brian, you should be able to use the Instr function within the select case to get at that:

Case Instr(1, YourString, "wil" & Chr(42))> 0

untested but I've done similar before.

 

[Brianwarnock]

I don't see how that helps that has to go on the Select case and returns true or false which the Case can use, but that means 30 Select Case, what he wants is one Select Case with 30 Case statements, I think
If .. Then...
Elseif
Elseif



is simpler.

Brian