Sum in VBA

[BadgerLikeSpeed]

I hope there is a very simple answer to this (as with all the questions I have!)...
So here is my code:

 Dim FuelCal As String
 Dim FuelGel As String
 Dim FuelHav As String
 Dim FuelKil As String
 Dim FuelLin As String
 Dim FuelMus As String
 Dim FuelRam As String
 Dim FuelWar As String
 Dim FuelWtn As String
 Dim FuelWat As String
 Dim FuelAll As String
 
 FuelCal = DSum("[GOUsed]", "EngUseCal")
 FuelGel = DSum("[GOUsed]", "EngUseGel")
 FuelHav = DSum("[GOUsed]", "EngUseHav")
 FuelKil = DSum("[GOUsed]", "EngUseKil")
 FuelLin = DSum("[GOUsed]", "EngUseLin")
 FuelMus = DSum("[GOUsed]", "EngUseMus")
 FuelRam = DSum("[GOUsed]", "EngUseRam")
 'FuelWar = DSum("[GOUsed]", "EngUseWarr")
 FuelWtn = DSum("[GOUsed]", "EngUseWtn")
 FuelWat = DSum("[GOUsed]", "EngUseWat")
 FuelAll = DSum("[GOUsed]", "EngUseCal") + DSum("[GOUsed]", "EngUseGel") + DSum("[GOUsed]", "EngUseHav") _
 + DSum("[GOUsed]", "EngUseKilroom") + FuelLin = DSum("[GOUsed]", "EngUseLin") + DSum("[GOUsed]", "EngUseMus") _
 + DSum("[GOUsed]", "EngUseRam") + DSum("[GOUsed]", "EngUseWtn") + DSum("[GOUsed]", "EngUseWat")
 
 'Fill Report
 Me.JobsSHookBox = JobSHook
 Me.OilJobBox = JobOil
 Me.DragonJobBox = JobDragon
 Me.OtherJobBox = JobOther
 Me.AllFuelBox = FuelAll
 Me.FuelCalBox = FuelCal
 Me.FuelHavBox = FuelHav
 Me.FuelRamBox = FuelRam
 Me.FuelKilBox = FuelKil
 Me.FuelLinBox = FuelLin
 Me.FuelWtnBox = FuelWtn
 Me.FuelMusBox = FuelMus
 Me.FuelGelBox = FuelGel
 Me.FuelWatBox = FuelWat
 'Me.FuelWarBox = 0

What I want is to get the sum of the fuel used in teh AllFuelBox textbox. Currently I get 'False'... If I write =FuelCal + FuelHav etc in the data field it writes the numbers one after another. As in not a sum, just the 1st value, then 2nd and so on.
Have I somehow changed the figures from numbers to text? I'm a novice at this vba thing (as I may have mentioned before!), so any help would be appreciated. I've tried googling, but nothing seemed to be of use.
Thanks

 

[pwbrown]

It's because your variables are Strings - text only
Change String to Integer where you want to hold numbers.

 

[Rx_]

pwbrown is right, the strings are not recognized by SQL.
Here is an example, it doesn't use DSUM, but uses SUM and MAX:

90    strSQLED_EMWellSumRpt = ""
100   strSQLED_EMWellSumRpt = strSQLED_EMWellSumRpt & "SELECT AA12MoReport.ID_Wells, AA12MoReport.[Well Name], Sum(AA12MoReport.[Tank Throughput]) AS [Tank Throughput], Sum(AA12MoReport.UnControlled) AS [Un Controlled],  "
110   strSQLED_EMWellSumRpt = strSQLED_EMWellSumRpt & " Sum(AA12MoReport.[Actual Controlled]) AS [Actual Controlled], First(AA12MoReport.Controlled) AS Controlled, Max(AA12MoReport.Efficiency) AS Efficiency, "
120   strSQLED_EMWellSumRpt = strSQLED_EMWellSumRpt & " Max(AA12MoReport.[Tank Updated]) AS [Tank Updated], Sum(AA12MoReport.[Flare Throughput]) AS [Flare Throughput], Sum(AA12MoReport.NOx) AS NOx, Sum(AA12MoReport.CO) AS CO, "
130   strSQLED_EMWellSumRpt = strSQLED_EMWellSumRpt & " Sum(AA12MoReport.VOC) AS VOC, Max(AA12MoReport.[BTU Rating]) AS [BTU Rating], Max(AA12MoReport.MolWeight) AS [Mol Weight], Max(AA12MoReport.VOCWeight) AS [VOC Weight], "
140   strSQLED_EMWellSumRpt = strSQLED_EMWellSumRpt & " Max(AA12MoReport.[Flare Updated]) AS [Flare Updated], Max(AA12MoReport.[Well Status]) AS [Well Status] "
150   strSQLED_EMWellSumRpt = strSQLED_EMWellSumRpt & " FROM AA12MoReport "
160   strSQLED_EMWellSumRpt = strSQLED_EMWellSumRpt & " GROUP BY AA12MoReport.ID_Wells, AA12MoReport.[Well Name] "
170   strSQLED_EMWellSumRpt = strSQLED_EMWellSumRpt & " ORDER BY AA12MoReport.ID_Wells; "

In the code window press Control+G to display the Immediate Window
under your code:
FuelCal = DSum("[GOUsed]", "EngUseCal")
add this line of code:
Debug.Print "GoUsed is: " & "[GOUsed]"
Debug.Print "FuelCal is: " & DSum("[GOUsed]", "EngUseCal")

When the code executes - the immediate window will show you that GoUsed is a string - not the number you were expecting.
Anyone new to coding will greatly benefit by using the immediate window and a few debug statements.
My estimate is that it will increase learning by four times and decrease frustration by ten beers a week.

 

[BadgerLikeSpeed]

That sorted it. Thanks.
I knew it was something basic, but couldn't find out what.