UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Construct Where Clause For Time Duration Text Box, Access 2016    
 
   
brastedhouse
post Oct 20 2019, 01:39 PM
Post#1



Posts: 130
Joined: 16-March 15
From: Chautauqua, NY


Good afternoon,

in query:
Hours: DateDiff("n",[drillStartTime],[drillEndTime])
to calculate the elapsed time of an event

in text box on report
=[Hours]\60 & Format([Hours] Mod 60,"\:00")
to get [Hours] to display as hours and minutes

This works like a charm. Now I would like to limit the calculation to the value of another field. I have a check box (drillSubjectOSHA)and I want to limit the Hours in a text box on the report to the hours when the check box is true. I think I need a where clause or something like it, but I have no idea how to do this.

Any help would be appreciated.

Best, Scott
Go to the top of the page
 
Doug Steele
post Oct 20 2019, 01:44 PM
Post#2


UtterAccess VIP
Posts: 22,223
Joined: 8-January 07
From: St. Catharines, ON (Canada)


If I'm understanding your question correctly, you don't need to change the calculation for Hours, you need to set a WHERE clause for your query so it only returns those rows where the check box is true.

--------------------
Doug Steele, Microsoft Access MVP (2000-2018)
Personal webpage
Microsoft profile
Co-author: Access Solutions: Tips, Tricks, and Secrets from Microsoft Access MVPs, published by Wiley
Co-author: Effective SQL: 61 Specific Ways to Write Better SQL, published by Addison-Wesley Professional
Technical Editor: Access 2010 Bible, Access 2013 Bible, Access 2016 Bible, all published by Wiley
Technical Editor: SQL Queries for Mere Mortals: A Hands-On Guide to Data Manipulation in SQL, 4th Edition, published by Addison-Wesley Professional
Go to the top of the page
 
projecttoday
post Oct 20 2019, 01:57 PM
Post#3


UtterAccess VIP
Posts: 11,189
Joined: 10-February 04
From: South Charleston, WV


If you want to include all the rows in the query but only include in the calculation those with the box checked then you need an Iif.

--------------------
Robert Crouser
Go to the top of the page
 
brastedhouse
post Oct 20 2019, 02:26 PM
Post#4



Posts: 130
Joined: 16-March 15
From: Chautauqua, NY


Hi, yes and yes. Calculation is good, just need to delimit it. and yes I am hoping for an IIf in a text box.I already have a query returning the elapsed time. An IIf something like:

CODE
=Sum(IIf([drillSubjectOSHA],1,0))
(drillSubjectOSHA is a yes/no field)

which I am using to get the total of a count if that field is true. But I cannot work out how to manipulate this example into what I need.

In the end I want to total the sum of [Hours] delimited by the yes/no field. I was thinking of something like this,

CODE
=Sum(IIF([drillSubjectOSHA]) = 1, ([Hours]\60 & Format([Hours] Mod 60,"\:00")), 0)


but it gives me am error saying wrong number of arguments.

Thanks, Scott
This post has been edited by brastedhouse: Oct 20 2019, 03:01 PM
Go to the top of the page
 
arnelgp
post Oct 20 2019, 02:59 PM
Post#5



Posts: 1,510
Joined: 2-April 09
From: somewhere out there...


maybe something like:

=Sum(IIf([drillSubjectOSHA] And [drillSubjectOSHA], [Hours], 0))

--------------------
Never stop learning, because life never stops teaching.
Go to the top of the page
 
brastedhouse
post Oct 20 2019, 03:09 PM
Post#6



Posts: 130
Joined: 16-March 15
From: Chautauqua, NY


That gives me the total of both yes and no. I have a small table for an experiment and it has several bogus records. There is a record for this member with OSHA and a record of non-OSHA. The result should be 2 total drills for the member and 2 total hours and 1 OHSA hour. I get 2 OSHA hours. hmmm.
Go to the top of the page
 
brastedhouse
post Oct 20 2019, 03:16 PM
Post#7



Posts: 130
Joined: 16-March 15
From: Chautauqua, NY


OK, my mistake. It is right. VERY Sorry! THANK YOU! Can you tell me how this works?
Go to the top of the page
 
brastedhouse
post Oct 20 2019, 03:25 PM
Post#8



Posts: 130
Joined: 16-March 15
From: Chautauqua, NY


Also, when I do this:

=Sum([Hours]) I get the correct result (180) in minutes.


When I do this:

=Sum([Hours]\60 & Format([Hours] Mod 60,"\:00"))

I get Property Not Found instead of 3 (Hours)
Go to the top of the page
 
Doug Steele
post Oct 20 2019, 04:18 PM
Post#9


UtterAccess VIP
Posts: 22,223
Joined: 8-January 07
From: St. Catharines, ON (Canada)


Your formatted converts the value to a text string. You can't sum text strings.

Do the summation, then do the formatting on the sum.

--------------------
Doug Steele, Microsoft Access MVP (2000-2018)
Personal webpage
Microsoft profile
Co-author: Access Solutions: Tips, Tricks, and Secrets from Microsoft Access MVPs, published by Wiley
Co-author: Effective SQL: 61 Specific Ways to Write Better SQL, published by Addison-Wesley Professional
Technical Editor: Access 2010 Bible, Access 2013 Bible, Access 2016 Bible, all published by Wiley
Technical Editor: SQL Queries for Mere Mortals: A Hands-On Guide to Data Manipulation in SQL, 4th Edition, published by Addison-Wesley Professional
Go to the top of the page
 
brastedhouse
post Oct 20 2019, 09:01 PM
Post#10



Posts: 130
Joined: 16-March 15
From: Chautauqua, NY


I don't understand how to do the summation, then do the formatting on the sum.

EDIT - AFTER LOTS OF STARING AND SCRATCHING . . . AH HAH!

Now I get it. I left the original with the minutes and made it not visible and covered it with another visible text box that had the formatting in it. Now I have the right duration and the right formatting. Is this what you were referring to?

Best, Scott
This post has been edited by brastedhouse: Oct 20 2019, 09:50 PM
Go to the top of the page
 
Doug Steele
post Oct 21 2019, 07:20 AM
Post#11


UtterAccess VIP
Posts: 22,223
Joined: 8-January 07
From: St. Catharines, ON (Canada)


That was probably the easiest approach.

Another would have been =Sum([Hours])\60 & Format(Sum([Hours]) Mod 60,"\:00"), but that would require more calculations.

Glad you got it to work. thumbup.gif

--------------------
Doug Steele, Microsoft Access MVP (2000-2018)
Personal webpage
Microsoft profile
Co-author: Access Solutions: Tips, Tricks, and Secrets from Microsoft Access MVPs, published by Wiley
Co-author: Effective SQL: 61 Specific Ways to Write Better SQL, published by Addison-Wesley Professional
Technical Editor: Access 2010 Bible, Access 2013 Bible, Access 2016 Bible, all published by Wiley
Technical Editor: SQL Queries for Mere Mortals: A Hands-On Guide to Data Manipulation in SQL, 4th Edition, published by Addison-Wesley Professional
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    15th November 2019 - 03:28 AM