Query Expression (1 Viewer)

[kitty77]

How can I create an expression in a query that brings back only after the last \?
Also, if the field has no value then return a blank or null.

\6002.pdf (should look like this)
\\SERVER\Plus\Spec\Results\ABC\ABC\Results\6002.pdf

Thanks.

 

[pbaldy]

Check out the InStrRev() function. That along with the Mid() function should get what you want. You'll have to first test for blank/null/no slash.

 

[kitty77]

Any examples you could provide?

 

[pbaldy]

Not offhand, just start with the InStrRev() and work your way out a step at a time. Post back if you get stuck.

 

[MajP]

It can be done with just the mid and instrrev, but you likely in a query will have to handle a Null value and a value without a slash
You can do it in line with an iif but easier to do a simple UDF.

Public Function AfterSlash(TheVal As Variant) As Variant
  AfterSlash = TheVal
  If Not IsNull(TheVal) Then
    If InStr(AfterSlash, "\") > 0 Then
      AfterSlash = Mid(AfterSlash, InStrRev(AfterSlash, "\") + 1)
    End If
  End If
End Function

Or the painful inline

IIf(InStr([field1],"\")>0,Mid([field1],InStrRev([field1],"\")+1),[field1])

 

[MajP]

See

Solved - String without last Word

Sir, I have a string field value in a column Place in Place_tbl, for example 123, abdf, sygea, dsead or kasr, dladr, ase, 4896 I want the result without the last word 123, abdf, sygea kasr, dladr, ase What should be the query design Please Help

www.access-programmers.co.uk

which is almost the exact opposite question. The OP wanted to remove everything after the last slash.

 

[pbaldy]

There's your fish.

 

[kitty77]

It can be done with just the mid and instrrev, but you likely in a query will have to handle a Null value and a value without a slash
You can do it in line with an iif but easier to do a simple UDF.

Public Function AfterSlash(TheVal As Variant) As Variant
  AfterSlash = TheVal
  If Not IsNull(TheVal) Then
    If InStr(AfterSlash, "\") > 0 Then
      AfterSlash = Mid(AfterSlash, InStrRev(AfterSlash, "\") + 1)
    End If
  End If
End Function

Or the painful inline

IIf(InStr([field1],"\")>0,Mid([field1],InStrRev([field1],"\")+1),[field1])

Perfect!