Member Avatar for mikeybware

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
  • 227 Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by kevoxo
Member Avatar for kevoxo

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.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.