The dreaded DSum-AHHHHHHHH

=DSum("[404junc.UNITS_SERV]" And "[ELEGIBLITY]","404CLINT","[ELEGIBLITY] = 'CSS' Between '" & [Forms]![404ls2cp]![StartDate] & "' And '" & [Forms]![404ls2cp]![EndDate] & "'")

In this Dsum I am attempting to sum the number of units_serv for those individuals who are of CSS status and whose startdate and enddate is between
one which the user types in on an unbound form

404junc is my junction table
Units_serv is the field
Elegibility is a field
404CLINT is the name of the table
404ls2cp is my unbound fm for the startdate and enddate

Thanks

Kevin


LVL 1
kpu8Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

clockwatcherCommented:
The first field of a DSum is the expression you want to sum.  The second field is the set you want to perform your Dsum over.  The third is your criteria.  

So your Dsum should probably be something like:

DSum(

   "[404junc.UNITS_SERV]",

   "SELECT ELEGIBILITY, UNITS_SERV FROM 404CLINT INNER JOIN 404JUNC ON 404CLINT.ID = 404JUNC.ID",

   "[ELEGIBLITY] = 'CSS' AND [SOMEDATE] Between #" & [Forms]![404ls2cp]![StartDate] & "# And #" & [Forms]![404ls2cp]![EndDate] & "#"  

   )

You'll have to change the second part to a query or SQL statement that actually represents the join between 404JUNC and 404CLINT.  

The third part requires [SOMEDATE] to be the field that you want to ensure is between your start and end date.  Plus, if you're actually using dates, you want to use the "#" symbol as delimiter not the single qoute " ' ".
0
JimMorganCommented:
Kevin:

Is this function in the Db you sent me?  Where is it and I'll check it out.

Jim
0
kpu8Author Commented:
Actually Jim, this function is not in the db of mine you have - but the report is - I believe it is called LS2C or LS2C2
but that most likely won't help but who knows

0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

JimMorganCommented:
Kevin:

Clockwatcher was on the right track but I guess he forgot that you can't use an SQL statement as the domain experssion for a aggragate function.  You will need to run this DSum function against a stored query.

Create a query, 404UnitsServ, by joining 404Client, 404Junc, and unless you have changed your database to make ATTENDEE a date instead of a Yes/No field, the meet date field from 404Meet.

Pull down into columns Units_Serv, Elegiblty, and the date field (we will leave it as Attendee for this discussion).  Save the query.

Now you expression would look like this:

=DSum("UNITS_SERV","404UnitsServ","ELEGIBLITY = 'CSS' AND ATTENDEE Between #" & [Forms]![404ls2cp]![StartDate] & "# And #" & [Forms]![404ls2cp]![EndDate] & "#")

I noticed that you have the # correct in the DCount question which was posted before this one.

Jim



0
kpu8Author Commented:
Jim - I tried what you said and since I'm at wits end - if it's not too much trouble I'm going to e-mail you my db
for a look-see

Thanks

Kevin
0
JimMorganCommented:
Here is the calculation that you want to put into your report for the Units of Service:

=NZ(DSum("UNITS_SERV","404UnitsServ","ELEGIBLITY = 'CSS' AND Date Between #" & [Forms]![404ls2cp]![StartDate] & "# And #" & [Forms]![404ls2cp]![EndDate] & "#"),0)

The problem areas were:

You have to do an NZ or the answer will be Null, which isn't able to print in a report.

You were using Attendee in several calculations as a date while it is a boolean Yes/No field.  Either remove the 'Between date to date' and say Attendee = True or change Attendee to Date, which is the date that the member attended a meet.

Some calculations had [Forms].[4041s2cp].[StartDate] and they should have used Bangs instead of Dots.  Dots may work in the report itself, like the Start To End  dates on the report but they can not be used in calculations.

I wasn't sure what you were trying to do with the Number Served row but the same items should apply there as well:

=nz(DCount("*","404UnitsServ","[ELEGIBLITY] = 'CSS' And Date Between #" & [Forms]![404ls2cp]![startdate] & "# And  #" & [Forms]![404ls2cp]![enddate] & "#"),0)

or

=nz(DCount("*","404UnitsServ","[ELEGIBLITY] = 'CSS' And ATTENDEE = True,0)

The underlying query 404UnitsServ looks fine.

Jim
0
kpu8Author Commented:
When you say attendee was a date - you mean in the calculation not in the deign scheme right?


I attempted to use this:

=NZ(DSum("UNITS_SERV","404UnitsServ","ELEGIBLITY = 'CSS' AND Date Between #" & [Forms]![404ls2cp]![StartDate] & "# And #" & [Forms]![404ls2cp]![EndDate] & "#"),0)

and got an #error in that field when I printed it out -

0
kpu8Author Commented:
When you say attendee was a date - you mean in the calculation not in the deign scheme right?


I attempted to use this:

=NZ(DSum("UNITS_SERV","404UnitsServ","ELEGIBLITY = 'CSS' AND Date Between #" & [Forms]![404ls2cp]![StartDate] & "# And #" & [Forms]![404ls2cp]![EndDate] & "#"),0)

and got an #error in that field when I printed it out -

0
JimMorganCommented:
You were trying to treat attendee as a date in the report.

Which field on the report did you do?  I discovered that you had the same information twice in the report.  The uppper detail section is hidden and the lower footer section is visible.  If you put the change in the detail section, you will see the error in the footer.

If it were me, I'd remove the footer and just leave the detail.  Since there are no records behind the report and all you are doing is making calculated fields, this is all you need.

As a matter of fact, if it were me and I was just showing this report, I wouldn't even fool with a report.  I'd do the whole thing as a form which is faster and can be printed the same as a report.

Jim
0
kpu8Author Commented:
Yeah I had the same info twice because
I was orginally planning on using Sums
for some of the fields and the only way they show up correctly is the make one invisble and one visible
but now I have moved attendees Dcount function and the units service DSum to just the details section of the report thus elimination the footer  I get #Error in both fields

I also tried your suggestion about just
using a form so I took all the info that I had in details and pasted it into a form and I also got a #Error

I also thought about using just straight SQL but that turned out to be a real nightmare when I tried it because I kept getting ambigious join errors when I used the same tables I used in the 404UnitsServ query

I'll keep plugging away at it - it's just getting very frustrating because once this report shows me the correct numbers I'm home free

Thanks

Kevin
0
JimMorganCommented:
To prevent the ambigious join errors, make the joins one-to-one for both tables.

Didn't I send the DB that I had working back to you?  If not, I'll resend it.  Just use it the way that I fixed it and you should be on your way.

Jim
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
kpu8Author Commented:
Thanks for all your help

these last couple of weeks

I got the report and it works without
giving me any errors - but sometimes
it gives the units served as 0 no matter what - even if I've checked that a client attended an event

I'll look at it a little more though -
maybe and hopefully I missed something small

Thanks Again

Kevin
0
JimMorganCommented:
You're welcome.  Glad to be of help.

I noticed that there might be some discrepencies between the tables.  For example, I don't understand the States List table (383 records).  The table is linked to the se_nodedetails table (52 records) by location. If you look at the query from the se_nodedetails side, you get all 52 records.  Reverse the relationship, you get 414 records.

Both tables have an identically named field, Index Number, which is autonumbered.  You would expect that the same field in different tables would have some kind of relationship.  They don't here.  They can't.

It would seem to me that the state list should only have the location along with the state and country codes.  What is the purpose of the Index Number here?

What is an example of a 0 units served report?  I'll check it out.

Jim
0
kpu8Author Commented:
I'm sorry but I'm not sure what you mean
I don't think I have anything in my db which is a states list table - or a table called se_nodedetails

In my db I think the one I sent you is called mental2.zip if you bring up a form say 404ls2cp and type in two dates then hit print report - it seems that the units_served field is always printed as 0 -

the units served field is triggered from the 404meet form by selecting the button client attendance sheet and then checking off attendees - when one does this the units_served field gets filled with a number 1,2 or 3 the report ls2c (lets say the first column for example)should show in the units_served field the sum of the units_served field for those people whose 404CLINT.Elegbility is equal to CSS and whose ADM_DATE (also on the 404Clint form) is between the two dates the user has entered on the 404ls2cp form

Thanks

Kevin  
0
JimMorganCommented:
Sorry about the wrong info in the comments.  I find it so hard to enter much information in this tiny comment box that I'm forced to responding to a lot of these comments off-line.  And I'm involved in some many questions which are similar in nature that I'm getting totally mixed up.

Sigh, I guess I need to cut back on my time here and get some rest.

Any way, I'll check it out and get back to you.

Jim
0
kpu8Author Commented:
OK - that sounds great

I've been a little bleary eyed as well  

I'm installing a beta-version of the system thus far -- on-site tomorrow
so it should be interesting

Thanks

Kevin


0
JimMorganCommented:
Kevin:

I went back and ran the report on my copy of the DB.  For #404 from 5/1/99 to 7/1/99 I got the following Units of Service:

     CSS         LOCAL    OTHER    TOTAL
      6            1        0        7

This is the control source for CSS:

=NZ(DSum("UNITS_SERV","404UnitsServ","ELEGIBLITY = 'CSS' AND Date Between #" & [Forms]![404ls2cp]![StartDate] & "# And #" & [Forms]![404ls2cp]![EndDate] & "#"),0)

I have no idea why yours isn't working.

Jim
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.