return char number value as numeric

[CoffeeGuru]

I have tried to use a bit of code so return the Last Year and WeekNo in my database, to select some records but….
It currently fails, I think this because it returns the values as text and will then not compare to a numeric field.
Is there an easy way to change this code so it returns numbers.

qryCurrentWkYr

[SIZE=3][FONT=Calibri]SELECT[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]Right(yw,2) [/FONT][/SIZE]
[SIZE=3][FONT=Calibri]AS week, [/FONT][/SIZE]
[SIZE=3][FONT=Calibri]Left(yw,4) [/FONT][/SIZE]
[SIZE=3][FONT=Calibri]AS [year][/FONT][/SIZE]
[SIZE=3][FONT=Calibri]FROM ([/FONT][/SIZE]
[SIZE=3][FONT=Calibri]SELECT [/FONT][/SIZE]
[SIZE=3][FONT=Calibri]max(year & "-" & right('0' & week,2)) [/FONT][/SIZE]
[SIZE=3][FONT=Calibri]AS yw [/FONT][/SIZE]
[SIZE=3][FONT=Calibri]FROM [CM-DATA])  [/FONT][/SIZE]
[SIZE=3][FONT=Calibri]AS [%$##@_Alias];[/FONT][/SIZE]

UPDATE: Just found the function VAL

 

[namliam]

Your query should indeed return the proper year and weeknumber as you are expecting.

Why isnt it working? What is it returning that you are not expecting?

 

[CoffeeGuru]

Hi manliam

it was returning the values as text.
However, wrapping VAL around it has now produced numeric values.

I think this will work now, just working on the rest of query

 

[namliam]

Yes right and left will return text values, .... but this query should still return the proper values for the results....

You didnt post any other SQL or anything so I can only evaluate this one.