Eliminate spases in number

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have data with spaces in it. The spaces are not consistant ie
06 12548 124
0124 124 112
1241854 4877
I want to create an expresion to eliminate the spases compleatly.
Please help
 
Assuming you're using Access 2000 or newer, try Replace([YourTextField], "
", "")
 
The low tech method:
First and foremost make a backup of the table or entire database just in
case things go wrong.
Open the table.
Go to Edit, Replace.
In Find What put in a space.
Leave Replace With blank.
In Look In make really sure that you only select the field with the spaces
or it might remove all spaces in all fields of the table.
Match Any Part of the Field
Search All.

If you have a lot of records, this could take a long time compared to doing
it with a query.

To be precise they are not numbers; rather, they are numerical characters in
a text field. If you convert the field to a number after removing the spaces,
the leading zeros will also be eliminated.
 
The ugly way:

Create two or more calculated fields in a query.
In the first field use this formula
FieldofFun1: Left([field1],1) & IIf(Mid([field1],2,1)="
","",Mid([field1],2,1)) & IIf(Mid([field1],3,1)="
","",Mid([field1],3,1)) & IIf(Mid([field1],4,1)="
","",Mid([field1],4,1)) & IIf(Mid([field1],5,1)="
","",Mid([field1],5,1)) & IIf(Mid([field1],6,1)="
","",Mid([field1],6,1))
(Replace "[field1]" with the name of your troublesome field.)
In the second field in the query, do the same thing as the first
increasing the number of the Mid function.
Create a third field in your query and use the formula
FinalField:FieldofFun1&FieldofFun2

You can do the same on a form using unbound fields.
 
Back
Top