DSum Problem

[AccessIM]

Hello All-

I am having a real problem getting a running total column to work properly
in one of my queries.

I have a union query that pulls in attendance point information from three
different tables. I am using this union query to try to create a running
total of points in the far right column.

The union query has the following fields: EmployeeID, SSN, IncidentDate,
Type and Points. I would like a column that has a running total of the
Points field for each EmployeeID. Like this:

EmployeeID SSN IncidentDate Type Points TotalPoints
6 555332222 10/21/09 Neg Pts -.25 -.25
6 555332222 10/22/09 Absent 2.00 1.75
8 111223333 10/20/09 Late 0.50 0.50
8 111223333 10/21/09 Absent 2.00 2.50
8 111223333 10/22/09 Late 0.50 3.00

I have come the closest with the code below but it totals all records in the
query and shows 4.75 instead of breaking them by SSN or EmployeeID (either of
these fields would work).

TotalPointsDSum("[qryTotalPointDetail].[Points]","qryTotalPointDetail","[SSN]<="
& [qryTotalPointDetail].[SSN] And "[IncidentDate]<=" &
[qryTotalPointDetail].[IncidentDate]))

I tried to take out the ...And "[IncidentDate]...part but that only returns
an #Error.

I have been searching through the threads and trying different variations
but just can't seem to figure it out.

Could someone please give me some assistance with this as I am working under
a dead line for this project? I would GREATLY appreciate it.

 

[John W. Vinson]

TotalPointsDSum("[qryTotalPointDetail].[Points]","qryTotalPointDetail","[SSN]<="
& [qryTotalPointDetail].[SSN] And "[IncidentDate]<=" &
[qryTotalPointDetail].[IncidentDate]))

I tried to take out the ...And "[IncidentDate]...part but that only returns
an #Error.

For one thing the word AND needs to be part of the string, rather than outside
it; for another, a Date field needs # as a criterion. Try

TotalPoints: DSum("[Points]", "qryTotalPointDetail", [SSN]<=" &
[qryTotalPointDetail].[SSN] & " And [IncidentDate]<=#" &
[qryTotalPointDetail].[IncidentDate] & "#")

If SSN is a Text type field (which it should be, it's not a number that you'll
use for calculations) it needs to be delimited by quotemarks:

TotalPoints: DSum("[Points]", "qryTotalPointDetail", [SSN]<='" &
[qryTotalPointDetail].[SSN] & "' And [IncidentDate]<=#" &
[qryTotalPointDetail].[IncidentDate] & "#")

 

[AccessIM]

Hi John -

Thank you so much for the help and quick response. I entered the code you
sent and I receive a message that says "The expression you entered has an
invalid date value". When I click ok to clear the message, it highlights the
following section:
...#" & [qryTotalPointDetail].[IncidentDate] & "#...
(I did include the end quote but it is not highlighted)

I made a beginner's attempt at moving the quotes around and adding/removing
quotes but the only time I was able to get the query to run, the field was
blank.

TotalPointsDSum("[qryTotalPointDetail].[Points]","qryTotalPointDetail","[SSN]<="
& [qryTotalPointDetail].[SSN] And "[IncidentDate]<=" &
[qryTotalPointDetail].[IncidentDate]))

I tried to take out the ...And "[IncidentDate]...part but that only returns
an #Error.

For one thing the word AND needs to be part of the string, rather than outside
it; for another, a Date field needs # as a criterion. Try

TotalPoints: DSum("[Points]", "qryTotalPointDetail", [SSN]<=" &
[qryTotalPointDetail].[SSN] & " And [IncidentDate]<=#" &
[qryTotalPointDetail].[IncidentDate] & "#")

If SSN is a Text type field (which it should be, it's not a number that you'll
use for calculations) it needs to be delimited by quotemarks:

TotalPoints: DSum("[Points]", "qryTotalPointDetail", [SSN]<='" &
[qryTotalPointDetail].[SSN] & "' And [IncidentDate]<=#" &
[qryTotalPointDetail].[IncidentDate] & "#")

 

[John W. Vinson]

Hi John -

Thank you so much for the help and quick response. I entered the code you
sent and I receive a message that says "The expression you entered has an
invalid date value". When I click ok to clear the message, it highlights the
following section:
...#" & [qryTotalPointDetail].[IncidentDate] & "#...
(I did include the end quote but it is not highlighted)

I made a beginner's attempt at moving the quotes around and adding/removing
quotes but the only time I was able to get the query to run, the field was
blank.

What's in IncidentDate? What is its datatype: Date/Time, Text, something else?
Might it be NULL (which would give this error)?

 

[AccessIM]

The data type of the INCIDENTDATE field is Date/Time and there are no null
values in this field. There shouldn't ever be a null value in this field.

Hi John -

Thank you so much for the help and quick response. I entered the code you
sent and I receive a message that says "The expression you entered has an
invalid date value". When I click ok to clear the message, it highlights the
following section:
...#" & [qryTotalPointDetail].[IncidentDate] & "#...
(I did include the end quote but it is not highlighted)

I made a beginner's attempt at moving the quotes around and adding/removing
quotes but the only time I was able to get the query to run, the field was
blank.

What's in IncidentDate? What is its datatype: Date/Time, Text, something else?
Might it be NULL (which would give this error)?

 

[AccessIM]

Sorry about the all caps on the IncidentDate field, John. I have made so
many changes trying to get this to work, the original field name was initial
caps and now it is in all caps. Just didn't want you to think i was yelling
it... )

Hi John -

Thank you so much for the help and quick response. I entered the code you
sent and I receive a message that says "The expression you entered has an
invalid date value". When I click ok to clear the message, it highlights the
following section:
...#" & [qryTotalPointDetail].[IncidentDate] & "#...
(I did include the end quote but it is not highlighted)

I made a beginner's attempt at moving the quotes around and adding/removing
quotes but the only time I was able to get the query to run, the field was
blank.

What's in IncidentDate? What is its datatype: Date/Time, Text, something else?
Might it be NULL (which would give this error)?

 

[AccessIM]

Would you have any suggestions?

Hi John -

Thank you so much for the help and quick response. I entered the code you
sent and I receive a message that says "The expression you entered has an
invalid date value". When I click ok to clear the message, it highlights the
following section:
...#" & [qryTotalPointDetail].[IncidentDate] & "#...
(I did include the end quote but it is not highlighted)

I made a beginner's attempt at moving the quotes around and adding/removing
quotes but the only time I was able to get the query to run, the field was
blank.

What's in IncidentDate? What is its datatype: Date/Time, Text, something else?
Might it be NULL (which would give this error)?

 

[vanderghast]

Format explicitly, using:


....#" & Format([qryTotalPointDetail].[IncidentDate], "mm-dd-yyyy hh:nn:ss")
& "#...



It is possible that your regional settings ask for some unexpected default
date_to_string conversion, such as using dot, or no 'delimiter' to supply
mmddyyyy strings, so forcing a well behaving format is preferable.



Vanderghast, Access MVP

Would you have any suggestions?

Hi John -

Thank you so much for the help and quick response. I entered the code
you
sent and I receive a message that says "The expression you entered has
an
invalid date value". When I click ok to clear the message, it
highlights the
following section:
...#" & [qryTotalPointDetail].[IncidentDate] & "#...
(I did include the end quote but it is not highlighted)

I made a beginner's attempt at moving the quotes around and
adding/removing
quotes but the only time I was able to get the query to run, the field
was
blank.

What's in IncidentDate? What is its datatype: Date/Time, Text, something
else?
Might it be NULL (which would give this error)?