InStr function in Query

[Teknologik]

Hi,

I'm trying to seperate out multiple characters from a free text field.

Expr1: Left([RefNo],InStr([RefNo],"-")-1)

But the problem being that I want to exclude - or /.

Is there anyway of achieving this?

Thanks

 

[nanscombe]

You could create a function to do it. I assume you are trying to get rid of the characters rather than pass the characters before the "-" or "/".

Expr1: NoChars([RefNo])

Public Function NoChars(byval theSource as Variant)
' Deals with any NULLs
theSource = theSource & ""

' Replace any "-" with nothing
theSource = Replace(theSource , "-" , "")

' Replace any "/" with nothing
theSource = Replace(theSource , "/" , "")

NoChars = theSource
End Function

Or, if you did want the first characters before ...

Expr1: NoChars([RefNo])

Public Function NoChars(byval theSource as Variant)
dim lngFound as long

' Deals with any NULLs
theSource = theSource & ""

' Replace any "-" with "/" so there's only one thing to find
theSource = Replace(theSource , "-" , "/")

' Look for "/", if found pass back only the characters before it.
lngFound = instr(theSource , "/")
if lngFound >0 then theSource = Left(theSource, lngFound - 1)

NoChars = theSource
End Function

 

[Teknologik]

You could create a function to do it. I assume you are trying to get rid of the characters rather than pass the characters before the "-" or "/".

Expr1: NoChars([RefNo])

Public Function NoChars(byval theSource as Variant)
' Deals with any NULLs
theSource = theSource & ""

' Replace any "-" with nothing
theSource = Replace(theSource , "-" , "")

' Replace any "/" with nothing
theSource = Replace(theSource , "/" , "")

NoChars = theSource
End Function

Or, if you did want the first characters before ...

Expr1: NoChars([RefNo])

Public Function NoChars(byval theSource as Variant)
dim lngFound as long

' Deals with any NULLs
theSource = theSource & ""

' Replace any "-" with "/" so there's only one think to find
theSource = Replace(theSource , "-" , "/")

' Look for "/", if found pass back only the characters before it.
lngFound = instr(theSource , "/")
if lngFound >0 then theSource = Left(theSource, lngFound - 1)

NoChars = theSource
End Function

I'm trying to remove anything after the - or / can this be achieved in a query and not a module?

Thanks for your help

 

[bob fitz]

Try this:

Expr1: IIf(InStr([RefNo],"-"),Left([RefNo],InStr([RefNo],"-")-1),IIf(InStr([RefNo],"/"),Left([RefNo],InStr([RefNo],"/")-1),[RefNo]))

 

[nanscombe]

Or (since I've been working on it)

Expr1: IIf(InStr(Replace([RefNo] , "/" , "-") , "-")>0 , Left([RefNo], InStr(Replace([RefNo] , "/" , "-") , "-")-1) , [RefNo])

 

[Teknologik]

Or (since I've been working on it)

Expr1: IIf(InStr(Replace([RefNo] , "/" , "-") , "-")>0 , Left([RefNo], InStr(Replace([RefNo] , "/" , "-") , "-")-1) , [RefNo])

Nanscombe that worked. Thank you.