VBA grammar for sum of two fields in SQL table

albertan

New Member
Joined
Nov 16, 2014
Messages
34
I have an SQL statement that sums up two fields and is working find in SQL

Select w.Forecasted_hours + w.actual_hours as 'TotalForecast'
however in VBA I'm not exactly sure what would be the right way to do it. If I paste the SQL it seem like not recognizing a plus sign.
Can anybody please help?
I'm looking at this website but couldn't figure out what is the best solution
https://stackoverflow.com/questions/45723473/sum-and-group-vba-sheet

Thanks
 

Fazza

MrExcel MVP
Joined
May 17, 2006
Messages
9,186
should be no problem with a plus sign
why do you think that is the problem?

what is the full SQL?? Without knowing, here are some ideas for a more robust syntax. SELECT (onefield + another_and_enclose_the_fields_summed_by_parentheses) AS [new field name in brackets is more robust]
Good luck
 

albertan

New Member
Joined
Nov 16, 2014
Messages
34
the full SQL statement is very standard (Select w.project_number, w.Forecasted_hours + w.actual_hours as 'TotalForecast' from dbo.Database as w
where project number is like 'P-100%'

When I put the statement in VBA, I put it within
.CommandText = Array("SELECT....) I'm getting Runtime error 5 (invalid procedure or call argument). The code works in SQL query with this field and works in VBA without this field. Something must be wrong with syntax. I did not quite understand your suggestion.
 
Last edited:

Fazza

MrExcel MVP
Joined
May 17, 2006
Messages
9,186
in SQL I'm familiar with, AS is not normal syntax for aliasing table names. try changing "as w" to "w"
Also, the field name in the WHERE clause has a space. This won't work
There is a leading parenthesis: I don't know what you're doing.
I don't know understand what you're describing wrt error 5. It doesn't make sense to me.
if you're getting errors I can't help without seeing the SQL. Such as it debug prints.
If you don't understand my descriptions & I don't know exactly what you're doing then I can't help much. Maybe someone else can. good luck
 

albertan

New Member
Joined
Nov 16, 2014
Messages
34
I have 2 fields in SQL: w.Forecasted_hours and w.actual_hours
I need to sum them up and name a new field. In SQL it works if i simply add them together with plus sign (i.e. "w.Forecasted_hours + w.actual_hours as 'TotalForecast' from dbo.Database")
It works well in SQL but not in VBA.

Maybe someone else can help. Thanks
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
34,146
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
Don't use the Array function there. Just use:

Code:
.CommandText = "SELECT...."
 
Last edited:

albertan

New Member
Joined
Nov 16, 2014
Messages
34
Thanks all, I think I was able to figure out. I used the format as shown below and I think there's a capacity constraint in each element of Array formula. I had to split it further and it worked.



.CommandText = Array(" text "," Text2 "," text3 "," Text4 ")
 
Last edited:

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
34,146
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
As I said, you don't need Array at all. And yes, each element is limited to 255 characters.
 

albertan

New Member
Joined
Nov 16, 2014
Messages
34
I think .CommandText is limited to 25 lines however if SQL is longer than that I have to use an Array formula and then split it by parts. Am I correct?
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
34,146
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
Not that I am aware of.
 

Forum statistics

Threads
1,077,674
Messages
5,335,603
Members
399,028
Latest member
greyland

Some videos you may like

This Week's Hot Topics

Top