cleteh
04-26-2017, 12:42 PM
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
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