Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2001-03-27 : 23:20:15
|
Asimwe writes "This statement is from report in MS access 2000 database and it work fine ,and I did transfer all data to SQL Server 2000
every thing went ok
But Now I just copy this statement direct from Access to SQL Query analyse doesn't want to compile How can I modify this statement so can work In Sql as all field and data are the same in both databases
SELECT DISTINCTROW tblScaffold.JobNo, tblScaffold.ScaffID, tblMaterial.MatType, tblMaterial.Qty, tblMaterial.Length, tblMaterial.Width, tblMaterial.Height, tblMaterial.Lifts, tblMatRates.OldRate, tblMatRates.NewRate, Month([Month]) AS Expr1, Year([Month]) AS Expr2, tblScaffold.StartDate, tblScaffold.FinishDate, IIf([FinishDate] Is Null,CCur([Qty]*[Length]*[Width]*[OldRate]*[Lifts]*(DateValue(Now())-DateValue([StartDate])+1)),CCur([Qty]*[Length]*[Width]*[OldRate]*[Lifts]*(DateValue([FinishDate])-DateValue([StartDate])+1))) AS Expr3, IIf([FinishDate] Is Null,CCur([Qty]*[Length]*[Width]*[NewRate]*[Lifts]*(DateValue(Now())-DateValue([StartDate])+1)),CCur([Qty]*[Length]*[Width]*[NewRate]*[Lifts]*(DateValue([FinishDate])-DateValue([StartDate])+1))) AS Expr4
FROM (tblJobs INNER JOIN tblScaffold ON tblJobs.JobNo = tblScaffold.JobNo) INNER JOIN ((tblMatType INNER JOIN tblMaterial ON tblMatType.MatType = tblMaterial.MatType) INNER JOIN tblMatRates ON tblMatType.MatType = tblMatRates.MatType) ON tblScaffold.ScaffID = tblMaterial.ScaffID
WHERE (((Month([Month]))=Month([StartDate])) AND ((Year([Month]))=Year([StartDate])));
I will appriciate your Help
Regards
Asimwe
Electronic Business Development
Photoplay Group
32 Delhi Street
West Perth 6005
Tel: 9321 7933
Fax: 9321 7944
WA, Australia
www.photoplay.com.au" |
|
|
|
|
|
|
|
SQL statement