replace characters in table

[angie]

i have a table with many fields and i import the data from an external
source. the problem is that the data consist of both latin and greek
characters. i want to create an update query that will do the following:

in four fields of my table i want to replace greek character "A" for example
with latin character "Q". the character could be anywhere within the fields
that contain both text and numbers.

i have tried to place in the update to row of the query the following :
Replace("myfieldname]";"Α";"Q")
but it does not work. i have also tried:
Replace([tbBulk.AttrValue], "A", "Q")
but it still does not work.

can you help me solve this problem?

moreover i want to create at about 15 queries in order to replace all the
greek characters. could i do that with a single query?

 

[vanderghast]

It should be

Replace( [fieldName], "A", "Q")

or


Replace( [tableName].[FieldName], "A", "Q")


Can have to use ; instead of , if your settting is such that semi colon are
required.

It is not: Replace( "field name", .... ) since then, it is the string
"field name" which will be considered as the object of the replacement,
rather than the name of the CONTAINER of the string to be replaced,

It is not: Replace( [tableName.FieldName], ... ) since then, the name of
the container would textually have a dot in its name, while you probably
targetted the syntax table.field, or, with brackets,

.[field] ( not
[table.field] )



Vanderghast, Access MVP

 

[KARL DEWEY]

i want to create at about 15 queries in order to replace all the greek
characters. could i do that with a single query?
Create a table with two fields - Greek and Latin. Populate it with the
characters.

BACKUP DATABASE BACKUP DATABASE BACKUP DATABASE
Then use this in the update query where you have both tables --
Replace([YourTable].[YourField], [Greek], [Latin])

 

[angie]

thank you for your reply. First problem is i cannot run the update query. i
get the error message "undefined function replace in expression".


Ο χÏήστης "KARL DEWEY" έγγÏαψε:

characters. could i do that with a single query?
Create a table with two fields - Greek and Latin. Populate it with the
characters.

BACKUP DATABASE BACKUP DATABASE BACKUP DATABASE
Then use this in the update query where you have both tables --
Replace([YourTable].[YourField], [Greek], [Latin])

--
Build a little, test a little.

i have a table with many fields and i import the data from an external
source. the problem is that the data consist of both latin and greek
characters. i want to create an update query that will do the following:

in four fields of my table i want to replace greek character "A" for example
with latin character "Q". the character could be anywhere within the fields
that contain both text and numbers.

i have tried to place in the update to row of the query the following :
Replace("myfieldname]";"Α";"Q")
but it does not work. i have also tried:
Replace([tbBulk.AttrValue], "A", "Q")
but it still does not work.

can you help me solve this problem?

moreover i want to create at about 15 queries in order to replace all the
greek characters. could i do that with a single query?

 

[John W. Vinson]

thank you for your reply. First problem is i cannot run the update query. i
get the error message "undefined function replace in expression".

What Access version are you using? Replace() is available in queries in all
recent versions, but I think it may not have been in Access 97 or 95...

 

[angie]

i am using access 2000

Ο χÏήστης "John W. Vinson" έγγÏαψε:

 

[vanderghast]

There was a bug in A2000 about Replace (and a couple of newly introduced
string functions) which were not accessible directly by queries. The work
around is to define a public function, in a standard module, doing nothing
more than re-warpping the VBA function:


Public Function MyReplace( source As string, pattern AS string, replacing AS
String) As String
MyReplace = Replace( source, pattern, replacing)
End Function


and, in the query, to use MyReplace instead of Replace.


Vanderghast, Access MVP