none
SQL from SSMS to Access RRS feed

  • Question

  • I am trying to run some SQL from SSMS in Access and I keep getting this error

    Syntax error (missing operator) in query expression 'FluidTransfer.[SourceContainerID] = FluidContainer.[ID] INNER JOIN FluidContainer on FluidTransfer.[DestinationContainerID] = FluidContainer.[ID] INNER JOIN Equipment on FluidContainer.[EquipmentID] = Equipment.[ID] INNER JOIN Equipment on FluidContainer.[EquipmentID] = Equipment.[ID] INNER JOIN person on FluidTransfer.[EmployeeID] = person.[ID] INNER JOIN Vendor on FluidTransfer.[VendorID] = Vendor.[ID'.

    I know that there are some differences in syntax between TSQL and SQL in access, and I am struggling to find them and make the changes. I read another forum regarding this issue and they solved the problem by placing parenthesis in certain areas of the code. I tried this, but I can't seem to get it right. Here is the entire query with minimal changes. Thanks in advance for any assistance with this issue.

    EDIT: I apologize about the formatting of this post, Whenever I try to move a line of code to the next line it adds another space between or changes the font size...

    SELECT  (person.FirstName + " " + person.LastName) as Fueler , CONVERT(VARCHAR(10),FluidTransfer.TransferDate,101) as [Date], FluidContainer.code as [From], FluidContainer.Code as To, FluidTransfer.Quantity, 
    FluidTransfer.MeterReading, FluidTransfer.MeterReadingDate, FluidTransfer.OdometerReading, FluidTransfer.OdometerReadingDate, 'Bad Meter Reading' as 'Problem Description'
    FROM FluidTransfer INNER JOIN FluidContainer on FluidTransfer.[SourceContainerID] = FluidContainer.[ID] INNER JOIN FluidContainer on FluidTransfer.[DestinationContainerID] = FluidContainer.[ID] INNER JOIN Equipment on FluidContainer.[EquipmentID] = Equipment.[ID] INNER JOIN Equipment on FluidContainer.[EquipmentID] = Equipment.[ID] INNER JOIN person on FluidTransfer.[EmployeeID] = person.[ID] INNER JOIN Vendor on FluidTransfer.[VendorID] = Vendor.[ID] 
    WHERE CAST(FluidTransfer.[TransferDate] as date) between DATEADD(day,-30,CAST(getdate() as date)) and CAST(getdate() as date) and (FluidTransfer.[MeterReadingStatus] >=2 or FluidTransfer.[OdometerReadingStatus] >= 2) 
    ORDER BY CONVERT(VARCHAR(10),FluidTransfer.[TransferDate],101), (person.[FirstName] + ' ' + person [LastName]); 


    Monday, February 18, 2019 2:18 PM