SQL Output - Characters found after end of SQL statement (1 Viewer)

[Cark]

Hi All,

I have been pulling my hair out for the past 6 hours on-and-off trying to identify where I have gone wrong with my SQL Output that is causing Run-time error '3142' to appear saying Characters found after end of SQL statement.

Is anyone able to quickly identify where the rogue character is that is throwing everything out of sync?

The original SQL Output in VBA is:

    SQL_Output = "SELECT TblTcAOG.ID, TblTcAOG.Date, TblTcAOG.STN, TblTcAOG.REG, TblTcAOG.ATA, TblTcAOG.[ATA2Digit Description], TblTcAOG.ATA4DIGIT, TblTcAOG.ATA2DIGIT, TblTcAOG.ATALAST2, TblTcAOG.[REASON FOR AOG], TblTcAOG.[AOG HRS], TblTcAOG.[AOG MINS], TblTcAOG.AOGTime, TblTcAOG.AOGTotalMins, TblTcAOG.[Event Type], TblTcAOG.[Severity Index], TblTcAOG.[Dispatched IAW MEL], TblTcAOG.[MEL Reference], TblTcAOG.[ETOPS Sector], TblTcAOG.[Charter Flight], tblactype.actypeid, tblactype.Manufacturer, tblactype.Type " & _
                 "FROM (tblactype INNER JOIN TblAcrft ON tblactype.[actypeid] = TblAcrft.[ModelLink]) INNER JOIN TblTcAOG ON TblAcrft.[Reg] = TblTcAOG.[REG]; " & _
                 "WHERE ((TblTcAOG.Date)>=" & dStart & " And (TblTcAOG.Date)<=" & dEnd & " AND ((TblTcAOG.AOGTotalMins)>=[forms]![FrmRptCriteriaAOG]![AOG_Time])) "

When this is printed it gives me the following:

SELECT TblTcAOG.ID, TblTcAOG.Date, TblTcAOG.STN, TblTcAOG.REG, TblTcAOG.ATA, TblTcAOG.[ATA2Digit Description], TblTcAOG.ATA4DIGIT, TblTcAOG.ATA2DIGIT, TblTcAOG.ATALAST2, TblTcAOG.[REASON FOR AOG], TblTcAOG.[AOG HRS], TblTcAOG.[AOG MINS], TblTcAOG.AOGTime, TblTcAOG.AOGTotalMins, TblTcAOG.[Event Type], TblTcAOG.[Severity Index], TblTcAOG.[Dispatched IAW MEL], TblTcAOG.[MEL Reference], TblTcAOG.[ETOPS Sector], TblTcAOG.[Charter Flight], tblactype.actypeid, tblactype.Manufacturer, tblactype.Type FROM (tblactype INNER JOIN TblAcrft ON tblactype.[actypeid] = TblAcrft.[ModelLink]) INNER JOIN TblTcAOG ON TblAcrft.[Reg] = TblTcAOG.[REG]; WHERE ((TblTcAOG.Date)>=#04/11/2015# And (TblTcAOG.Date)<=#04/11/2019# AND ((TblTcAOG.AOGTotalMins)>=[forms]![FrmRptCriteriaAOG]![AOG_Time])) AND TblTcAOG.[ATA2DIGIT] IN (0,20,21,12,11,10,09,08,07,06,05,24,25,26,27,28,29,30,31,32,33,22,23,34,35,36,38,47,46,49,51,52,53,54,55,56,57,70,71,72,73,74,75,76,77,78,79,80,01,02,03,04)

Let me know if anything else needs sharing.

 

[isladogs]

Remove the semicolon at the end of line 2

 

[Cark]

Hi isladogs,

I had previously tried that, but it just changed the Run-time error message to Run-time error '3071' This expression is typed incorrectly, or it is too complex to be evaluated. For example, a numeric expression may contain too many complicated elements. Try simplifying the expression by assigning parts of the expression to variables.

Any further thoughts?

 

[isladogs]

You definitely need to remove the semicolon
I would replace the date expression >= … and <= with Between ...And.
Use date delimiters in the SQL.
Your second code sample has an extra IN clause not in the first code
Check your bracketing has matched pairs

 

[MajP]

There is no way that vba makes the below string, you must be missing something that does the ATA2DIGIT

more readable

SELECT     tbltcaog.id, 
           tbltcaog.DATE, 
           tbltcaog.stn, 
           tbltcaog.reg, 
           tbltcaog.ata, 
           tbltcaog.[ata2digit description], 
           tbltcaog.ata4digit, 
           tbltcaog.ata2digit, 
           tbltcaog.atalast2, 
           tbltcaog.[reason for aog], 
           tbltcaog.[aog hrs], 
           tbltcaog.[aog mins], 
           tbltcaog.aogtime, 
           tbltcaog.aogtotalmins, 
           tbltcaog.[event type], 
           tbltcaog.[severity index], 
           tbltcaog.[dispatched iaw mel], 
           tbltcaog.[mel reference], 
           tbltcaog.[etops sector], 
           tbltcaog.[charter flight], 
           tblactype.actypeid, 
           tblactype.manufacturer, 
           tblactype.type 
FROM       (tblactype 
INNER JOIN tblacrft 
ON         tblactype.[actypeid] = tblacrft.[modellink]) 
INNER JOIN tbltcaog 
ON         tblacrft.[reg] = tbltcaog.[reg];WHERE ( 
  ( 
    tbltcaog.DATE 
  ) 
  >=#04/11/2015# AND 
  ( 
    tbltcaog.DATE 
  ) 
  <=#04/11/2019# AND 
  ( 
    ( 
      tbltcaog.aogtotalmins 
    ) 
    >=[forms]![FrmRptCriteriaAOG]![AOG_Time] 
  ) 
) 
AND tbltcaog.[ATA2DIGIT] IN (0,20,21,12,11,10,09,08,07,06,05,24,25,26,27,28,29,30,31,32,33,22,23,34,35,36,38,47,46,49,51,52,53,54,55,56,57,70,71,72,73,74,75,76,77,78,79,80,01,02,03,04)

If you search this you will find the problem ";". Please use a free SQL formatter when posting.

 

[plog]

tbltcaog.[ATA2DIGIT] IN (0,20,21,12,11,10,09,08,07,06,05,24,25,26,27,28,29,30,31,32,33,22,23,34,35,36,38,47,46,49,51,52,53,54,55,56,57,70,71,72,73,74,75,76,77,78,79,80,01,02,03,04)

Where'd that come from? That's not in your 'Original SQL Output in VBA'

Second, its not correct. You don't have anything in the IN surrounded by quotes, which means they are to be compared as numbers. However, 05 isn't a number. What datatype is ATA2DIGIT?

Third, that's a lot of values. IF they all share the same characteristic it might be better to have them in a table and related there. Then instead of explicitly listing them in the query, you just do an INNER JOIN.


And definitely the semi colon.

 

[Cark]

I am very much an amateur and still learning how to use Access and inherited a lot of this code from a previous project. This is the full Export Button Code for the Export Report to Excel.

ATA2DIGIT is a calculated field which comes from ATA and is calculated by using Left([ATA],2) and the result type is Integer with Format 00 with auto decimal places.

Private Sub Export_Click()

    Dim ctl As Control
    Dim sFilter As String
    Dim sContName As String

    sFilter = ""
    sFilter = "AND TblTcAOG.[ATA2DIGIT] IN (0"
    For Each ctl In Controls

        If ctl.Tag = "ATA" Then
            sContName = Right(ctl.Name, 2)
            'Debug.Print sContName
              'Once you have this working listing each control now interrogate the value
            If ctl.Value = True Then
                sFilter = sFilter & "," & sContName
            End If
        End If
    Next ctl
    sFilter = sFilter & ")"
    Debug.Print sFilter
  
    Dim dbsCurrent      As Database
    Dim SQL_Name        As QueryDef
    Dim SQL_Output      As String
    Dim dStart          As String
    Dim dEnd            As String

    Set dbsCurrent = CurrentDb
    Set SQL_Name = dbsCurrent.QueryDefs("qry_AOGreport_criteria")

    dStart = "#" & Format([Forms]![FrmRptCriteriaAOG]![Beg_date_txt], "mm/dd/yyyy") & "#"
    dEnd = "#" & Format([Forms]![FrmRptCriteriaAOG]![End_Date_txt], "mm/dd/yyyy") & "#"


    SQL_Output = "SELECT TblTcAOG.ID, TblTcAOG.Date, TblTcAOG.STN, TblTcAOG.REG, TblTcAOG.ATA, TblTcAOG.[ATA2Digit Description], TblTcAOG.ATA4DIGIT, TblTcAOG.ATA2DIGIT, TblTcAOG.ATALAST2, TblTcAOG.[REASON FOR AOG], TblTcAOG.[AOG HRS], TblTcAOG.[AOG MINS], TblTcAOG.AOGTime, TblTcAOG.AOGTotalMins, TblTcAOG.[Event Type], TblTcAOG.[Severity Index], TblTcAOG.[Dispatched IAW MEL], TblTcAOG.[MEL Reference], TblTcAOG.[ETOPS Sector], TblTcAOG.[Charter Flight], tblactype.actypeid, tblactype.Manufacturer, tblactype.Type " & _
                 "FROM (tblactype INNER JOIN TblAcrft ON tblactype.[actypeid] = TblAcrft.[ModelLink]) INNER JOIN TblTcAOG ON TblAcrft.[Reg] = TblTcAOG.[REG] " & _
                 "WHERE ((TblTcAOG.Date)>=" & dStart & " And (TblTcAOG.Date)<=" & dEnd & " AND ((TblTcAOG.AOGTotalMins)>=[forms]![FrmRptCriteriaAOG]![AOG_Time])) "
                         
    Debug.Print SQL_Output & sFilter
    SQL_Name.SQL = SQL_Output & sFilter
    
    DoCmd.Echo False
    
    If Dir("C:\Users\tkorynek\Downloads\qry_AOGs_Export.xlsx") <> "" Then
        Kill "C:\Users\tkorynek\Downloads\qry_AOGs_Export.xlsx"
    End If
    
    DoCmd.OpenQuery "qry_AOGs_Export"
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "qry_AOGs_Export", "C:\Users\Downloads\qry_AOGs_Export.xlsx", True
    Call FormatExcelExport("C:\Users\Downloads\qry_AOGs_Export.xlsx")
    DoCmd.Close acQuery, "qry_AOGs_Export"
    DoCmd.Echo True
    
    Set dbsCurrent = Nothing
    Set SQL_Name = Nothing
    
End Sub

 

[plog]

First rule of code club---make something work. Depending on your definition of football:

You don't throw to the endzone on every play
You don't shoot from midfield on every possession

Work the ball up the field until you reach the goal. So, yank all the criteria out of your query. Comment out the WHERE, don't put in the sFilter. Make SQL_Output just contain the SELECT, FROM and INNER JOINs. Then run the thing and see if it exports--who cares if its not the right data--just get soemthing to spit out.

Once you have it doing something correctly, add in your criteria one by one. That way, you know what is breaking it.

With that said, this is wrong:

ATA2DIGIT is a calculated field which comes from ATA and is calculated by using Left([ATA],2) and the result type is Integer with Format 00 with auto decimal places.

Left(https://www.techonthenet.com/access/functions/string/left.php) spits out a string. Format(https://www.techonthenet.com/access/functions/string/format.php) spits out a string. Ergo, ATA2DIGIT is a string and everything in the IN needs to be surrounded by single quotes:

...'0', '20', '21'...

But work on that later, just make that thing work and build from there.

 

[Cark]

Thanks for the advice plog, I have been trying to pick it all apart and go back to basics to get something to spit out, but the error messages keep flying my way and keep changing each time making me think quite a bit is wrong in my code.

I already have a similar bit of code to the one I previously posted which uses the same filtering logic for creating sFilter and that does not have single quotes around the 05,06,07 etc and that works perfectly when it comes to filtering the exports and exporting to Excel.

Based on your advice I will amend the code to include quotes to help with troubleshooting.

Sometimes I get errors saying the record set is not updateable and other times I get runtime error 31532 where I am unable to export to Excel.

How would you advise I start from scratch? I have been told that I should be using 2 queries. The first query called qry_AOGreport_criteria to check the data and then qry_AOGs_Export to use to export to Excel. I am trying to understand what the purpose of having these two queries is. Anyone have a clue? Appreciate it is the kind of question that I should already know the answer to seeing as though it's my project, but I really am an amateur trying to learn.

 

[plog]

I didn't say you had to start from scratch. I said you had to make something simple work and then add complexity to it until it does what you want.

I believe the goal is to have a query spit out data to excel. I would make this the SQL:


SELECT TblTcAOG.ID FROM tblTcAOG.ID

Get that to spit out to excel. Then work on making the query produce the data you want.

 

[Cronk]

When I have trouble with a sql string that I've type directly into vba, I put a debug.print strSQL line immediately after the "strSQL = " and paste the string into the query design SQL window.
Access will tell you what if anything is wrong with the sql.

 

[JHB]

Try to avoid references to a form value in the SQL-string.

  Dim TheAOG_Time As [B][COLOR=Red]Long[/COLOR][/B] ' ([B][COLOR=red]I don't know if it is the correct variable type, else change it[/COLOR][/B])
  
  [B][COLOR=red]TheAOG_Time = [Forms]![FrmRptCriteriaAOG]![AOG_Time][/COLOR][/B]
  
  dStart = "#" & Format([Forms]![FrmRptCriteriaAOG]![Beg_date_txt], "mm/dd/yyyy") & "#"
  dEnd = "#" & Format([Forms]![FrmRptCriteriaAOG]![End_Date_txt], "mm/dd/yyyy") & "#"


  SQL_Output = "SELECT TblTcAOG.ID, TblTcAOG.Date, TblTcAOG.STN, TblTcAOG.REG, TblTcAOG.ATA, TblTcAOG.[ATA2Digit Description], TblTcAOG.ATA4DIGIT, TblTcAOG.ATA2DIGIT, TblTcAOG.ATALAST2, TblTcAOG.[REASON FOR AOG], TblTcAOG.[AOG HRS], TblTcAOG.[AOG MINS], TblTcAOG.AOGTime, TblTcAOG.AOGTotalMins, TblTcAOG.[Event Type], TblTcAOG.[Severity Index], TblTcAOG.[Dispatched IAW MEL], TblTcAOG.[MEL Reference], TblTcAOG.[ETOPS Sector], TblTcAOG.[Charter Flight], tblactype.actypeid, tblactype.Manufacturer, tblactype.Type " & _
               "FROM (tblactype INNER JOIN TblAcrft ON tblactype.[actypeid] = TblAcrft.[ModelLink]) INNER JOIN TblTcAOG ON TblAcrft.[Reg] = TblTcAOG.[REG] " & _
               "WHERE ((TblTcAOG.Date)>=" & dStart & " And (TblTcAOG.Date)<=" & dEnd & " AND ((TblTcAOG.AOGTotalMins)>=[B][COLOR=Red]" & [/COLOR][COLOR=red]TheAOG_Time[/COLOR][/B] [B][COLOR=red]& "[/COLOR][/B])) "

 

[CJ_London]

or separate into a different element of the string creation

SQL_Output = "SELECT TblTcAOG.ID, TblTcAOG……..

…..AND ((TblTcAOG.AOGTotalMins)>=#" & [Forms]![FrmRptCriteriaAOG]![AOG_Time] & "#)) "

All date elements when presented as a string need to be in US format of mm/dd/yyyy

 

[Cark]

As an update to my previous posts:

I have now isolated the export issue to the sFilter term where I am pulling a load of Option Button Values to apply as a filter in the SQL. This sFilter term works perfectly on another Form, but for some reason in this Form it is giving me issues (I appreciate the SQL for the two exports in the two Forms are different, but I haven't been able to spot the difference other than field names as the field formatting in the two tables for ATA2DIGIT are exactly the same - I think). I have managed to identify sFilter as the root cause by using a single quote infront of sFilter and the export runs perfectly.

When I have the ATA2DIGIT Option Button Values surrounded by quotes like ' I get the following:
The error I am currently getting when I have the sFilter term activated is: Run-time error 3071 This expression is typed incorrectly, or it is too complex to be evaluated.

When I don't have the quotes:

Run-time error 3326 This recordset is not updateable.

Any ideas? Also where do I find a free SQL formatter?

The code for sFilter is:

    sFilter = ""
    sFilter = "AND TblTcAOG.[ATA2DIGIT] IN (0"
    For Each ctl In Controls

        If ctl.Tag = "ATA" Then
            sContName = Right(ctl.Name, 2)
            'Debug.Print sContName
              'Once you have this working listing each control now interrogate the value
            If ctl.Value = True Then
                sFilter = sFilter & "," & sContName
            End If
        End If
    Next ctl
    sFilter = sFilter & ")"
    Debug.Print sFilter

The full SQL is:

SELECT TblTcAOG.ID, TblTcAOG.Date, TblTcAOG.STN, TblTcAOG.REG, TblTcAOG.ATA2DIGIT, TblTcAOG.AOGTotalMins FROM tblactype INNER JOIN (TblAcrft INNER JOIN (TblTcAOG INNER JOIN TblAirports ON TblTcAOG.STN = TblAirports.STN) ON TblAcrft.Reg = TblTcAOG.REG) ON tblactype.actypeid = TblAcrft.ModelLink WHERE ((TblTcAOG.Date)>=#04/15/2017# And (TblTcAOG.Date)<=#04/15/2019# AND ((TblTcAOG.AOGTotalMins)>=[forms]![FrmRptCriteriaAOG]![AOG_Time])) AND TblTcAOG.[ATA2DIGIT] IN (0,20,21,12,11,10,09,08,07,06,05,24,25,26,27,28,29,30,31,32,33,22,23,34,35,36,38,47,46,49,51,52,53,54,55,56,57,70,71,72,73,74,75,76,77,78,79,80,01,02,03,04)

 

[The_Doc_Man]

Regarding:

Run-time error 3326 This recordset is not updateable.

The article referenced here gives you a pretty solid description of why a given SELECT query might not be updatable. At least two of Allen's reasons involve JOIN queries and are worth a look.

http://allenbrowne.com/ser-61.html

 

[Cark]

I took a look at that allenbrowne link and am unable to figure out how the sFilter term is causing the query / the code to break.

I am completely stumped. I even tried taking it back to basics and slowly building up from SELECT TblTcAOG.ID FROM TblTcAOG.ID as suggested and got to the following:

SQL_Output = 
"SELECT TblTcAOG.[ID] " & _
"FROM tblactype INNER JOIN (TblAcrft INNER JOIN (TblTcAOG INNER JOIN TblAirports ON TblTcAOG.STN = TblAirports.STN) ON TblAcrft.Reg = TblTcAOG.REG) ON tblactype.actypeid = TblAcrft.ModelLink "

Which worked fine as I had the export step doing:

SELECT TblTcAOG.ID, TblTcAOG.ATA2DIGIT, TblTcAOG.Date
FROM TblTcAOG INNER JOIN qry_AOGreport_criteria ON TblTcAOG.ID = qry_AOGreport_criteria.ID;

However as soon as I added sFilter to the SQL_Output it broke...

The below is SQL_Name.SQL = SQL_Output & sFilter which gives me the Recordset cannot be updated error. Is it a join that is not set up correctly?

SELECT TblTcAOG.[ID] FROM tblactype INNER JOIN (TblAcrft INNER JOIN (TblTcAOG INNER JOIN TblAirports ON TblTcAOG.STN = TblAirports.STN) ON TblAcrft.Reg = TblTcAOG.REG) ON tblactype.actypeid = TblAcrft.ModelLink WHERE TblTcAOG.[ATA2DIGIT] IN (0,20,21,12,11,10,09,08,07,06,05,24,25,26,27,28,29,30,31,32,33,22,23,34,35,36,38,47,46,49,51,52,53,54,55,56,57,70,71,72,73,74,75,76,77,78,79,80,01,02,03,04)

 

[essaytee]

...snip...
The below is SQL_Name.SQL = SQL_Output & sFilter which gives me the Recordset cannot be updated error. Is it a join that is not set up correctly?

SELECT TblTcAOG.[ID] FROM tblactype INNER JOIN (TblAcrft INNER JOIN (TblTcAOG INNER JOIN TblAirports ON TblTcAOG.STN = TblAirports.STN) ON TblAcrft.Reg = TblTcAOG.REG) ON tblactype.actypeid = TblAcrft.ModelLink WHERE TblTcAOG.[ATA2DIGIT] IN (0,20,21,12,11,10,09,08,07,06,05,24,25,26,27,28,29,30,31,32,33,22,23,34,35,36,38,47,46,49,51,52,53,54,55,56,57,70,71,72,73,74,75,76,77,78,79,80,01,02,03,04)

Can you replace this part

WHERE TblTcAOG.[ATA2DIGIT] IN (0,20,21,

with a simple WHERE condition such as:

WHERE TblTcAOG.[ATA2DIGIT] = 5);"

Does this work? If it does, then it's something to do with the IN clause.

 

[isladogs]

I just tested by creating a similar query in one of my own databases:

SELECT PupilData.*
FROM (PupilData INNER JOIN tblEthnicityCodes ON PupilData.Ethnicity = tblEthnicityCodes.[Ethnicity Code]) INNER JOIN SEN_Stages ON PupilData.COPLevel = SEN_Stages.COPStage
WHERE (((PupilData.PupilID) In ("12599","12469", "13251")));

NOTE:
I used quotes in the IN clause as PupilID is a text field
My query was editable. I don't have time to test with 40 odd values but I see no reason why it would be different

However look at the Access specifications in case something is being breached

I suggest you paste your code into a query as well
Allow Access to add all the WHERE brackets or add them as above.
Omit the quotes as ATA2DIGIT appears to be a number field
Can you edit that query?
If not remove almost all the IN clause values & try again. Any difference?
If the query is editable but you still get the error when exporting to Excel, I would follow previous advice e.g. create a reference table for your IN values

 

[Cark]

After reading essaytee's suggestion I had a go at using a 'simple' where condition of (with 36 being an ATA2DIGIT that I knew would be a common one to be exported):

WHERE TblTcAOG.[ATA2DIGIT] = 36);"

This gave the "Recordset unupdateable error"

I then tried with quotes and it gave a "data type mismatch".
I then tried without quotes and without the semi-colon and "Recordset unupdateable" came back.
I then tried with quotes and without the semi-colon and it said "data type mismatch".

When I look at the qry_AOGreport_criteria which is defined as:

SELECT TblTcAOG.ID
FROM tblactype INNER JOIN (TblAcrft INNER JOIN (TblTcAOG INNER JOIN TblAirports ON TblTcAOG.STN = TblAirports.STN) ON TblAcrft.Reg = TblTcAOG.REG) ON tblactype.actypeid = TblAcrft.ModelLink
WHERE (((TblTcAOG.ATA2DIGIT)=36));

It works and shows me just the ID's where ATA2DIGIT = 36.

When I look at the qry_AOGs_Export query which is defined as:

SELECT TblTcAOG.ID, TblTcAOG.ATA2DIGIT
FROM TblTcAOG INNER JOIN qry_AOGreport_criteria ON TblTcAOG.ID = qry_AOGreport_criteria.ID;

This shows me the ID's and ATA2DIGIT columns where ATA2DIGIT = 36 just as I expect it to work. However, this does not want to export correctly into Excel and I am stumped as to why. Any ideas?

 

[isladogs]

You still didn't say whether the query itself is editable.

Long shot but try changing from SELECT to SELECT DISTINCTROW
Also you don't need the [] around ATA2DIGIT.