UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Syntax Error SQL Inside Vba, Access 2013    
 
   
g0049978
post Sep 20 2019, 04:06 PM
Post#1



Posts: 26
Joined: 13-September 19



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));"
Go to the top of the page
 
DanielPineault
post Sep 20 2019, 04:09 PM
Post#2


UtterAccess VIP
Posts: 6,899
Joined: 30-June 11



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));"

--------------------
Daniel Pineault (2010-2019 Microsoft MVP, UA VIP, EE Distinguished Expert 2018)
Professional Help: https://www.cardaconsultants.com
Free MS Access Code, Tips, Tricks and Samples: https://www.devhut.net

* Design should never say "Look at me". It should always say "Look at this". -- David Craib
* A user interface is like a joke, if you have to explain it, it's not that good! -- Martin LeBlanc


All code samples, demonstration databases, links,... are provided 'AS IS' and are to be used at your own risk! Take the necessary steps to check, validate ...(you are responsible for your choices and actions)
Go to the top of the page
 
g0049978
post Sep 20 2019, 04:16 PM
Post#3



Posts: 26
Joined: 13-September 19



Yep - that did it! Thank you - learning something new every day!!
Go to the top of the page
 
DanielPineault
post Sep 20 2019, 05:20 PM
Post#4


UtterAccess VIP
Posts: 6,899
Joined: 30-June 11



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.

--------------------
Daniel Pineault (2010-2019 Microsoft MVP, UA VIP, EE Distinguished Expert 2018)
Professional Help: https://www.cardaconsultants.com
Free MS Access Code, Tips, Tricks and Samples: https://www.devhut.net

* Design should never say "Look at me". It should always say "Look at this". -- David Craib
* A user interface is like a joke, if you have to explain it, it's not that good! -- Martin LeBlanc


All code samples, demonstration databases, links,... are provided 'AS IS' and are to be used at your own risk! Take the necessary steps to check, validate ...(you are responsible for your choices and actions)
Go to the top of the page
 
g0049978
post Sep 23 2019, 01:47 PM
Post#5



Posts: 26
Joined: 13-September 19



Thank you! That's a great time saving idea:)
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    13th October 2019 - 07:55 PM