April 28, 2006 at 8:31 am
I'm doing upsizing from Access to SQL Server. However the queries didn't get transfer to the SQL Server. The following is the typical query I don't know how to rewrite in SQL, which has IIf syntax:
SELECT [LName] & ", " & [FName] AS Employee, tblTimesheet.PayType, tblTimesheet.Hours, tblTimesheet.dateWorked, tblTimesheet.caseID, tblCase.location, tblPosition.jobPositionGroup, IIf([dateWorked]>#1/26/2005#,[RE05],[RE04]) AS RE, IIf([dateWorked]>#1/26/2004#,[OT05],[OT04]) AS OT, IIf([dateWorked]>#1/26/2004#,[TR05],[TR04]) AS TR, IIf([PayType]="RE",[RE],IIf([PayType]="OT",[OT],
)) AS HourRate, [NewRate]*[Hours] AS SubTotal, tblTimesheet.NRate, IIf([NRate]>0,[NRate],[HourRate]) AS NewRate
FROM ((tblTimesheet INNER JOIN tblPosition ON tblTimesheet.PositionID = tblPosition.positionID) INNER JOIN tblCase ON tblTimesheet.caseID = tblCase.caseID) INNER JOIN tblEmpInfo ON tblTimesheet.EmpID = tblEmpInfo.empID
WHERE (((tblTimesheet.dateWorked) Between [forms].[frmRunRpt].[BeginningDate] And [forms].[frmRunRpt].[EndingDate]));
Thanks in advance for your help. Any help will be very much appreciated.
Yueming
May 1, 2006 at 7:35 am
when you do upsizing, the tables don't get migrated unless you do an ADP front end. Try to go to view and choose sql view and the copy the code and paste it into query analyzer and convert it into a view or procedure depending on your code.
May 1, 2006 at 10:55 am
Hi, Jessica,
Thank you very much for your advise. The upsizing did create an ADP front end, so I have all the tables and some very simple queries get migrated. However, all the complex queries failed. I copy and paste the code in query analyzer, I get the following error message:
incorrect syntax near '>'
incorrect syntax near 'tblCase'
Here is the code in Access:
SELECT tblTimeSheet.TaskNum, tblTimeSheet.caseID, tblCase.location, tblTimeSheet.dateWorked, [FName] & " " & [LName] AS Employee, tblPosition.jobPositionGroup, tblTimeSheet.PayType, tblTimeSheet.Hours, IIf([dateWorked]>#10/21/2005#,[DEACRBRE06],[DEACRBRE]) AS RE, IIf([dateWorked]>#10/21/2005#,[DEACRBOT06],[DEACRBOT]) AS OT, IIf([dateWorked]>#10/21/2005#,[DEACRBTR06],[DEACRBTR]) AS TR, IIf([PayType]="RE",[RE],IIf([PayType]="OT",[OT],
)) AS HourRate, [NewRate]*[Hours] AS SubTotal, tblTimeSheet.NRate, IIf([NRate]>0,[NRate],[HourRate]) AS NewRate
FROM tblPosition INNER JOIN (tblEmpInfo INNER JOIN (tblTimeSheet INNER JOIN tblCase ON tblTimeSheet.caseID = tblCase.caseID) ON tblEmpInfo.empID = tblTimeSheet.EmpID) ON tblPosition.positionID = tblTimeSheet.PositionID
WHERE (((tblCase.location)="CRB"));
Please give further advise.
Thank you.
Yueming
May 2, 2006 at 3:56 am
IIf([NRate]>0,[NRate],[HourRate]) AS NewRate
Try this and it will work (one field from you query):
CASE NRATE >0 THEN NRATE ELSE HOURRATE AS NEWRATE
May 2, 2006 at 4:36 am
Also don't forget that dates in SQL Server use single quotes not #. So '12/02/2005'
Also it's a general rule to put dates in the following format yyyymmdd, as to avoid any issues with date format. i.e. '20050212'
Also Access tends to over do it with brackets () in the where clause - which I always find harder to read and understand. So I tend to re-write the where clause from scratch.
Cheers,
Rodney.
May 3, 2006 at 1:10 pm
Thank all of you for help.I really appreciate it.
I rewrite the query, but still get the error message:
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near '>'.
Here is the query syntex:
SELECT dbo.tblEmpInfo.LName + ', ' + dbo.tblEmpInfo.FName AS Employee, dbo.tblTimesheet.caseID, dbo.tblTimesheet.location, dbo.tblTimesheet.dateWorked, dbo.tblTimesheet.Hours, dbo.tblTimesheet.NRate, dbo.tblTimesheet.PayType, dbo.tblPosition.jobPositionGroup,
CASE dateWorked>'1/26/2005' THEN RE05 ELSE RE04 AS RE,
CASE dateWorked>'1/26/2005' THEN OT05 ELSE OT04 AS OT,
CASE dateWorked>'1/26/2005' THEN TR05 ELSE TR04 AS TR,
CASE PayType = RE THEN RE,
CASE PayType = OT THEN OT ELSE TR AS HourRate,
CASE NRate>0 THEN NRate ELSE HourRate AS NewRate,
FROM dbo.tblEmpInfo INNER JOIN
dbo.tblTimesheet ON dbo.tblEmpInfo.empID = dbo.tblTimesheet.EmpID INNER JOIN
dbo.tblPosition ON dbo.tblTimesheet.PositionID = dbo.tblPosition.positionID
Any help will be very much appreciated.
Yueming
May 4, 2006 at 7:21 am
Yueming,
You need to write your case statement like this:
Case when dateworked > '1/26/2005 THEN tro5 ELSE tro4 END,
Hope this helps,
Rodney.
May 4, 2006 at 8:08 am
Hi, Rodney,
Thank you so much for help. I edited the query as you instructed. Things seem to be improved. I got the following error message which is more to the point:
Msg 207, Level 16, State 1, Line 5
Invalid column name 'RE'.
Msg 207, Level 16, State 1, Line 5
Invalid column name 'RE'.
Msg 207, Level 16, State 1, Line 6
Invalid column name 'OT'.
Msg 207, Level 16, State 1, Line 6
Invalid column name 'OT'.
Msg 207, Level 16, State 1, Line 6
Invalid column name 'TR'.
Msg 207, Level 16, State 1, Line 7
Invalid column name 'HourRate'.
Here is the query syntax:
SELECT
dbo.tblEmpInfo.LName + ', ' + dbo.tblEmpInfo.FName AS Employee, dbo.tblTimesheet.caseID, dbo.tblTimesheet.location, dbo.tblTimesheet.dateWorked, dbo.tblTimesheet.Hours, dbo.tblTimesheet.NRate, dbo.tblTimesheet.PayType, dbo.tblPosition.jobPositionGroup,
CASE
WHEN dateWorked>'1/26/2005' THEN RE05 ELSE RE04 END AS RE,
CASE
WHEN dateWorked>'1/26/2005' THEN OT05 ELSE OT04 END AS OT,
CASE
WHEN dateWorked>'1/26/2005' THEN TR05 ELSE TR04 END AS TR,
CASE
WHEN PayType = RE THEN RE END,
CASE
WHEN PayType = OT THEN OT ELSE TR END AS HourRate,
CASE
WHEN NRate>0 THEN NRate ELSE HourRate END AS NewRate
FROM
dbo.tblEmpInfo INNER JOIN
dbo
.tblTimesheet ON dbo.tblEmpInfo.empID = dbo.tblTimesheet.EmpID INNER JOIN
dbo
.tblPosition ON dbo.tblTimesheet.PositionID = dbo.tblPosition.positionID
Please give further advise. Thank you.
Yueming
May 5, 2006 at 6:41 am
In T-SQL, if you surround a value with double-quotes, it interprets it as a column name. Or, in your case, it interprets it as a column name when you use no quotes. You need to use single quotes to indicate string values.
A couple other things to watch for:
Nz() is not valid in T-SQL. Use ISNULL() instead, and it works the same way.
'&' does not work to concatenate strings. Use '+' instead. When doing this, explicitly convert all the pieces to varchar. It will save you headaches in the long run.
Look up CAST and CONVERT in BOL. Data type conversion works a bit differently.
Note that you can no longer directly reference values on forms or reports in your queries (e.g. [Forms]![frmMyForm]![txtMyData]). You'll need to pass them as parameters to stored procedures instead.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply