Dsum not giving expected results. (1 Viewer)

MIkeD666

Registered User.
Local time
Today, 04:57
Joined
Jan 12, 2019
Messages
59
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

Enthusiastic Amateur
Local time
Today, 11:57
Joined
Sep 21, 2011
Messages
14,038
What happens if you run the DSum with those hard coded values.?

Are there time elements involved with these dates?
 

MIkeD666

Registered User.
Local time
Today, 04:57
Joined
Jan 12, 2019
Messages
59
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

Registered User.
Local time
Today, 04:57
Joined
Jan 12, 2019
Messages
59
sorry the are no time elemets in the values just the dates
 

theDBguy

I’m here to help
Staff member
Local time
Today, 04:57
Joined
Oct 29, 2018
Messages
21,357
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

Immoderate Moderator
Staff member
Local time
Today, 06:57
Joined
Feb 28, 2001
Messages
26,996
Also, just on the practical side, you would have to type less AND clarify the exact intent of the comparison if you used

Code:
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.
 
Last edited:

MIkeD666

Registered User.
Local time
Today, 04:57
Joined
Jan 12, 2019
Messages
59
Hi WELL TANK YOU FOR THE INPUT I GOT IT WORKING WITHIN 5MINS.
Much appreciated. Thank you
 

theDBguy

I’m here to help
Staff member
Local time
Today, 04:57
Joined
Oct 29, 2018
Messages
21,357
Also, just on the practical side, you would have to type less AND clarify the exact intent of the comparison if you used

Code:
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. :)
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 07:57
Joined
Feb 19, 2002
Messages
42,970
Just to clarify. When your standard date format is NOT mm/dd/yyyy, you need to format text strings to either that format or the less ambiguous yyyy/mm/dd because SQL ?Server defaults to the US date format. Dates that are amgibuous such as 10/02/19 will be assumed to be October 2nd whereas you think the date is Feb 10th.

Additionally, using Format() to format a date converts it to a string so unless you are using yyyy/mm/dd, you cannot sort or compare string dates since 01/02/16 will compare as LESS than 10/22/15 because 01 is less than 10.

So, NEVER format dates except when you are creating an SQL string as you were in this case. As long as both dates are date data types, the actual format is irrelevant and the date functions will always work correctly.
 

Users who are viewing this thread

Top Bottom