Dsum not giving expected results.

[MIkeD666]

tamount = DSum("[Invtotal]", "tblsalesinvoice", "[InvDate] >= #" & [Text150] & "# And " & "[invDate] <= #" & [Text152] & "#")

Hia all, I was wondering if any of you have come across an issue I am having with the code above.
It is simply calculating the total value of the field “Invtotal” between 2 dates. However when I run it I get a figure that it not correct. Nor can I fund where it is getting the figure from.

I ran A simple query as a test to get the “invtotal” with a criteria >= 1-1-19 and <=30-1-19 . this works fine, but when I put it in to the dsum as above I get a complete different value.. Any help, hints or such would be most greatfull AM I missing some thing so simple I cant see it

. Thank you all in advance

Brain dead

 

[Gasman]

What happens if you run the DSum with those hard coded values.?

Are there time elements involved with these dates?

 

[MIkeD666]

hI your points note d about the ashtag

when I run the dsum it given results that are way to high example the return value should be 188 but it returns 838, I check to see if I could find out it got that value. I check the total value of all the date in the invtotal field and that's 1035.

I made a simple query that worked correctly and then changed it to a dsum

mike

 

[MIkeD666]

sorry the are no time elemets in the values just the dates

 

[theDBguy]

Hi. Pardon me for jumping in, but what do you get if you tried the following:


tamount = DSum("[Invtotal]", "tblsalesinvoice", "[InvDate] >= #" & Format(CDate([Text150]),"yyyy-mm-dd") & "# And " & "[invDate] <= #" & Format(CDate([Text152]),"yyyy-mm-dd") & "#")

 

[The_Doc_Man]

Also, just on the practical side, you would have to type less AND clarify the exact intent of the comparison if you used

tamount = DSum( "[InvTotal]", "tblsalesinvoice", "[InvDate] Between #" & _
  Format( CDate( [Text150], "yyyy-mm-dd" ) ) & "# And #" & _
  Format( CDate( [Text152], "yyyy-mm-dd" ) ) & "# " )

theDBguy - check your parenthesis balance. You have nested functions of Format and CDate so need TWO ending parentheses in the expression not counting the one that ends the DSum.

 

[MIkeD666]

Hi WELL TANK YOU FOR THE INPUT I GOT IT WORKING WITHIN 5MINS.
Much appreciated. Thank you

 

[theDBguy]

Also, just on the practical side, you would have to type less AND clarify the exact intent of the comparison if you used

tamount = DSum( "[InvTotal]", "tblsalesinvoice", "[InvDate] Between #" & _
  Format( CDate( [Text150], "yyyy-mm-dd" ) ) & "# And #" & _
  Format( CDate( [Text152], "yyyy-mm-dd" ) ) & "# " )

theDBguy - check your parenthesis balance. You have nested functions of Format and CDate so need TWO ending parentheses in the expression not counting the one that ends the DSum.

Hi Doc. I try, really. Thanks for the assist.