VBA grammar for sum of two fields in SQL table

[albertan]

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]

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]

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.

 

[Fazza]

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]

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]

Don't use the Array function there. Just use:

.CommandText = "SELECT...."

 

[albertan]

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

 

[RoryA]

As I said, you don't need Array at all. And yes, each element is limited to 255 characters.

 

[albertan]

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]

Not that I am aware of.