IIf statement

[moorsey]

Should this statement work?

IIf([CalledNumber] Like "*?*",[CalledNumber]=Mid([CalledNumber],2))

Some of the fields brought in from the CSV file have random "?" in front of some of the names, so I made this to remove the first character of the field if there was a "?" there. Maybe there is an easier way, but I would still like to understand why this doesnt, it just returns no data as if there are no fields matching, there are some with a "?" e.g.

?JoeBloggs

 

[Smart]

if([CalledNumber] Like "*?*",[CalledNumber]=Mid([CalledNumber],2))

Try this

if([CalledNumber] Like "*?*",[CalledNumber]=Mid([CalledNumber],2),[CalledNumber])

 

[moorsey]

still the same results, should I be putting this in the criteria box or after "callednumber" in the field box, I get confused as to which goes where, I've tried it in both anyway, in the criteria box it returns no results as before, (I see what I missed in my query, the second part of the IIf) and in the field box it gives a circular reference, which I couldn't seem to fix by making a new field for the calculation...

 

[Smart]

in the field section type

CallNum:iif([CalledNumber] Like "*?*",[CalledNumber]=Mid([CalledNumber],2),[CalledNumber])

 

[moorsey]

just tried that now, still no luck, it just returns a zero value if there is data in "callednumber", looks like it should work fine, ........


edit: realised the zero value is irrelivent, because the change is being made in callednumber, it still does not remove the "?", odd

 

[wazz]

CalledNum: IIf(InStr([CalledNumber],"?"),Mid([CalledNumber],2),[CalledNumber])

 

[allan57]

I think you will struggle using the Like command as the ? can be used within its criteria. The following will strip out your invalid character:

Dim CalledNumber As String
Dim Result As String

CalledNumber = "?Fred"

If Left(CalledNumber, 1) = Chr(63) Then
Result = MID([CalledNumber], 2)
End If