Syntax Error (missing operator)

Question

Background:

We had an ADP database, but with the new Office, it is no longer supported, so I am transferring everything over to ACCDB.  I have updated this query, but it gives me a syntax error at the group of INNER JOINS.

Can someone please point out where I screwed up, this is killing me.

Thanks

Chad

SELECT P.[WorkLocationDescription] AS Source

              ,D.[WorkLocationDescription] AS Destination

              ,M.[MaterialType]

              ,  T.[Loads]

               * E.[Capacity]

               * M.[MaterialSwellFactor]

               * M.[MaterialBulkDensity] AS Tonnes

              ,IsProductive(T.[Pit_SP_AreaID], T.[DumpingLocationID], T.[MaterialTypeID]) AS IsProductive

              ,(SELECT TOP 1 RS.[GroupID]

                FROM dbo_UI_ReportSetup_Production RS

                LEFT JOIN dbo_UI_ReportSetup_Production_Account RA ON RA.[GroupID] = RS.[GroupID]

                WHERE RS.[ConfigID] = @ConfigID

                  AND (RS.[AllPitSPAreaCategory] = 1       OR RS.[PitSPAreaCategoryID]       = P.[WorkLocationCategoryID])

                  AND (RS.[AllDumpingLocationCategory] = 1 OR RS.[DumpingLocationCategoryID] = D.[WorkLocationCategoryID])

                  AND (RS.[AllMaterialCategory] = 1        OR RS.[MaterialCategoryID]        = M.[MaterialCategoryID])

                  AND (RS.[AllMaterialType] = 1            OR RS.[MaterialTypeID]            = M.[MaterialTypeID])

                  AND (RS.[AllAccount] = 1                 OR RA.[AccountID]                 = A.[AccountID])

                ORDER BY RS.[Priority]) AS GroupID

        FROM dbo_TimeCardData T

        INNER JOIN dbo_Equipment E ON E.[EquipmentID]   = T.[EquipmentID]

        INNER JOIN dbo_MaterialType M ON M.[MaterialTypeID] = T.[MaterialTypeID]

        INNER JOIN dbo_WorkLocation P ON P.[WorkLocationID] = T.[Pit_SP_AreaID]

        INNER JOIN dbo_WorkLocation D ON D.[WorkLocationID] = T.[DumpingLocationID]

        INNER JOIN dbo_WorkLocationCategory C ON C.[WorkLocationCategoryID] = P.[WorkLocationCategoryID]

        INNER JOIN dbo_EquipmentCategory Q ON Q.[CategoryID] = E.[CategoryID]

        INNER JOIN dbo_Account A ON A.[AccountID] = T.[AccountID]

        WHERE T.[Date] BETWEEN @FromDate AND @ToDate

          AND Q.[IsHauling] = 1

          AND A.[IsTraining] = 0

    ), Material AS (

        SELECT P.[GroupLabel]

              ,P.[Priority]

              ,T.[Source]

              ,T.[Destination]

              ,T.[MaterialType]

              ,CASE T.[IsProductive] WHEN 1 THEN T.[Tonnes]

                                            ELSE 0.0 END 'Productive'

              ,CASE T.[IsProductive] WHEN 0 THEN T.[Tonnes]

                                            ELSE 0.0 END 'Rehandle'

        FROM Tonnes T

        INNER JOIN dbo_UI_ReportSetup_Production P ON P.[GroupID] = T.[GroupID]

        WHERE T.Tonnes > 0

    )

SELECT [GroupLabel]

          ,[Priority]

          ,[Source]

          ,[Destination]

          ,[MaterialType]

          ,CAST(SUM([Productive]) AS decimal(19,2)) 'Productive'

          ,CAST(SUM([Rehandle]) AS decimal(19,2)) 'Rehandle'

    FROM Material

    GROUP BY [GroupLabel]

            ,[Priority]

            ,[Source]

            ,[Destination]

            ,[MaterialType]

END

Answer 1

Ok, I assumed perhaps more experience here, but it would seem you are making some good progress here.

I was in fact suggesting two steps.

That “store procedure” stub I posted, and the suggesting to add your t-sql right after was to be done in sql management studio (SSMS).

Once that store procedure was created, you THEN follow the second part as to how to call that t-sql.

So the steps are:

From SSMS – you paste in that create procedure stub, and also include your long t-sql you posted. I assume you choose a store procedure name of your choice or whatever the heck you like. I used “MyWorkQuery”, but unfortunately can’t read minds, and what name you choose is really up to you, no more so then what name you want to give a windows folder or file.

Once you paste that create proc into SSMS and run it, then you can toss out that query you just made. You only run that create store proc code one time. This will create a store procedure with your 2 parameters, and the t-sql you have. You are NOT doing this from Access, but from SSMS.

If you don’t have SSMS, or experience with SSMS, then you need to play with SSMS. I can no more suggest a location for you to deliver some pizza, but then finding out you don’t have a driver’s license means that a rather simple act of asking you to get a coffee for me is missing that you don’t drive.

As noted, if you get this right, it really is a cut + paste kind of approach. (You don’t have to read the t-sql, or debug it, or even modify it).

Once you created the store proc, then you create the PT query in Access with the “exec” part. And as noted, the important detail is that original t-sql has two parameters in it. We using this approach so we really don’t have to write code, but really just cut + paste what we have already.

If the PT query is prompting for a connection, then you not setup the connection correctly. So you need + want to address that issue. When you create the PT query, you want to create the connection – if you must, create a new FILE dsn. I am going to repeat this only one time:

ALWAYS use a FILE dsn. (failure to follow this advice will cause much pain on your part, and you will become a thirsty camlel wondering the desert looking for water).

So the ONLY PT query part you do in Access is this:

Exec MyWorkQuery '2018-05-15', '2018-05-16'

You want to get the above query working in Access. Don’t try code, forms and all kinds of other stuff.

Get the PT query working – and replace the above dates with some known dates you have. Once again, we are cut + paste here. We not writing code, and we really not working hard. So far, we just playing with the mouse and cutting and pasting stuff.

So, in summary:

Steps are:

Create the store proc – that is done 100% outside of Access. (with SSMS)

If the store proc is created success, then next up is to create the Access PT query. And inside of that PT query for testing you can use the above exec.

Exec MyWorkQuery '2018-05-15', '2018-05-16'

So the above is what you place in that PT query. You don’t really want to attempt or mess with the first part I have above with “create procedure” inside of Access. You really (but really!) want to do that kind of stuff from SSMS – not from access.

So after the create proc works. Then next is to create a PT query in Access. At this point in time you not written any VBA, not modified any Access VBA code at all.

So the working assuming here is that you have some basic skills to work with SQL server and SSMS. If you don’t have use of SSMS, then you are really dead in the water.

Once you get the above two steps working, then you can now look at say the VBA code you have to launch the report with the date parameters, and get that working (so that would be step 3 in this process).

As noted, depending on the VBA code used to launch the report and “how” it gets the date will in fact determine if my advice to use a store proc (in placed of the OTHER suggesting to use a view). As noted, you can consider a view, but you have to modify your existing SQL, and I am suggesting you adopt a cut + paste approach in which you don’t really care to read, or even know how the t-sql works. I mean, ok, take a look as you cut + paste, but really why waste brain power and time reading something you already know has been working for many years.

So a basic skill set to work with SQL server is required here. (and thus did not realize you would not know where to paste + run the create procedure part (you do that in SSMS – not access).

So the first 2 steps can be accomplished without writing any VBA code, and the steps are in fact a mechanical cut + paste approach.

Total time here is less then what it will take you to read this post.

So you are 100% correct. The creating of the store proc does not return records, but I assume you spooled up a copy of the sql management studio (SSMS). That part of creating the t-sql procedure will NOT be done from Access. (it can be, but I don’t recommend you do this – too much work and pain).

So before anything of above, you need to be using + running SSMS.

Regards,

Albert D. Kallal (Access MVP, 2003-2017)

Edmonton, Alberta Canada

Answer 2

You can certainly base a Query on a preexisting Query; just use the query name in place of a table name.

But I'm not at all clear what you're starting with, or what you're trying to accomplish. If you could post a fuller description someone might be able to help.

Answer 3

What I would do here is paste the whole previous query UN-TOUCHED into the query builder.

Change the query to a pass-through query. Keep in mind that ALL queries from an ADP projects are RAW T-SQL.

So that query can and will run un-modified without any effort on your part.

Now such queries when returned this way in Access are read only, but just a quick glance at the “joins” etc., this was likely for some type of report or form that displays data – not edits it.

So you “can” convert the T-SQL query back to a JET/ACCESS sql, but that can be some real work, and in a “lot” of cases, you really don’t need nor want to make such an effort.

So, I vote for creating a pass-through query – it should run 100% as before without any changes.

As noted, the only downside to such an approach is results are read only – but as noted, often that is not an issue.

If for some reason you need a read/write result, then take the original raw T-SQL from the query, and paste it into SQL studio as a view. Once again, zero syntax change will be required, and the results are read/write. (You will have to “link” to the view from Access).

So before you spend too much time trying to convert the T-SQL query to an Access/Jet query, I suggest using a PT query.

However, the one big issue in your query is you have 2 parameters.

Because of the two parameters? Well, I would remove the condition from the query and create a view.

Now base your report on this view.

Then to open the report (and still get high speed filter performance) do this:

Dim strWhere   as string

strWhere = "T.[Date] between " & FromDate & " and " & _

            ToDate

The “big” issue is however you have to “add” the t.date as a column in that view – that is still too much work!!!!

If you want even LESS changes to your code?

Save the query as a store proc. That way you not even had to add the T.[Date] as a column.

So, try this:

Create PROCEDURE [dbo].[MyWorkQuery]

   @ToDate as datetime,

   @FromDate as datetime

AS

BEGIN

       -- SET NOCOUNT ON added to prevent extra result sets from

       -- interfering with SELECT statements.

       SET NOCOUNT ON;

    -- Insert statements for procedure here

       -- place you BIG query below

END

So right below the “place” your query below, just cut + paste in your query.

Since we added the two params, then you should require ZERO changes to your sql.

Next, in Access now you need to create pass-though query.

It will look like this:

Exec MyWorkQuery

Ok, save the above. In fact the “exec” is optional.

Now, in code to launch  the report, you go:

With currentdb.QueryDefs("Name of PT query")

   .SQL = "exec MyWorkQuery " & strStartDate & "," & strEndDate

End with

Docmd.OpenReport "myReport",acViewPreview,,strWhere

So the report is to now be based on this pt query.

The only trick/tip in above, is you have to set strstartDate and strEndDate as CORRECTLY formatted SQL dates. I usually make a “function” to do this for me.

The take away in the above suggesting here is to avoid at ALL COSTS converting the sql to Access/jet sql. You really don’t have to, nor want to in the vast majority of cases since it can be a lot of work – work that you can avoid.

I use this handy function to return a “correct” t-sql date.

Public Function qudateSQL(myDate As Variant) As String

   ' returns ISO date for sql server

   If IsNull(myDate) = True Then

      qudateSQL = ""

   Else

      ' USE iso date format

      qudateSQL = "'" & Format(myDate, "yyyy-mm-dd") & "'"

   End If

End Function

So our final code (if we use a PT query with store proc) would be:

Dim strStartDate   as string

Dim strEndDate     as string

strStartDate = qudateSQL(me.SomeDateOnform)

strEndDate   = qudateSQL(me.SomeEndDateOnForm)

With currentdb.QueryDefs("Name of PT query")

   .SQL = "exec MyWorkQuery " & strStartDate & "," & strEndDate

End with

Docmd.OpenReport "myReport",acViewPreview

So really, without ANY modify of the sql, you can take and grab t-sql from your ADP, and use it without changes.

If we follow above, then the whole process turns into a cut + paste approach – and is every little work.

So now we doing a cut + paste process as opposed to re-writing that rather long t-sql statement as jet/sql – that’s often a challenge – one that can be avoided in most cases.

So create the store proc. You can use my above sample header – paste in your exiting sql. Run that create store proc.

Now, create the pt query in Access. You are really quite much done. You have to move the parmaters from the openReprot to the code just before you launch the report (and thus you can remove the “where” clause from that report.

It is a “close” tie to make a view, or store proce. If you did not have @Params in that existing t-sql, then I would go the view road. However, if @Parms exist “deep” in the existing t-sql, then I am just too lazy to even read the t-sql – just paste it into some store proce like above and not even really much look at the sql.

Don't try and fix great existing t-sql that you "know" been working for years. Adopt a cut + paste approach, and you in most cases will have ZERO changes to your t-sql.

Regards,

Albert D. Kallal (Access MVP, 2003-2017)

Edmonton, Alberta Canada

Answer 4

Thank you for all that detail.  I tried running the script through a PT query.  It asks me to select an ODBC connection (which I have), but then it says "Pass-through query with ReturnsRecords property set to True did not return any records.

I thought maybe that is fine, and I called the query from the report and it put me into an infinite loop of selecting an ODBC connection.

Answer 5

I wish I could.  I have programming knowledge and experience, but never dealt with databases.

I am being tasked with overhauling an old obsolete *.adp database (with 15 years of data) to a new access front end database.

The tables, forms, reports all seem to cross over somewhat fine, its just all the stored procedures.

This procedure is way above me.  It appears to grab a bunch of data, then runs the data through a function to determine if the material mined out is productive or rehandle based on material type.

Then it compares the data to a different table and checks to see if the area that it was taken from is productive or rehandle (dug out from an original location, or dug out from a place that is a secondary dumping location.

Then it assigns a group based on a different table.