modifying data from ODBC

[Noewon]

Hi



I'm using tables created from a linked ODC source. One of the fields from the original database has leading spaces, which is affecting any attempts to run a query. The corresponding field in another table has no spaces

So essentially the SQL would be querying: WHERE " 123" = "123". How do i strip the leading spaces?

I've tried using a "create table" query and then running an update on that, but no dice. I've tried:

UPDATE test2 SET test2.contract_no = Trim([contract_no]);

But this just gives the same string with leading spaces.

I must admit I'm still at a basic level on this side of things, if any further information is required, let me know.

[ssanfu]

I ran your update query on a table in A2K3 and it removed the leading space.

Try executing this query. What happens?

SELECT Test2.contract_no, Len([contract_no]) AS LenNoTrim, Len(Trim([contract_no])) AS LenTrim FROM Test2;

Do you get LenNoTrim = 4 and LenTrim = 3 ???

[Noewon]

The actual data has more characters, but adding your extra code did return the right numbers.

I must be screwing up the syntax somewhere in my original query...

[Noewon]

Got it. It was a syntax error. Thanks for your help.

[ssanfu]

You are welcome