Removing spaces from fields

[Col]

Hi all,

Having trouble with the following hope some kind soul can help.

I'm developing a database at work, which imports an Excel sheet of staff
details. The pay number is not in a format we want in that it consists of
three numbers then some spaces and then up to five numbers for example '123
4567'

For our use we only need the final four or five numbers.

Once the sheet has been imported I've set up a query using which does the
following;

1: Use the StripExtraChars function which I found on the support section of
Microsoft.com which reduces the above example to 123 4567 i.e. just one
space, I gave this field name 'Trim' as follows;

Trim: StripExtraChars([Pay]," ")

2: Use the InStr function to give the location of the space as follows;

Space: InStr([Trim]," ")

3: Use Mid function;

Pay Number: Mid([Trim],[Space]+1,5)

At the moment I'm importing the Excel sheet, then filtering our own staff by
office location and putting that into a table [1].

I then run the example above on table 1 which gives another table albeit
with the extra Trim and Space columns.

Is there some way to incorporate all of that in one string, so I only have
to make one table and without the extra columns, i.e. with just the
correctly formatted pay number.

Many thanks for any advice received.

Colin.

 

[Guest]

Try this --
Mid(StripExtraChars([Pay]," "),Instr(StripExtraChars([Pay]," ")," ")+1,5)

 

[Col]

Yep, that's great thank you Karl.

 

[MarkB]

I tried StripExtraChars([fname]," ")) on a field that has some values that
have an extra space at the end but it didn't do anything.

The values look like the following, e.g., "Joan " not "Joan", but not all
fields have the trailling space.

Also tried the RTRIM function with InStr looking for a space but although I
can see the space is present, Access acts as if it is not there.