VBA grammar for sum of two fields in SQL table

albertan

Board Regular
Joined
Nov 16, 2014
Messages
66
Office Version
  1. 365
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
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
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
 
Upvote 0
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:
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
Don't use the Array function there. Just use:

Code:
.CommandText = "SELECT...."
 
Last edited:
Upvote 0
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:
Upvote 0
As I said, you don't need Array at all. And yes, each element is limited to 255 characters.
 
Upvote 0
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?
 
Upvote 0
Not that I am aware of.
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top