Hello. I'm new to Query Builder in VS 2005. I'm an IT pro that flirts with
progamming so I'm no expert. I've used Access in the past to perform the task
I'm having trouble with.
I have one field [ship_yw] which represents the ship year and week. all data
is in the following format: 200701, 200702 and so on.
In Access I accomplished this using LEFT([ship_yw],4) in one column and
RIGHT([ship_yw],2) in the other. this worked perfect. I'm trying to write
the same query in VS 2005 Query Builder. QB insits on this
LEFT('[ship_yw]',4) which treats the field name like a strign right???? all
that returs form the query is '[ship_yw]' in every row not the year numbers.

[quoted text, click to view]
SELECT
LEFT(Ship__yw,4),
RIGHT(RTRIM(Ship__yw),2)
FROM
TestT

better way

SELECT
SUBSTRING(Ship__yw,1,4),
SUBSTRING(Ship__yw,5,2)
FROM
TestT

[quoted text, click to view]

Thanks for the reply. I tried this but when I enter SUBSTRING(Ship__yw,1,4)
under "column" in query builder, it changes it to

SELECT "SUBSTRING"('Ship__yw', 1, 4) AS Expr1
FROM PUB.oe_head

and then returns the word "ship" in every row not 2004,2005,2006 etc. from
the table. any thoughts?

That is the built-in formatting options in query builder. Consider using
Query Analyzer to write queries. It is much more reliable than other EM
tools.

--
Anith