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?
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
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