CODEWITHSUNDEEP
ms-access
D347HxD
D347HxD

Reputation: 465

How to insert text infront of text in table under column

I am working with at able and I have imported data from excel into this table. Each text in the column below has different text in it, and I'd like to put ID: in front of all of the ones I selected, as not all fields will have the ID: in front of it so I don't want to apply a format to the whole column. How would I do this?

This is what the column looks like:

I want to be able to select the circled data in the "notes" field of the table, then put ID: in front of them all.

Upvotes: 1

Views: 310

Answers (1)

jacouh
jacouh

Reputation: 8741

I'm the first who may understand the author ;-), do you want this?

UPDATE MyTable SET Notes = 'ID:' & Notes
WHERE (Notes NOT LIKE 'ID:*');

This Access update query prepends 'ID:' string before Notes without it.

  1. In Access, you create a query, name it qryUpdNotes for example, change type to UPDATE. Save it and double click it. Access will do the rest of the job

  2. Use DAO in VBA: CurrentDb.Execute "UPDATE MyTable SET Notes = 'ID:' & Notes WHERE (Notes NOT LIKE 'ID:*');"

I use this:

Function daoExecuteSql(ByVal strSQL As String) As Long
'
  Dim nDone As Long
'
  Dim db As DAO.Database
'
  On Error GoTo ErrorStatus
'
  Set db = CurrentDb
  db.Execute strSQL, dbFailOnError
'
  nDone = db.RecordsAffected
'
  On Error GoTo 0
'
' db.Close
  Set db = Nothing
'
  daoExecuteSql = nDone
'
  Exit Function
'
ErrorStatus:
'
  MsgBox "daoExecuteSql(): " & strSQL
'
  Set db = Nothing
  daoExecuteSql = 0
'
End Function

And Call it like this in a Sub/Macro:

Dim strSQL
strSQL = "UPDATE MyTable SET Notes = 'ID:' & Notes" _
  & " WHERE (Notes NOT LIKE 'ID:*');"
daoExecuteSql strSQL

Upvotes: 2

Related Questions