replace part of the field 1

[peini]

I've tried to use the update query to update
the table, I thought I can use replace function
but it doesn't work, any other way to do it
Thanks.


Update table1
Set col1=replace(col1,"-","&quot
Where instr(Col1,"-&quot>0

 

[FoxProProgrammer]

It looks like you are trying to replace all instances of a dash in col1 with an empty string. Bascially you want to delete all the dashes in col1. Is this correct? Why doesn't your query work? Do you get an error or does it just return the wrong results? dz
dzaccess@yahoo.com

 

[peini]

When I ran the query
it pops up a message


Undefined function "replace in expression

 

[peini]

Oops! I didn't reply all your questions.

Yes, I'm trying to replace all the dash in for that column.
It simply pops up the error message, the query not runn
at all. Looks like it can't recognize the replace function.
I'm using Access 2000

 

[FoxProProgrammer]

The reason your SQL doesn't work is because the Replace function returns a value. The correct syntax would be

col1 = Replace(col1, "-", "&quot

You can't include that type of expression in a query (that I know of).

The following will work.

Dim db As Database
Dim rs As Recordset

Set db = Currentdb
Set rs = db.OpenRecordset("table1&quot

While Not rs.EOF
rs.Edit
rs.col1 = Replace(rs.col1, "-", "&quot
rs.Update
rs.MoveNext
Wend

rs.Close
db.Close

It's not an Update query, but it works.

Best regards, dz
dzaccess@yahoo.com

 

[peini]

Thank you for your quick answer.
I just tried it.

I got complier error
points to this line
db As Database
and says user-defined type not defined

Probably I misplaced the code.
Got to go home now to take care my baby.
I'll try again first thing in the morning tomorrow.
Thank you again.

 

[FoxProProgrammer]

I just read that you are using Access 2000. Make sure that you have the DAO library installed. Open the code window. Click on Tools, References, and scroll down to the Microsoft DAO object library. Click it. Then the code should run. If it doesn't Dim the db and rs like this:

Dim db As DAO.Database
Dim rs As DAO.Recordset

It should work either way. dz
dzaccess@yahoo.com