Query problem splitting field into two comumns

Hi I'm new to reporting services. I'm desinging a query for a report.
I have one filed "ship_yw" that contains year and week numbers, for example,
200701, 200702. I want to display the year in one column and the week in
another. I'm connected to a Progress database via ODBC. I have accomplished
this with no problem in Access using the following code:
SELECT LEFT([Ship_yw],4), RIGHT([Ship_yw],2)
This will not work in Query Builder or if I manually type it in the string
window.
I have also tried:
SELECT
LEFT(Ship__yw,4),
RIGHT(RTRIM(Ship__yw),2)
FROM
oe_head
SELECT
SUBSTRING(Ship__yw,1,4),
SUBSTRING(Ship__yw,5,2)
FROM
oe_head
Query Builder puts unwanted ' around the field name and causes the query to
return "ship" in every row in one column and "yw" in every row of the second.
Any suggestions?Try giving names for the fildes , Use AS
"Twaterman" wrote:
> Hi I'm new to reporting services. I'm desinging a query for a report.
> I have one filed "ship_yw" that contains year and week numbers, for example,
> 200701, 200702. I want to display the year in one column and the week in
> another. I'm connected to a Progress database via ODBC. I have accomplished
> this with no problem in Access using the following code:
> SELECT LEFT([Ship_yw],4), RIGHT([Ship_yw],2)
> This will not work in Query Builder or if I manually type it in the string
> window.
> I have also tried:
> SELECT
> LEFT(Ship__yw,4),
> RIGHT(RTRIM(Ship__yw),2)
> FROM
> oe_head
> SELECT
> SUBSTRING(Ship__yw,1,4),
> SUBSTRING(Ship__yw,5,2)
> FROM
> oe_head
> Query Builder puts unwanted ' around the field name and causes the query to
> return "ship" in every row in one column and "yw" in every row of the second.
> Any suggestions?
>
>