More issues converting from .ADP to .ACCDB not populating list box

[cleteh]

I receive an error saying Syntax error (missing operator in query expression 'ais.AuditFormTypeID=aft.ID INNER JOIN etc.) I don't know why I would receive upon trying to open the form is my first question. I try to debug in the load event but the error occurs before the first line of code in the Load event is executed.

In the .ADP version this form opens without any issue and works perfectly fine. If I hit OK on the error the form opens. There is then a click event to populate a list box that doesn't work in the .ACCDB version. Here is the code for the click event... I think it may be related to the error I get when I first open the form.

If cboAuditType.Text = "Internal" Then
sSql = "SELECT DISTINCT CONVERT(VARCHAR, ais.FormNeededDate, 101) AS MonthNeeded, aft.policy "
sSql = sSql & " FROM AuditInstanceSchedule ais"
sSql = sSql & " INNER JOIN AuditFormType aft ON ais.AuditFormTypeID = aft.ID"
sSql = sSql & " INNER JOIN AuditFamilyType aft2 ON aft.AuditFamilyTypeID = aft2.ID"
sSql = sSql & " WHERE ais.FormCreatedDate IS NOT NULL"
sSql = sSql & " AND ais.ReportFinalizedDate IS NULL"
sSql = sSql & " AND aft2.Internal = 1"
sSql = sSql & " GROUP BY aft.policy, CONVERT(VARCHAR, ais.FormNeededDate, 101)"
sSql = sSql & " ORDER BY MonthNeeded, aft.policy;"
Else
sSql = "SELECT DISTINCT CONVERT(VARCHAR, ais.FormNeededDate, 101) AS MonthNeeded, aft.policy "
sSql = sSql & " FROM AuditInstanceSchedule ais"
sSql = sSql & " INNER JOIN AuditFormType aft ON ais.AuditFormTypeID = aft.ID"
sSql = sSql & " INNER JOIN AuditFamilyType aft2 ON aft.AuditFamilyTypeID = aft2.ID"
sSql = sSql & " WHERE ais.FormCreatedDate IS NOT NULL"
sSql = sSql & " AND ais.ReportFinalizedDate IS NULL"
sSql = sSql & " AND aft2.Internal = 0"
sSql = sSql & " GROUP BY aft2.FamilyType, CONVERT(VARCHAR, ais.FormNeededDate, 101)"
sSql = sSql & " ORDER BY MonthNeeded, aft.policy;"
End If

Debug.Print sSql

lstAuditInstance.RowSource = sSql
lstAuditInstance.Requery

When I copy and paste the sql string into sql server and execute I get the results that should populate the list box. One thing I noticed is that in one of my tables I get -1 instead of 1 which is displayed when I view the table in SQL Server vs. Access 2013

[PhilS]

Some general explanations first.
ADPs directly connect to SQL Server. Any query is send directly to the server.
With an AccDB and linked SQL Server tables any SQL statement is parsed by the Access ACE-Database-Engine first and then passed on to SQL-Server if appropriate. So, you need to adapt your SQL statements for Access/ACE to work in an AccDb.

Now for your posted SQL.
The CONVERT function is a SQL Server function and it is not known by the local ACE-Engine. You can use the Format-Function instead to format a date in SQL.
The Join expressions need to be nested in brackets for Access/ACE. This is to gross for me, so I'm not posting an example here. Just use the Access Query Designer to build any query with Joins and view the resultung SQL to get gist of Acces Joins.

It probabaly would be sensible to create a view in SQL Sever for the base query of your SQL instead of using that as data source for your list box.

When I copy and paste the sql string into sql server and execute I get the results that should populate the list box.

Yes, of course it works. Thats the environment it was used in before. Now you need to check it against the local database engine.
Open a new query in Access and paste the SQL into the SQL View of that query to test it.

One thing I noticed is that in one of my tables I get -1 instead of 1 which is displayed when I view the table in SQL Server vs. Access 2013

Correct, SQL Server uses 0 and 1 to represent Bit/Boolean data while Access/ACE used 0 and -1. You need to account for that when writing SQL.

[PhilS]

With an AccDB and linked SQL Server tables any SQL statement is parsed by the Access ACE-Database-Engine first and then passed on to SQL-Server if appropriate.

There is a notable exception to this: Pass-Through-Queries are directly passed through to SQL Server, hence their name.

[cleteh]

Thanks Phil, that makes a lot of sense. All my VBA experience has come working within this .adp file. If I create the view in SQL Server and then link it to the database could I then simply set sSql = (The View Name) in my code above for it to work?


If cboAuditType.Text = "Internal" Then
sSql = View1
Else
sSql = View2

End If

Debug.Print sSql

lstAuditInstance.RowSource = sSql
lstAuditInstance.Requery

[PhilS]

If I create the view in SQL Server and then link it to the database could I then simply set sSql = (The View Name) in my code above for it to work?

Basically yes, but I'm not entirely sure about the ORDER BY clause in the statement.
So I would rather use a SQL statement including an explicit ORDER BY for the Rowsources.

sSql = "SELECT MonthNeeded, aft.policy FROM yourView ORDER BY MonthNeeded, aft.policy;"

It's hard to say without knowing more about your data and the requirements. Still, if possible, I would create only one view, which includes the column Internal in the output and then put the where condition Internal = 1 or Internal = 0 into the VBA-Code building the rowsources. If you have lots of views this will reduce their total number and is probably more manageable long term, as there is only one view per "use case" to modify if requirements change.

[cleteh]

Thanks again Phil... In this case the ORDER BY doesn't make a difference but you are right in other areas I have to insert the ORDER BY like you did above because it doesn't seem to recognize the ORDER BY in the View.

Here is the code I went with for this piece.

If cboAuditType.Text = "Internal" Then
sSql = "select * from OpenAuditInstanceInternal"
ElseIf cboAuditType.Text = "Vendor" Then
sSql = "select * from OpenAuditInstanceVendor"
Else: sSql = "select * from OpenAuditInstanceCPS"
End If

Debug.Print sSql

lstAuditInstance.RowSource = sSql
lstAuditInstance.Requery