Report based on Interactive Form, DSUM with multiple Criteria

[Denise2020]

I have an interactive form that creates a report based on criteria input by the user.
The user can select a JobSource and a Date Range and the report shows how many hours in total were spent on the jobs that fit those criterion. The report is based on a query with criterion based on the JobSource and Dates inputted on the forms.

However, there is a checkbox on each job as to whether the job was done outside of normal work hours. I need to create a subtotal on the report
that shows the total number of hours spent just on those jobs outside of normal work hours. I am having a heck of a time figuring this out. I have tried a separate query and joining it to the original query to get the total, but then I just get the TOTAL number of hours spent outside normal working hours for ALL jobs (no JobSource or Date Range taken into consideration).

I tried writing a DSUM expression to take all criterion into consideration (JobSource, Yes/No on the checkbox, and Date Range), but it also gives the total for ALL jobs.

Google isn't helping me find an answer I can make work, including the ever-helpful (http://theaccessweb.com/general/gen0018.htm)

This is my crazy query expression. Am I going about this totally the wrong way? Thanks in advance!

UtanforArbTid: DSum("Tidsatgang","tblStodarenden","TidUtanforArbetstid= -1" And "Uppdragsstallare Like " & [Forms]![fStodarendenList]![cboFilterUppdragsstallare] AND "Arendedatum Between " & [Forms]![fStodarendenList]![txtStartDateT] & " and " & [Forms]![fStodarendenList]![txtEndDateT])



Disclaimer: I am self-taught, and poorly at that, so please go easy on me.

 

[pbaldy]

Try

UtanforArbTid: DSum("Tidsatgang","tblStodarenden","TidUtanforArbetstid= -1 And Uppdragsstallare Like " & [Forms]![fStodarendenList]![cboFilterUppdragsstallare] & " AND Arendedatum Between " & [Forms]![fStodarendenList]![txtStartDateT] & " and " & [Forms]![fStodarendenList]![txtEndDateT])

You'll need to add the appropriate delimiters as noted on that site.

 

[Denise2020]

Okay now I am just running amok. I usually say I know just enough to be dangerous.

This is obviously not working. What have I done? Delimiters are killing me. I tried to separate it out so I could more easily see things (I still can't)

I should add that Uppdragsstallare is a dropdown box selection.

UtanforArbTid: DSum("Tidsatgang","tblStodarenden","TidUtanforArbetstid= -1 _
& " AND Uppdragsstallare Like '" & [Forms]![fStodarendenList]![cboFilterUppdragsstallare] & "'"_
& " AND Arendedatum Between #" & [Forms]![fStodarendenList]![txtStartDateT] & "# and #" & [Forms]![fStodarendenList]![txtEndDateT]&"#")

 

[pbaldy]

I should say if this is in a query, a DSum() can be a performance killer. I'd create a separate totals query that pulled the summed values and join to it. To your expression, I'd get it working without breaking it up first. Is the field for the dropdown text or numeric? Try':

UtanforArbTid: DSum("Tidsatgang","tblStodarenden","TidUtanforArbetstid= -1 AND Uppdragsstallare Like '" & [Forms]![fStodarendenList]![cboFilterUppdragsstallare] & "' AND Arendedatum Between #" & [Forms]![fStodarendenList]![txtStartDateT] & "# and #" & [Forms]![fStodarendenList]![txtEndDateT] & "#")

If the data type of Uppdragsstallare is numeric, you want to get rid of the single quotes.

 

[Denise2020]

Okay so that is how I originally tried this, I made a separate query that selected only the jobs where Tidutanforarbetstid was true, and totalled those jobs.

However, when I joined it and ran the report, it showed the total as 75 hours (the current total for all jobs outside of normal working hours) no matter what criterion I put in for the JobSource or Date Range. How do I incorporate those criterion as well?

(I really appreciate your help with this - as always!)

 

[pbaldy]

It sounds like you need to add the date criteria to the totals query. Can you attach the db here?

 

[Denise2020]

Would it work if I don't attach the backend? I am not sure how helpful it would be.

I tried creating the query with all three (date, jobsource, outside normal work hours) and added the sum with the Totals for that column. This is where I got stuck before. How do I join that total to the query the report is based on? And with the Date range, I get an error that it is too complex to calculate.

 

[pbaldy]

No, it probably wouldn't help without data to test on. Try changing Group By to Where for the fields that have criteria.

 

[Denise2020]

Okay, I am going to walk this back because maybe (read probably) I am going about this all wrong.

My form is a split form with a few user-input controls (see attached). My original needs were to show all jobs, filter jobs by date range, filter jobs by source, or filter jobs by date range and source. The filtering became an issue when I realized I could not select one job source or ALL sources. I tried adding an "ALL" to the combobox which was a disaster and felt... wrong. So I had to create two sections. One where I could filter by source and dates, and another for filtering just by date range.

The left lighter grey box is where ideally the user can select the JobSource, then the Date Range. It shows the total number of job hours, and there is a button to either open or print the report of those selections. The form is based on a query that does not have any criteria, and the report opens based on a query (also see attached).

The lighter grey box to the right of that one is a temporary solution I had to create because I could not figure out how to make it so that if no selection is made in the JobSource combobox, that ALL JobSources are selected. So that one filters solely based on Date Range. That report is based on a second query (see attached). Messy, but it works.

There is VBA code behind the buttons for showing the filters on the form itself. (attached as well).

I have pieced all of this together as needed and because I am a complete novice, it is not pretty. Sorry if it makes your expert eyes cringe!

So now my boss needs the total number of job hours AND the total number of job hours only worked outside normal working hours (just to clarify, NOT total hours within working hours, and outside working hours, but TOTAL of all jobs, and then outside working hours shown separately as well).

My original idea was to just create a query that sums just those outside working hours and have a field on the report that shows the total sum, but I could not figure out how to get it to show that sum (the query works great, getting it on the form and report is the issue).

Then I googled and tried IIF, DSUM, and various other solutions, but I am failing miserably.

If I walk all this back to the beginning, what would be the most elegant way to handle this (seemingly) simple thing that has me so vexed? Thank you!!

 

[Denise2020]

(I am working on trying to get the db to where I could share it, but until then I hope the screenshots would help.)

 

[Gasman]

I would calculate the out of hours as a new field, then sum that.
Not sure what you class as out of hours?, start after end of day?, or finish after end of day?
Anyway calculate what you need as a new field then sum that, along with the total hours. Might need to subtract out of hours from total hours, or again, criteria to determine within hours?
Up to you.