Find Spaces in Field

[stottle]

Hello All,

I have a field in a table where data repeats its self many times.

But, some of the fields have a many spaces after the text.

eg

A040 (with nine space characters at the end)
A040 (with no characters at the end)
A040 (with nine space characters at the end)
A859 (with nine space characters at the end)
A859 (with nine space characters at the end)
A859 (with nine space characters at the end)


etc

Can i run a query on the field to find all records that have spaces after the A040 so when i run a report from crystal and group the data it shows it all in one line rather crystal thinking it is two different lines of text.

i've tried

like "* *"

but my knowledge of access queries is very limited.

Thanks in advance
stottle

[RuralGuy]

If you want to eliminate the spaces then you can use the Trim() function.

[stottle]

hi, thanks for you reponse,

i should have explained before becuase i won't be able to change the data through access as i'm only using access to look at the data which is an SQL database.

I just need to be able to find the data (along with some other fields) and then pass it to a 3rd party for them to do something on the database.

Thanks again
Stuart

[RuralGuy]

How about

InStr([YourField],"   ") > 0

...to find fields with at least three spaces in them?

[dcrake]

In your query use Left(Field,4). Simples

[RuralGuy]

So you want to return all of the records where the Len([YourField]) > 4 right?

[dcrake]

RG,

Don't think so it seams that he want to group by code and as SQL text is providing trailing spaces due to its datatype, by only using the left 4, as I suggested earlier, he can group by this column in his query so that items with and without trailing spaces are grouped as one.

David