to replace "ram" with "shyam"

[hotmalepiyush]

i am really new to queries.. and this might seem very easy to u all.. but please lemme know..
i have a huge table and i have to change some values.. how to do it in one go...

thnx
piyush

 

[DCrake]

Simple Software Solutions

You need to be looking at the function Replace as your question suggests.

NewString = Replace(SearchString,LookForString,ReplaceWithString)

such as

NewString = Replace("My Name is Bill Gates","Bill Gates","Bill The Geek Gates")

CodeMaster:

 

[hotmalepiyush]

how do i write a query with this.. please cite an example..

 

[DCrake]

Simple Software Solutions

If the changes you are about to make are going to be permanent you may be better off doing it in VBA

First Create a function called MyReplace()

Function MyReplace(MyField as Field, MyFindText As String, MyReplaceText As String)

Dim Rs As DAO.Recordset
Set Rs = CurrentDb.OpenRecordset("YourTableNameHere")

If Not Rs.EOF And Not Rs.BOF Then

   Do Unitl Rs.EOF
       Rs.Edit
       Rs(MyField) = Replace(Rs(MyField),MyFindText,MyReplaceText)
       Rs.Update
       Rs.MoveNext
   Loop
   Rs.Close
   Msgbox "Finished"
End If

Set Rs = Nothing

In the immediate window (Ctrl+G) type in

? MyReplace("[YourFieldNameHere]","Monday","Tuesday")

This will open up the table named in the MyReplace function
Check for EOF
Look in the field name you passed as Parameter 1 for Parameter 2
Replace it with Parameter 3
Save the changes and move to the next record in the table.



One thing to remember if there is the likelyhood that the look for text appears more than once in the contents of the field than all occurances will be replaced.

CodeMaster:

 

[twoplustwo]

Whilst an elegant solution couldn't you just write an update query?

 

[DCrake]

Simple Software Solutions

Whilst Update queries will suffice, the "elegant solution" provided is more flexible whereas the user does not have to edit the query each time to change the field names and table names for every update.

Using this utility provides LAE - Least Administrative Effort. The key to good progamming.

CodeMaster:

 

[hotmalepiyush]

hey DCrake,
thanx for ur effort.. i really appreciate it
i have used macros in excel. i started with recording and then moved on to VBA programming and stuff.. one cannot record macros in access na.. thats why i havent used them as yet... can u brief me about where to start from if i want to master macros in access as well..

regards
piyush

 

[georgedwilkinson]

hey DCrake,
thanx for ur effort.. i really appreciate it
i have used macros in excel. i started with recording and then moved on to VBA programming and stuff.. one cannot record macros in access na.. thats why i havent used them as yet... can u brief me about where to start from if i want to master macros in access as well..

regards
piyush

Macros in Access are not like macros in Excel. If you want something that you're familiar with, you need to look at VBA coding in Access. However, you cannot record things like you can in Excel, unfortunately. Which means you have to totally rely on your VB coding skills.

You invoke the VB editor in Access in the same way you invoke it in Excel (<alt> F11 is one way).

Also, keep in mind that VBA in Access is a little more form and object event centric than VBA in Excel. Access, in that respect, is a lot more like vanilla VB.