Passing Recordset Variable into SQL - Syntax Error 2

[JStrand]

I'm receiving this syntax error:

Syntax error (missing operator) in query expression
'(((Branch.BranchName)=Atlata NE ) AND
((ChartOfAccounts.ScorecardLine)>0))'

When executing the code below. I believe the syntax error is near the Having Clause where I have inserted my variable from the recordset. If someone could take a quick pass at catching where the syntax error is I would be greatly appreciative. Thank you.
----------------------------------------------------------------

Function ScorecardReports()

Dim strOutputFormat As String
strOutputFormat = "PDF Format (*.pdf)"

Dim strObjectName As String
strObjectName = "BranchScorecardRprt"


'Reference to Current Database
Dim db As Database
Dim qdf As QueryDef

Set db = CurrentDb()
Set qdf = db.QueryDefs("qdf")

'Identify table with names
Dim tbl As TableDef
Set tbl = db.TableDefs("Branch")


'Declare a string variable to hold the branch names
Dim strBranchName As String
strBranchName = ""

'Declare a counter for a loop
Dim i As Integer
i = 0

'Open a recordset against the table with names
Dim rst As Recordset
Set rst = tbl.OpenRecordset

With rst

If .RecordCount > 0 Then 'Verify records even exist first!

.MoveFirst 'Moves to the first position

Do While i <= .RecordCount And Not .EOF

strBranchName = rst!BranchName

Dim StrSQL As String

StrSQL = ""

StrSQL = StrSQL & "SELECT Branch.Division, Branch.DivisionName, Branch.Region, Branch.RegionName, Branch.Branch, Branch.BranchName, TY.Period, TY.Year, "
StrSQL = StrSQL & "TY.Account, TY.Ctr, TY.Type, TY.Description, Sum(TY.[Current Period $]) AS [SumOfCurrent Period $], Sum(TY.[To Date $]) AS YTD, "
StrSQL = StrSQL & "Sum(TY.[Current Period Budget $]) AS [SumOfCurrent Period Budget $], Sum(TY.[To Date Budget $]) AS [SumOfTo Date Budget $], "
StrSQL = StrSQL & "Sum(TY.[Current Period Prior Year $]) AS [SumOfCurrent Period Prior Year $], Sum(TY.[To Date Prior Year $]) AS [SumOfTo Date Prior Year $], "
StrSQL = StrSQL & "ChartOfAccounts.ScorecardLine, ChartOfAccounts.Category, ChartOfAccounts.CategoryName, ReportGroup3.ReportGroup3 INTO BranchMasterTbl "
StrSQL = StrSQL & "FROM ((Branch INNER JOIN TY ON Branch.Branch = TY.Div) INNER JOIN ChartOfAccounts ON TY.Account = ChartOfAccounts.Account) "
StrSQL = StrSQL & "INNER JOIN ReportGroup3 ON (ChartOfAccounts.ScorecardLine = ReportGroup3.ScorecardLine) AND (TY.Ctr = ReportGroup3.Ctr) "
StrSQL = StrSQL & "GROUP BY Branch.Division, Branch.DivisionName, Branch.Region, Branch.RegionName, Branch.Branch, Branch.BranchName, TY.Period, TY.Year, "
StrSQL = StrSQL & "TY.Account, TY.Ctr, TY.Type, TY.Description, ChartOfAccounts.ScorecardLine, ChartOfAccounts.Category, "
StrSQL = StrSQL & "ChartOfAccounts.CategoryName, ReportGroup3.ReportGroup3 "
StrSQL = StrSQL & "HAVING (((Branch.BranchName)= " & strBranchName & " ) AND ((ChartOfAccounts.ScorecardLine)>0)) "
StrSQL = StrSQL & "ORDER BY Branch.Branch, TY.Ctr, ChartOfAccounts.ScorecardLine; "

Debug.Print StrSQL

qdf.SQL = StrSQL

DoCmd.SetWarnings False

DoCmd.OpenQuery "qdf"

DoCmd.SetWarnings True

'Output the reports to the H: Drive

DoCmd.OutputTo acReport, strObjectName, strOutputFormat, "H:\Reports\BranchScorecardRprt" & "-" & strBranchName, False, ""

i = i + 1

.MoveNext

Loop

Else
'Do Nothing

End If
End With

Debug.Print strBranchName

End Function

 

[MazeWorX]

try

"HAVING (((Branch.BranchName)= [red]'[/red]" & strBranchName & "[red]'[/red] ) AND ((ChartOfAccounts.ScorecardLine)>0)) "

Also noted

strSQL = [red]strSQL[/red] & "SELECT Branch.

HTH << MaZeWorX >> "I have not failed I have only found ten thousand ways that don't work" <<Edison>>

 

[JStrand]

Thank You MazeWorX. Code is working great now. I'm not sure what you meant with your also noted comment on strSQL = strSQL & "SELECT Branch. If you could expand on that I would appreciate it. Thanks again!

 

[MazeWorX]

strSQL at that point in the code is an empty string so the first line should read strSQL = "SELECT ...... then every line after that as you build your SQL statement would read strSQL = strSQL & " ....... Your code works because the string was declared as empty but just for arguments sake what if unexpectedly strSQL contained data then your SQL statement would throw an error. In this case its not a big deal but could be an issue.

M

HTH << MaZeWorX >> "I have not failed I have only found ten thousand ways that don't work" <<Edison>>

 

[dhookom]

The query is a little more efficient if you use a WHERE clause rather than HAVING:

    StrSQL = "SELECT Branch.Division, Branch.DivisionName, Branch.Region, Branch.RegionName, Branch.Branch, Branch.BranchName, TY.Period, TY.Year, "
    StrSQL = StrSQL & "TY.Account, TY.Ctr, TY.Type, TY.Description, Sum(TY.[Current Period $]) AS [SumOfCurrent Period $], Sum(TY.[To Date $]) AS YTD, "
    StrSQL = StrSQL & "Sum(TY.[Current Period Budget $]) AS [SumOfCurrent Period Budget $], Sum(TY.[To Date Budget $]) AS [SumOfTo Date Budget $], "
    StrSQL = StrSQL & "Sum(TY.[Current Period Prior Year $]) AS [SumOfCurrent Period Prior Year $], Sum(TY.[To Date Prior Year $]) AS [SumOfTo Date Prior Year $], "
    StrSQL = StrSQL & "ChartOfAccounts.ScorecardLine, ChartOfAccounts.Category, ChartOfAccounts.CategoryName, ReportGroup3.ReportGroup3 INTO BranchMasterTbl "
    StrSQL = StrSQL & "FROM ((Branch INNER JOIN TY ON Branch.Branch = TY.Div) INNER JOIN ChartOfAccounts ON TY.Account = ChartOfAccounts.Account) "
    StrSQL = StrSQL & "INNER JOIN ReportGroup3 ON (ChartOfAccounts.ScorecardLine = ReportGroup3.ScorecardLine) AND (TY.Ctr = ReportGroup3.Ctr) "
    [red]StrSQL = StrSQL & "WHERE Branch.BranchName= """ & strBranchName & """  AND ChartOfAccounts.ScorecardLine>0 "[/red]
    StrSQL = StrSQL & "GROUP BY Branch.Division, Branch.DivisionName, Branch.Region, Branch.RegionName, Branch.Branch, Branch.BranchName, TY.Period, TY.Year, "
    StrSQL = StrSQL & "TY.Account, TY.Ctr, TY.Type, TY.Description, ChartOfAccounts.ScorecardLine, ChartOfAccounts.Category, "
    StrSQL = StrSQL & "ChartOfAccounts.CategoryName, ReportGroup3.ReportGroup3 "
    StrSQL = StrSQL & "ORDER BY Branch.Branch, TY.Ctr, ChartOfAccounts.ScorecardLine; "

Duane
Hook'D on Access
MS Access MVP

 

[JStrand]

Thank You Duane. I appreciate your help. Happy New Year!

 

[JStrand]

Hi Duane, I'm getting the following syntax error:

syntax error (missing operator) in query expression 'Report Group3 .ReportGroup3 Where Branch.BranchName = "Atlanta NE" AND Chart of accounts.ScorecardLine>0'.

when replacing:

StrSQL = StrSQL & "HAVING (((Branch.BranchName)= '" & strBranchName & "' ) AND ((ChartOfAccounts.ScorecardLine)>0)) "

with

StrSQL = StrSQL & "WHERE Branch.BranchName= """ & strBranchName & """ AND ChartOfAccounts.ScorecardLine>0 "

Do you see any syntax issues? Thanks again.

Jim

 

[dhookom]

Can you provide the results from

Debug.Print StrSQL

Did you move the WHERE clause to where I moved it? You can't just replace the HAVING while leaving it in the same location.

Duane
Hook'D on Access
MS Access MVP

 

[MazeWorX]

WHERE and HAVING

use WHERE when you want to exclude records from an aggregate query before the Grouping use HAVING when you want to exclude records after they have been grouped but the calculations have been applied.

WHERE:
SELECT
FROM
WHERE
GROUP BY

HAVING:
SELECT
FROM
GROUP BY
HAVING

or both
SELECT
FROM
WHERE
GROUP BY
HAVING


HTH << MaZeWorX >> "I have not failed I have only found ten thousand ways that don't work" <<Edison>>

 

[JStrand]

OK I it is working with the improved code. Thank you both!

Jim