Replace characters in a table

[Guest]

Hi,

I have several records and several fields in a table that contain the "$" in
random locations and all these "$" need to be changed to "0" (zero). I
thought maybe a macro would work, but looking through this access.queries
newsgroup, it seems like a query is the answer but I don't know how to write
this update query.

Any suggestions?

Thanks

 

[Guest]

I forgot to say that the $ are embedded within text. For example "ASDF-$ASDF"
or "SDFLK$SDFLKJ". There's no specific, repeating location of the $.

 

[Guest]

Backup the table and try this in your update query --
Replace([YourFieldName], "$", "0")

 

[Guest]

Karl,

So I have to create a new query, go into the SQL view and type in
"Update Replace([tbBulk.AttrValue], "$", "0");"? My syntax is wrong and I'm
not sure what to change it to!

Thanks

Backup the table and try this in your update query --
Replace([YourFieldName], "$", "0")

I forgot to say that the $ are embedded within text. For example "ASDF-$ASDF"
or "SDFLK$SDFLKJ". There's no specific, repeating location of the $.

 

[Guest]

No.
Create a select query and then in design view right click in the open space
where the tables are. Move the cursor to Query Type and select Update Query.

Drag your field to the Field row.

Type in the Update To row the following --
Replace([tbBulk.AttrValue], "$", "0")

Run the query.

Karl,

So I have to create a new query, go into the SQL view and type in
"Update Replace([tbBulk.AttrValue], "$", "0");"? My syntax is wrong and I'm
not sure what to change it to!

Thanks

Backup the table and try this in your update query --
Replace([YourFieldName], "$", "0")

I forgot to say that the $ are embedded within text. For example "ASDF-$ASDF"
or "SDFLK$SDFLKJ". There's no specific, repeating location of the $.

:

Hi,

I have several records and several fields in a table that contain the "$" in
random locations and all these "$" need to be changed to "0" (zero). I
thought maybe a macro would work, but looking through this access.queries
newsgroup, it seems like a query is the answer but I don't know how to write
this update query.

Any suggestions?

Thanks

 

[Guest]

Hi Karl,

I have this now:

UPDATE tbBulk SET tbBulk.AttrValue = Replace([AttrValue],"$","0");

which seems to work except I get a "Microsoft Access can't update all the
records in the update query. Microsoft Access didn't update 2 field(s) due to
a type conversion failure, 0 record(s)....". I think I get this error b/c 2
of the fields I am updating are null or blank.

Thanks

Backup the table and try this in your update query --
Replace([YourFieldName], "$", "0")

I forgot to say that the $ are embedded within text. For example "ASDF-$ASDF"
or "SDFLK$SDFLKJ". There's no specific, repeating location of the $.

 

[Guest]

Hi Karl,

Thanks! I'm more familar with queries working in the design view although
this Replace bit was new to me. Thank you!

No.
Create a select query and then in design view right click in the open space
where the tables are. Move the cursor to Query Type and select Update Query.

Drag your field to the Field row.

Type in the Update To row the following --
Replace([tbBulk.AttrValue], "$", "0")

Run the query.

Karl,

So I have to create a new query, go into the SQL view and type in
"Update Replace([tbBulk.AttrValue], "$", "0");"? My syntax is wrong and I'm
not sure what to change it to!

Thanks

Backup the table and try this in your update query --
Replace([YourFieldName], "$", "0")

:

I forgot to say that the $ are embedded within text. For example "ASDF-$ASDF"
or "SDFLK$SDFLKJ". There's no specific, repeating location of the $.

:

Hi,

I have several records and several fields in a table that contain the "$" in
random locations and all these "$" need to be changed to "0" (zero). I
thought maybe a macro would work, but looking through this access.queries
newsgroup, it seems like a query is the answer but I don't know how to write
this update query.

Any suggestions?

Thanks

 

[fredg]

Hi Karl,

I have this now:

UPDATE tbBulk SET tbBulk.AttrValue = Replace([AttrValue],"$","0");

which seems to work except I get a "Microsoft Access can't update all the
records in the update query. Microsoft Access didn't update 2 field(s) due to
a type conversion failure, 0 record(s)....". I think I get this error b/c 2
of the fields I am updating are null or blank.

Thanks

Backup the table and try this in your update query --
Replace([YourFieldName], "$", "0")

I forgot to say that the $ are embedded within text. For example "ASDF-$ASDF"
or "SDFLK$SDFLKJ". There's no specific, repeating location of the $.

:

Hi,

I have several records and several fields in a table that contain the "$" in
random locations and all these "$" need to be changed to "0" (zero). I
thought maybe a macro would work, but looking through this access.queries
newsgroup, it seems like a query is the answer but I don't know how to write
this update query.

Any suggestions?

Thanks

So then you need to take Null fields into account:

UPDATE tbBulk SET tbBulk.AttrValue = Replace([AttrValue],"$","0")
Where tbBulk.AttrValue Is Not Null;