May 19, 2015 at 3:58 am
hi fellow DBA's...just need your little help....this is my query in SQL Server 2000.
"SELECT tblSchoolYear.SchoolYearTitle" & _
" From tblSchoolYear" & _
" Where (((Val(Left([tblSchoolYear]![SchoolYear], 4))) > " & Left(sOldSchoolYear, 4) & "))" & _
" ORDER BY tblSchoolYear.SchoolYearTitle;"
the error message with that query is:
Err: 'Val' is not a recognized function name.
so my question is:
1. How to rewrite my query to a sql server 2000?
Note: im using VB6 and SQL Server 2000
May 19, 2015 at 5:03 am
What is the datatype of SchoolYear?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
May 19, 2015 at 7:01 am
That's an Access query and you need to answer Phil's question or even better supply complete table definition
If the column is varchar then
"SELECT tblSchoolYear.SchoolYearTitle" & _
" FROM tblSchoolYear" & _
" WHERE LEFT(tblSchoolYear.SchoolYear, 4) > '" & Left(sOldSchoolYear, 4) & "'" & _
" ORDER BY tblSchoolYear.SchoolYearTitle ASC;"
*Editied*
p.s. your query also indicates that SchoolYear is longer than 4 chars but the first 4 are the year
Far away is close at hand in the images of elsewhere.
Anon.
May 19, 2015 at 9:45 am
the data type of schoolyear is nvarchar.
actually that database is come from ms-access then i just convert it to sql server 2000.
May 19, 2015 at 9:58 am
Instead of VAL, you should use CAST or CONVERT.
May 19, 2015 at 9:59 am
the data type of schoolyear is nvarchar.
well, there's your problem. Convert it to an INT and you're good to go. (And for heaven's sake, fix all those stupid Access naming conventions.)
May 19, 2015 at 10:10 am
sorry if i didnt post it correctly since i am newbie here. and thanks all for your suggestions i will try all your suggestions tomorrow and i will just update the outcome tomorrow. 😀
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply