Replace numbers

[fencie3]

Hi,

May I know how do you replace the number which is only the 4 digits in front.

For example,

2002-123-9631
2002-789-5211
2005-159-6320

Based on this example above, how can I replace the "2002" to "2007" only, rather than I replace all?

Hope to hear from you soon.

 

[Galaxiom]

IIF(Left([fieldname],4)="2002","2007" & Mid([fieldname],5),[fieldname])

 

[fencie3]

Hi Galaxiom,

I am new to this access 2007, could you please guide me in steps.

I will be appreciated if you can tell me where should i put this queries or if there is another way to replace it?

Can I replace it from the button itself? As I know I can find the 2002 but when you start to replace it I have no idea to change it.

Thanks

 

[Galaxiom]

Where are you trying to replace the value. Permanently in a table or just for display in a report?

 

[fencie3]

Hi Galaxiom,

It is in the table. I need to search the "2002" and change it to "2007" as the above.

Is there any button I can click without any queries?

Thanks.

 

[Galaxiom]

It cannot be done with Find and Replace. Access is not like Excel or Word.
Do not fear the query for they are central to database operation and there is the Designer.

Create a query and drag the table into the top area.
Drag the field you want to change into the boxes at the bottom.

Right click on the top area and change the query type to Update.

In the Update To cell for the field enter:
"2007" & Mid([fieldname],5)

In the Criteria cell enter:
Left([fieldname],4)="2002"

You can check which records will be affected by changing to Datasheet view.

Then save it an run the query. Access will warn you how many records will change.