Solved String without last Word (1 Viewer)

[nirmal]

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

 

[CJ_London]

try

shortenedPlace: left(Place,instrrev(place,",")-1)

to test in the immediate window
left("kasr, dladr, ase, 4896",instrrev("kasr, dladr, ase, 4896",",")-1)
kasr, dladr, ase

 

[nirmal]

try

shortenedPlace: left(Place,instrrev(place,",")-1)

to test in the immediate window
left("kasr, dladr, ase, 4896",instrrev("kasr, dladr, ase, 4896",",")-1)
kasr, dladr, ase

Thank You it works

 

[MajP]

@nirmal. Are any strings Null or have no ","? If so you may want a custom function

Public Function WithoutLast(TextIn As Variant) As Variant
  If Not IsNull(TextIn) Then
   WithoutLast = TextIn
   If InStr(TextIn, ",") > 0 Then
    WithoutLast = Left(TextIn, InStrRev(TextIn, ",") - 1)
   End If
  End If
End Function

or modify the in line function

 IIf(InStr([place],",")>0,Left(Nz([place]),InStrRev([place],",")-1),[place])

Comparing the two
Query1 Query1

Field1	Expr1	Expr2
the brown fox	#Func!	the brown fox
the, brown, fox	the, brown	the, brown
The	#Func!	The
the, fox	the	the
	#Error

 

[Pat Hartman]

The best solution is to fix the structure. It is poor practice to mush multiple fields into one since it violates first normal form.

 

[nirmal]

@nirmal. Are any strings Null or have no ","? If so you may want a custom function

Public Function WithoutLast(TextIn As Variant) As Variant
  If Not IsNull(TextIn) Then
   WithoutLast = TextIn
   If InStr(TextIn, ",") > 0 Then
    WithoutLast = Left(TextIn, InStrRev(TextIn, ",") - 1)
   End If
  End If
End Function

or modify the in line function

 IIf(InStr([place],",")>0,Left(Nz([place]),InStrRev([place],",")-1),[place])

Comparing the two
Query1 Query1

Field1	Expr1	Expr2
the brown fox	#Func!	the brown fox
the, brown, fox	the, brown	the, brown
The	#Func!	The
the, fox	the	the
	#Error

Sir none string will be Null or there won't be a string without comma