DSum function ignoiring criteria in Access (1 Viewer)

KSDavis

New member
Local time
Today, 12:35
Joined
Sep 18, 2017
Messages
7
I am having some issues in access using the DSum function to create a running total in access. I am trying to create a running total for both my Production standard and total conforming parts by using the date as the criteria. I am also doing this in a totals query which is set to pull data between [start date] and [end date]. The formula is working correctly for my production standard creating a running total between the specified dates but the total conforming formula (which is the exact same formula) is summing all of the data and then creating a running total. The are the two formulas I am using:

Production Standard - Running Total: DSum("[Production Standard]","M151-3 by Month (with standards)","Date <= #" & [Date] & "#")

Total Conform- Running Total: DSum("[Total Conform]","Kylee M151-3 by Month (with standards)","Date <= #" & [Date] & "#")


I should also note that I used these exact formulas for a different query (sorting by a different shift) and they worked perfectly.


Production Standard - Running Total: DSum("[Production Standard]","M151-1A by Month (with standards)","Date <= #" & [Date] & "#")

Total Conform- Running Total: DSum("[Total Conform]","Kylee M151-1A by Month (with standards)","Date <= #" & [Date] & "#")


Can anyone tell me what I am possibly doing wrong and why theses equations work for some of my data and not the others? This is all pulled from the same source I am just creating different reports sorted by shift. Also I am using this query in to make a graph to comparing the two running total values by date so it must be done in a query although I am aware that this slows down access.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 14:35
Joined
Feb 28, 2001
Messages
27,000
Access is very "touchy" when you use reserved words such as "Date" in place of a variable or field name. In particular, if there is a [Date] field in your domain specified by the "M151-1A..." then the question is, from where does the comparison date originate? If you were doing that from a form control, for example, then you have ambiguity in the source of your compared data.

To be honest, from what you provided, it is hard to see why it wouldn't work. Therefore, the only clue I have is that it works for one sorting scheme but not the other, and therefore, you have an issue in which something in that criteria expression interacts with the sort order in a way that isn't clear. Since all you have is "date" or "[date]" then something is wrong with the date selection and you are not selecting what you think you are. In the absence of a better description, that's all I can guess at.

Also, far be it from me to discuss style, as I have never been accused of having any, but including special characters such as parentheses in object names is not commonly done. Hey, it's your DB and you have to do the typing; no skin off my nose. If this is linked from something like an SQL Server table or something like that, I understand. But exceptionally long table names don't help you unless you needed typing practice.

Or as the bard once put it, "A rose by any other name..."
 

KSDavis

New member
Local time
Today, 12:35
Joined
Sep 18, 2017
Messages
7
I have uploaded some screen shots showing the information in my queries in both design view and data sheet view I hope it's helpful to give a more visual description. The [Date] criteria I am using is an existing field in my query so that my running total is summed by ascending dates. As far as the really long query name goes it is most definitely not ideal but I am working from an existing data base which contains hundreds of pre-existing queries, forms, and reports so it is purely for identification purposes for me. Could the parenthesis in my query name be throwing off my data? I am fairly new to access so this is a learn as I go process for me and I really appreciate your help.
 

Attachments

  • M151-1A.png
    M151-1A.png
    7 KB · Views: 55
  • M151-1A Query.png
    M151-1A Query.png
    7.7 KB · Views: 64
  • M151-1A graph.png
    M151-1A graph.png
    18.3 KB · Views: 73
  • M151-3.png
    M151-3.png
    37.9 KB · Views: 102
  • M151-3 Query.png
    M151-3 Query.png
    10.3 KB · Views: 73

plog

Banishment Pending
Local time
Today, 14:35
Joined
May 11, 2011
Messages
11,611
Let me state it more succintly and disdainfully than Doc:

Your poorly named objects are causing Access to not know what you are talking about.

I suggest removing all non-alphanumeric characters from your table/field names, as well as renaming that Date field to something else.

You can also put brackets around your table and field names to get it to work as is.
 

plog

Banishment Pending
Local time
Today, 14:35
Joined
May 11, 2011
Messages
11,611
Now I am looking at your screenshots and don't understand the issue. It seems your DSUM is working---I don't see any error messages.

So what's the issue? Unexpected results? Everything seems to add up even. Really don't understand the issue.
 

KSDavis

New member
Local time
Today, 12:35
Joined
Sep 18, 2017
Messages
7
It is summing correctly for my M151-1A data and for the Production standard in my M151-3 data. For the total conforming data it summing all of the data for shift three and then creating a running total from that. Essentially it is ignoring the date criteria that I have set up for the entire query and is including data from previous dates. So my total conform running total should start out at 5787 but instead is starting at 165400. These formulas worked in the other query though so I am not sure exactly where I am going wrong.
 

plog

Banishment Pending
Local time
Today, 14:35
Joined
May 11, 2011
Messages
11,611
Can you post a stripped down version of that database so I can play with it? Include just the objects I need to see.
 

KSDavis

New member
Local time
Today, 12:35
Joined
Sep 18, 2017
Messages
7
I unfortunately am not able to do that but I have been made aware that the reason the query is not returning the information that I want is because I need to include two different date criteria in my formula. I was also formatting the date value incorrectly which was confusing access. So this was suggested to correct the date formatting:

Production Standard - Running Total: DSum("[Production Standard]","[M151-3 by Month (with standards)]","[Date] <= #" & Format([Date], "yyyy\/mm\/dd") & "#")

And this was suggested to include two date criteria:

Production Standard - Running Total: DSum("[Production Standard]","[M151-3 by Month (with standards)]","[Date] Between #" & Format([StartDate], "yyyy\/mm\/dd") & "# And #" & Format([EndDate], "yyyy\/mm\/dd") & "#")

The only issue I am having with the second formula is now it is not creating a running total but it is selecting information between those two specified dates and giving me the combined total of all of that data. Any suggestions on how to rewrite this so that it still creates a running total but only between the specified dates?

 

KSDavis

New member
Local time
Today, 12:35
Joined
Sep 18, 2017
Messages
7
I was thinking I might be able to use an IIF function? IIf [Date] is between [Start Date] and [End Date]........(first formula above) but I am not sure how to go about writing it I keep getting error messages.
 

Users who are viewing this thread

Top Bottom