0

I am trying to sum a field in my MS Access database that I long ago stored as a string.

I was using

dbCommand.CommandText = "SELECT SUM(SubTotal) FROM Glasses WHERE OrderDate >= #" & SearchDateStart & "# AND OrderDate <= #" & SearchDateEnd & "#"

and this would work for awhile ... then for some reason, without changing code, VB decided that it wanted to throw errors on that code. To fix this, I caught the error and ran the following line of code:

dbCommand.CommandText = "SELECT SUM(Val(SubTotal)) FROM Glasses WHERE OrderDate >= #" & SearchDateStart & "# AND OrderDate <= #" & SearchDateEnd & "#"

This too would work for awhile until I recently started getting errors on that line of code as well. I also tried the following lines of code, butthat did no work as well:

dbCommand.CommandText = "SELECT SUM(CAST(SubTotal AS decimal(10,5))) FROM Glasses WHERE OrderDate >= #" & SearchDateStart & "# AND OrderDate <= #" & SearchDateEnd & "#"

and

dbCommand.CommandText = "SELECT SUM(CONVERT(decimal(10,5), SubTotal)) FROM Glasses WHERE OrderDate >= #" & SearchDateStart & "# AND OrderDate <= #" & SearchDateEnd & "#"

If anyone can help, that would be much appreciated.

2
Contributors
1
Reply
9
Views
2 Years
Discussion Span
Last Post by kevoxo
0

Hi I have had similar problem with another vb program. I found the fix was I built a string before the final command then used the string.
hope it works.

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.