Printable Version of Topic

Click here to view this topic in its original format

UtterAccess Forums _ Access Modules _ Syntax Error SQL Inside Vba

Posted by: g0049978 Sep 20 2019, 04:06 PM

Anyone know what my syntax error could be? The 0 is an integer field - this was copied directly from the SQL view of the query in Access:

DoCmd.RunSQL "INSERT INTO FinalTotalsbyDate ( Source, [CountOfIntake ID], SumOfMatch, ReportDateTime )" _
& "SELECT Open6pm.Source, Count(Open6pm.[Intake ID]) AS [CountOfIntake ID], Sum(Open6pm.Match) AS SumOfMatch, Open6pm.ReportDateTime" _
& "FROM Open6pm LEFT JOIN FinalTotalsbyDate ON Open6pm.ReportDateTime = FinalTotalsbyDate.ReportDateTime" _
& "WHERE (((FinalTotalsbyDate.ReportDateTime) <> [Open6pm]![ReportDateTime]))" _
& "GROUP BY Open6pm.Source, Open6pm.ReportDateTime" _
& "HAVING (((Sum(Open6pm.Match))>0));"

Posted by: DanielPineault Sep 20 2019, 04:09 PM

Well, for one thing you have no space when you concatenate your lines together, so try

CODE
DoCmd.RunSQL "INSERT INTO FinalTotalsbyDate ( Source, [CountOfIntake ID], SumOfMatch, ReportDateTime ) " _
& "SELECT Open6pm.Source, Count(Open6pm.[Intake ID]) AS [CountOfIntake ID], Sum(Open6pm.Match) AS SumOfMatch, Open6pm.ReportDateTime " _
& "FROM Open6pm LEFT JOIN FinalTotalsbyDate ON Open6pm.ReportDateTime = FinalTotalsbyDate.ReportDateTime " _
& "WHERE (((FinalTotalsbyDate.ReportDateTime) <> [Open6pm]![ReportDateTime])) " _
& "GROUP BY Open6pm.Source, Open6pm.ReportDateTime " _
& "HAVING (((Sum(Open6pm.Match))>0));"

Posted by: g0049978 Sep 20 2019, 04:16 PM

Yep - that did it! Thank you - learning something new every day!!

Posted by: DanielPineault Sep 20 2019, 05:20 PM

You may like to look over http://allenbrowne.com/ser-71.html it is worth creating the form suggested by the author for this type of exact scenario! Save you some time and frustration the next time.

Posted by: g0049978 Sep 23 2019, 01:47 PM

Thank you! That's a great time saving idea:)