Using MS query

[Ian@1904]

I am having a few issues using the MS Query tool within Excel
I am trying to extract the 7 digits on the left (vehicle reg) out of a data field named narrative

In the query in a new column I entered
Reg:left([narrative],7)

and several variations but just obtain syntax errors. Any ideas on a way round this, or what am I doing wrong

[Adrian Kelland]

This might be the kind of question where the data is the problem, rather than the function use. Are all fields full, how will it handle nulls etc.

[Ian@1904]

Originally Posted by Adrian Kelland

This might be the kind of question where the data is the problem, rather than the function use. Are all fields full, how will it handle nulls etc.

All fields contain data of some form usual a 7 digit reg and sometimes the model eg KV07XXA Audi A6

[Garf]

what's the field type ian.

[Adrian Kelland]

I'd have a good look at the entires that cause the problem Ian. If the syntax is right for some of them, then it is probably the data that is the problem. Is it a lot of data?

[Garf]

I just connected to an access database using ms query

I selected all the fields from the table and then went and edited the SQL

I added left(tablename.fieldname,7)

and this worked

then I edited the SQL again and changed it to be

left(tablename.fieldname,7) AS FIELDDESCRIPTIONIWANT

and this worked

hope that helped

[Ian@1904]

Originally Posted by Garf

left(tablename.fieldname,7) AS FIELDDESCRIPTIONIWANT

and this worked

hope that helped

Thank you, sorted. A little bit of knowledge goes a huge long way.