Query to remove spaces

[The Boondock Saint]

Hello all,
Is there a way you can make a query remove any spaces from field which is
text....

any ideas?
Cheers
The Saint

 

[Allen Browne]

1. Make a backup of the database in case something goes wrong.

2. Create a query using this table.

3. Change it to an Update query (Update on Query menu.)
Access adds an Update row to the grid.

4. Drag the text field into the grid.

5. In the Update row under this field, enter:
Replace([Field1], " ", "")
but put your field name in place of Field1.

6. Run the query.

That should work fine in Access 2002 or 2003. Post back for a workaround it
complains in Access 2000. If you are using Access 97 or earlier, you will
need further instructions as the Replace() function did not exist back then.

 

[The Boondock Saint]

Thanks Allen,
Sorry I forgot to say i was using A2000

I tried it with your example but it didnt work....and ideas for A2000?

1. Make a backup of the database in case something goes wrong.

2. Create a query using this table.

3. Change it to an Update query (Update on Query menu.)
Access adds an Update row to the grid.

4. Drag the text field into the grid.

5. In the Update row under this field, enter:
Replace([Field1], " ", "")
but put your field name in place of Field1.

6. Run the query.

That should work fine in Access 2002 or 2003. Post back for a workaround
it complains in Access 2000. If you are using Access 97 or earlier, you
will need further instructions as the Replace() function did not exist
back then.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Hello all,
Is there a way you can make a query remove any spaces from field which is
text....

any ideas?
Cheers
The Saint

 

[Allen Browne]

You did not indicate the error message you received, but perhaps it is this
issue:
http://support.microsoft.com/?id=225956
If so, the solution is to create the wrapper function as suggested.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Thanks Allen,
Sorry I forgot to say i was using A2000

I tried it with your example but it didnt work....and ideas for A2000?

1. Make a backup of the database in case something goes wrong.

2. Create a query using this table.

3. Change it to an Update query (Update on Query menu.)
Access adds an Update row to the grid.

4. Drag the text field into the grid.

5. In the Update row under this field, enter:
Replace([Field1], " ", "")
but put your field name in place of Field1.

6. Run the query.

That should work fine in Access 2002 or 2003. Post back for a workaround
it complains in Access 2000. If you are using Access 97 or earlier, you
will need further instructions as the Replace() function did not exist
back then.

Hello all,
Is there a way you can make a query remove any spaces from field which
is text....

any ideas?
Cheers
The Saint