This query works in Access not in SQL server

[longmatch]

I have an Access 2000 database, which was upsized into our local sql server 7.0. The connection is working right now. I also tested with a simple query, it is fine.
But a query working in Access database, does not work in sql server any more. Anybody know the reason. Please refer the query below.

strSQL = "SELECT Resident, count(rotation) as No FROM (SELECT B.ID, B.Resident, B.Blockname, B.StartDate, B.EndDate, B.Rotation, R.PGY, R.Track FROM tbl_blockInfo as B INNER JOIN tbl_resident as R ON B.Resident = R.Resident WHERE R.PGY =" & PGY & &quot group by Resident"

the error massage is
Microsoft OLE DB Provider for SQL Server error '80040e14'

Incorrect syntax near the keyword 'group'.

/haijunw/resident_schedule/TMPf45kcdenux.asp, line 40

The query is on the line 40 right now.

Thanks

longmatch

 

[Microbe]

May be better to try the SQL forum

Steve Davis
hey.you@hahaha.com.au

http://www.hahaha.com.au

Me? I can't even spell ASP!

 

[davesho]

longmatch-

Did you solve this anomaly? I'm having the same problem: Here's my SQL statement:

SELECT [time stamp], [shrink], [shrink per inch],
[part number], [send reason]
FROM [Cut Length Adjust Log]
WHERE [time stamp] Between #6/28/2003# And #7/29/2003#
AND [shrink] Is Not Null
AND [part number] Like '50218AB*'
AND [send reason] = '20 Minute Length Check';

SQL from VB seems to be having a problem with the Like
'50218AB' part -- when I change to the specific Like '50218AB225' then everything is A-OK (in both Access and VB). Thanks!

Dave

 

[roy29]

davesho,

You may want to change the * (asterisk) to a % (percent sign) in the like portion.

 

[Veep]

Why the subquery? Couldn't you just do:

strSql="SELECT B.Resident,Count(B.Rotation) as [No] FROM tbl_blockInfo B " _
& "JOIN tbl_Resident R ON B.Resident = R.Resident WHERE R.PGY=" & PGY & " GROUP BY B.Resident"
Looks more friendly this way.
I take it PGY is a numeric variable?

davesho, # is not the date delimiter for SQL Server and * is not the wildcard character. Use ' and % respectively.

 

[clarkin]

I recommend you have a look at this:

http://www.aspfaq.com/show.asp?id=2214

very, very comprehensive access->SQL server differences FAQ


Posting code? Wrap it with code tags: [ignore]

[/ignore][code]CodeHere

[ignore][/code][/ignore].

 

[davesho]

My apologies -- my problem is a difference in how MS-Access handles SQL compared to Visual Basic 6.0 (not between Access and SQL Server).

Using the same SQL statement (ref. above), in Access I get a recordset returned with 56 or so records, in VB using the same SQL statement, I get EOF and BOF.

Sorry for the miscommunication... Maybe I've got the wrong forum?

 

[longmatch]

You do need to check your connectionstring to make sure you are connected to the right database and right table. Some time other errors result that, not sql itself. Just double check.

Longmatch