Error in FROM clause (1 Viewer)

[tmyers]

Public Sub GrandTotal()

    Dim total_lines As Integer
    Dim num_lines_to_create As Integer
    Dim source As String
    Dim grand_total As Double
   
    'calculate the total service
    grand_total = DSum("[Extended]", "CreateQuoteQry")
   
    'get total records from query/table
    total_lines = DCount("1", "CreateQuoteQry")
    'calculate extra lines to pad
    num_lines_to_create = LINES_PER_PAGE - (total_lines Mod LINES_PER_PAGE) - 1
   
    'create Union query
    source = "SELECT [SelectBox],[TypeName],[Quantity], [Manufacturer], [Part], [Comments], [Unit Price], [Extended], [JobID], [LampType], [NumberOfLamps], [LED] FROM CreateQuoteQry" & _
                "UNION ALL " & _
                "SELECT TOP " & num_lines_to_create & " 99991,NULL, NULL, NULL, NULL FROM CreateQuoteQry " & _
                "UNION ALL " & _
                "SELECT TOP 1 99999, NULL, NULL, 'Grand Total', " & grand_total & " FROM CreateQuoteQry;"
    Reports![Quoterpt].Report.RecordSource = source

End Sub

The above code was supplied by the courtesy of @arnelgp in an example DB he posted in another one of my threads.

I have never used a union query before, so am unsure if I modified it correctly. CreateQuoteQry is the control source behind my report. When I try using this code, I get a syntax error due to issue with the FROM clause when opening the report. When I comment out this module, the report opens. So the query CreateQuoteQry itself is fine.

Did I maybe need to increase the amount of times it calls NULL? I could certainly use the help of someone who better understands what the union is doing.

 

[theDBguy]

Hi. One way to troubleshoot something like this is to use Debug.Print to display the SQL statement in the Immediate Window and then copy and paste the result in the query designer. Access should then point you somewhere close to where the problem is.

 

[tmyers]

I just tried that, and while it threw the same error it didn't highlight/point me to the possible problem.
Here is what it printed out.

SELECT [SelectBox],[Type],[Quantity], [Manufacturer], [Part], [Comments], [Unit Price], [Extended], [JobID], [LampType], [NumberOfLamps], [LED]
FROM CreateQuoteQry
UNION ALL SELECT TOP 23 99991,NULL, NULL, NULL, NULL, FROM CreateQuoteQry
UNION ALL SELECT TOP 1 99999, NULL, NULL, 'Grand Total', 1368.18181777457
FROM CreateQuoteQry;

 

[theDBguy]

I just tried that, and while it threw the same error it didn't highlight/point me to the possible problem.
Here is what it printed out.

SELECT [SelectBox],[Type],[Quantity], [Manufacturer], [Part], [Comments], [Unit Price], [Extended], [JobID], [LampType], [NumberOfLamps], [LED]
FROM CreateQuoteQry
UNION ALL SELECT TOP 23 99991,NULL, NULL, NULL, NULL, FROM CreateQuoteQry
UNION ALL SELECT TOP 1 99999, NULL, NULL, 'Grand Total', 1368.18181777457
FROM CreateQuoteQry;

So, you copy and paste that in the Query Designer; and when you see the error message and click OK, take a screenshot of the SQL statement again. The cursor should be close to where the complaint was.

 

[tmyers]

So, you copy and paste that in the Query Designer; and when you see the error message and click OK, take a screenshot of the SQL statement again. The cursor should be close to where the complaint was.

Turns out the FROM clause error was due to the lack of a space.
I fixed that, however now I am getting " the SELECT statement includes a reserved word or an argument name that is misspelled or missing, or the punctuation is incorrect."

 

[theDBguy]

Turns out the FROM clause error was due to the lack of a space.
I fixed that, however now I am getting " the SELECT statement includes a reserved word or an argument name that is misspelled or missing, or the punctuation is incorrect."

And you don't get any indication of the location of the issue with the cursor placement?

 

[tmyers]

And you don't get any indication of the location of the issue with the cursor placement?

Sadly no.
I think I see the issue however. I will let you know!

 

[arnelgp]

I think the issue is with the Number of Columns you have on the first select.
the 2 Union should have exactly same column:

Source = "SELECT [SelectBox],[TypeName],[Quantity], [Manufacturer], [Part], [Comments], [Unit Price], [Extended], [JobID], [LampType], [NumberOfLamps], [LED] FROM CreateQuoteQry" & _
                "UNION ALL " & _
                "SELECT TOP " & num_lines_to_create & " 99991, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL FROM CreateQuoteQry " & _
                "UNION ALL " & _
                "SELECT TOP 1 99999, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 'Grand Total', " & grand_total & " FROM CreateQuoteQry;"

 

[tmyers]

I think the issue is with the Number of Columns you have on the first select.
the 2 Union should have exactly same column:

Source = "SELECT [SelectBox],[TypeName],[Quantity], [Manufacturer], [Part], [Comments], [Unit Price], [Extended], [JobID], [LampType], [NumberOfLamps], [LED] FROM CreateQuoteQry" & _
                "UNION ALL " & _
                "SELECT TOP " & num_lines_to_create & " 99991, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL FROM CreateQuoteQry " & _
                "UNION ALL " & _
                "SELECT TOP 1 99999, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 'Grand Total', " & grand_total & " FROM CreateQuoteQry;"

Yup! It was that along with a missing space causing a syntax issue. I managed to get it working. Thank you for allowing me to use it!