Replace last comma with "and" (1 Viewer)

[rio]

Hello.
how to replace last comma with "and" in query:

ID Name
a1 adam, toni, richard, kamarul
b1 rio, man, sarah

to

ID Name
a1 adam, toni, richard and kamarul
b1 rio, man and sarah

 

[John Big Booty]

Try something like;

[URL="http://www.techonthenet.com/access/functions/string/left.php"]Left[/URL]([Your String], [URL="http://www.techonthenet.com/access/functions/string/instrrev.php"]InstRev[/URL]([Your String], ",")-1) & " AND " & [URL="http://www.techonthenet.com/access/functions/string/right.php"]Right[/URL]([Your String], [URL="http://www.techonthenet.com/access/functions/string/len.php"]Len[/URL]([Your String])-InstRev([Your String], ",")

Caveat Emptor; untested aircode

 

[rio]

thanks john for replies. when i used the code it came with error. this is the code i used to combine the data from multi row to one row.

Public Function ConcatField_STRING(Code As String) As String

    Dim db As DAO.Database
    Dim rst As DAO.Recordset
    Dim strSQL As String
    Dim sReturn As String
    
    Set db = Application.CurrentDb
    
    strSQL = "SELECT [Nama Pegawai] FROM tblPegawaiSelected WHERE [Kod Operasi] = '" & Code & "'"
    
    Set rst = db.OpenRecordset(strSQL, dbOpenForwardOnly, dbReadOnly)
    
    If Not rst.EOF And Not rst.BOF Then
        
        Do While Not rst.EOF
            
            sReturn = sReturn & rst(0) & ", "
            
            rst.MoveNext
        Loop
        
    End If
    
    sReturn = Mid$(sReturn, 1, Len(sReturn) - 2)
    
    ConcatField_STRING = sReturn
    
    rst.Close
    Set rst = Nothing
    db.Close
    Set db = Nothing
    
End Function

 

[John Big Booty]

Ok I've just tested and fixed my air code from my previous post it should look like;

 Left([TestText],InStrRev([TestText],",")-1) & " AND " & Right([TestText],Len([TestText])-InStrRev([TestText],","))

 

[John Big Booty]

Here's a further modification;

 Replace(Left([TestText],InStrRev([TestText],",")-1) & " AND " & Right([TestText],Len([TestText])-InStrRev([TestText],",")),"  "," ")

I've used the Replace() function to find double spaces that might be created by my previous code with a single space.

 

[rio]

hi john. where should i put the code.

should i put this code under ConcatNama_Pegawai column in query.

Replace(Left([ConcatNama_Pegawai],InStrRev([ConcatNama_Pegawai],",")-1) & " AND " & Right([ConcatNama_Pegawai],Len([ConcatNama_Pegawai])-InStrRev([ConcatNama_Pegawai],",")),"  "," ")

or create new colum with this code

NewName: Replace(Left([ConcatNama_Pegawai],InStrRev([ConcatNama_Pegawai],",")-1) & " AND " & Right([ConcatNama_Pegawai],Len([ConcatNama_Pegawai])-InStrRev([ConcatNama_Pegawai],",")),"  "," ")

here i attach the database
View attachment Test.mdb

 

[John Big Booty]

I'd use it to create an expression in a new column of your Query Design grid.

If however you wish to perminatly update a set of records I'd use it in the Update to filed of an update query.

Really depedns on your desired outcome.

 

[rio]

ok. i used it to create new column. it's working. but for the single name it came out with error. should I used IIF function?

 

[John Big Booty]

ok. i used it to create new column. it's working. but for the single name it came out with error. should I used IIF function?

Yes, you'll need to use an IIf, something along the lines of;

Iif(Instr("Your String", ",")=0, "Your String", [COLOR="Orange"]Do what we've already got[/COLOR])

Replace this bit Do what we've already got with the formula that we've already got working.

 

[rio]

thanks john for ur help. it's working perfect.

 

[John Big Booty]

thanks john for ur help. it's working perfect.

I knew we could do it