Page 1 of 2 12 LastLast
Results 1 to 15 of 16

Calculated field based on criteria being true

  1. #1
    Giles64 is offline Novice
    Windows 7 32bit Access 2013
    Join Date
    Jul 2015
    Posts
    14

    Calculated field based on criteria being true

    Hi

    I have only basics access skills and would very much appreciate your help and understanding being unfamiliar with the language.

    I have an unbound text box on a form which totals man hours for a project, it calculates the time difference between "Finish" and "Start" time less 30 minutes for lunch, which works well.
    Text Box name = txtTotalManHours


    Control Source =Sum((Round(([Finish]-[Start])*24,2))-0.5)

    However lunch is not always taken or time on site is either too short or multiple sites attended in the one day, so...
    Is it possible to base the calculation on the provision that time difference reduces by 30 minutes only if "LunchTaken" (Checkbox) is true.

    Any help would be great, thanks in advance.

  2. #2
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    6,473
    Maybe
    Code:
    = IIF(LunchTaken = True,Sum((Round(([Finish]-[Start])*24,2))-0.5)-30,Sum((Round(([Finish]-[Start])*24,2))-0.5))
    I might suggest to pass the value of Sum((Round(([Finish]-[Start])*24,2))-0.5) to another textbox or variable so that this could be
    Code:
    txtSum = Sum((Round(([Finish]-[Start])*24,2))-0.5)
    and then in other textbox
    Code:
    = IIF(LunchTaken = True,txtSum-30, txtSum)
    However, I might make the textbox equal to a function that makes this calculation as what I've posted doesn't cover situations where your value(s) could be Null.
    - "doesn't work" doesn't help. Implement changes in copies of your database.
    "Everyone has a photographic memory; some just don't have film." Steven Wright

  3. #3
    Giles64 is offline Novice
    Windows 7 32bit Access 2013
    Join Date
    Jul 2015
    Posts
    14
    Hi

    Thanks for the quick response, I did try your suggestion and I can confirm that it does not calculate correctly if the "LunchTaken" box is false. Given my inexperience, please could you explain to me what you are meaning by "making the textbox equal to a function that makes this calculation", I thought Option 1 above did that. Thanks for your help.

  4. #4
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    6,473
    I can confirm that it does not calculate correctly if the "LunchTaken" box is false
    My example was based on the checkbox being True, not False, so not sure what that means. Examples of results vs expected might help.

    As for the function question I meant a user defined function (UDF), which is another way of saying that in a standard module you write a function that returns the value you need. Then you either assign that function to the textbox control source or to an event that will populate it. Using the latter will allow you to bind the field to a table, whereas if you make the function the control source, the textbox will not be updatable. Thus the purpose and process of the form matters.

    I thought Option 1 above did that
    That is correctly known as an expression, even if it uses vba functions.

  5. #5
    Giles64 is offline Novice
    Windows 7 32bit Access 2013
    Join Date
    Jul 2015
    Posts
    14
    Sorry for any confusion, I see what you mean about the calculation working on the basis of being only "true", so I will try to explain fully what the current situation is and what I am trying to achieve.
    The unbound text box is used for providing a running total of hours onsite for a particular project, so as new records are entered the total updates.
    I would like the hours for each record to be calculated allowing for whether lunch was taken or not as the case may be. So if the check box "LunchTaken" is ticked then reduce time difference by 30 minutes and if unticked then just calculate the time difference. I do not wish to store the calculation for each record just the running total.
    I was going to make the default for "LunchTaken" true and untick manually only when required because the most common scenario is true.

    Thanks for the explanation on the UDF, so am I correct in thinking I would need to assign the "Function" to an event, if this is true then your first option

    with some additional code to cater for the "false" situation would suffice on an "After Update" event attached to "LunchTaken"??

    I'm not sure at all, your help would be appreciated. Thanks again

  6. #6
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    6,473
    You are correct that the calculation shouldn't be stored.
    You haven't said where this calculated textbox is but I presume it's in the form header or footer. The latest comments make me think that the table design probably isn't right. What should be stored is -30 in a separate field in the table, if on the form the user sets the check to true. That's because the -30 is really the data, not the checkbox value. Think about that for a moment. What is really important - True/False or -30? T/F in this case is a flag (moderator, condition, whatever you want to call it) but it's not data.

    The event for this would be AfterUpdate of the checkbox on the form. Update -30 into the table field if True, update to Null if unchecked. The check should not be a table field, otherwise you run the risk of this being altered in the table and the -30 won't get removed.

    Then in the form footer or header, have one textbox sum all the minutes and another that sums all the -30's. These can be hidden if that looks better. A 3rd textbox should sum the other two. Now you will have your total across all the records for a project.
    Hope that helps. If not completely, post back as required. Consider uploading a zipped copy of the db if you think that will speed things up.

  7. #7
    Giles64 is offline Novice
    Windows 7 32bit Access 2013
    Join Date
    Jul 2015
    Posts
    14

    Calculated field based on criteria being true

    Thanks for explaining the process, it makes sense and I would much appreciate you showing me what you mean so I have attached a sample of the database with only the tables and forms relevant - hope this is right. Record 1 should have a labour total of 16.5 and Record 2 13.5. I am sure once I have viewed what you are explaining it will become clearer.

    Again thanks for your patience and help, very much appreciated.Attachment 40228

  8. #8
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    6,473
    Do you want the bad news first or the bad news first?
    Your db has a lot of design issues and I'd recommend you fix them first. I intended to forge ahead and advise you within the reply where I would post your db, but in fact, I can't even do what I need to do. With respect to that, the labour table doesn't have a PK field so it's practically impossible to select a record for the -30 minutes.
    If this db doesn't really reflect what you have, then that's a problem and you should post something more representative. If it is, then that's a problem. So let me know what that situation is because I'd rather not go through a long list of design issues if what you're using doesn't really look like this. I'm referring mainly to normalization issues. Maybe read up on these for some clues (at least for normalization) and see what you think:
    Normalization Parts I, II, III, IV, and V
    http://rogersaccessblog.blogspot.com...on-part-i.html
    and/or
    http://holowczak.com/database-normalization/

    Entity-Relationship Diagramming: Part I, II, III and IV
    http://rogersaccessblog.blogspot.com...ng-part-i.html

    How do I Create an Application in Microsoft Access?
    http://rogersaccessblog.blogspot.com...cation-in.html

    Important for success:
    Naming conventions - http://access.mvps.org/access/general/gen0012.htm
    https://www.access-programmers.co.uk...d.php?t=225837

    What not to use in names - http://allenbrowne.com/AppIssueBadWord.html
    About Auto Numbers
    - http://www.utteraccess.com/wiki/Autonumbers
    - http://access.mvps.org/access/general/gen0025.htm

  9. #9
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    8,486
    I agree with Micron - there are issues that should be fix before moving on. But with a cut down version, it is hard to see all of the issues.

    In the meantime, I made some changes.
    - There were 2 fields with a special character (%) in the names, so I change it to "Pct".
    - "Start" is a reserved word in Access - I changed it to "Beginning".
    - Both tables do have an Autonumber as the PK field, but you are using an autonumber to have meaning - a Job Number.

    - I like the PK and FK fields to have suffixes - easier for me to know what fields are PK/FK. I changed the obvious ones.


    AutoNumber
    ----------------
    Purpose: Use an AutoNumber field to provide a unique value that serves no other purpose than to make each record unique. The most common use for an AutoNumber field is as a primary key.
    If you need a sequential number for the Job number and you want it created automatically, you should use an custom autonumber routine.


    I changed the 2 form record sources from tables to queries. In "qryLabour", I created a calculated column to calculate man hours. Then that can be used to get a sum.


    EDIT: Also you have a missing "End Sub" in the code.



    What so you think of this??
    Attached Files Attached Files
    HTH
    -----
    Steve
    --------------------------------
    "Veni, Vidi, Velcro"
    (I came; I saw; I stuck around.)

  10. #10
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    6,473
    oth tables do have an Autonumber as the PK field,
    I was going to reply with "In at least one table I saw an autonumber field that was not a key at all." but decided to check and wouldn't you know, it was there. Then I opened the form, then put the table in design view, dismissing the usual warning about not being able to save changes. Guess what - the PK icon is gone again. Never saw that before.

    Click image for larger version. 

Name:	tblLabour.jpg 
Views:	16 
Size:	8.1 KB 
ID:	40231

  11. #11
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    8,486
    @Micron - I opened the main form, then opened the tables in datasheet view, then in design view (several times) - PK icon was always shown; but I'm using A2010.....

    Don't know why so many think they need to format tables/hide fields/make row height double/etc, then use forms where the table formatting doesn't matter.
    HTH
    -----
    Steve
    --------------------------------
    "Veni, Vidi, Velcro"
    (I came; I saw; I stuck around.)

  12. #12
    Giles64 is offline Novice
    Windows 7 32bit Access 2013
    Join Date
    Jul 2015
    Posts
    14
    Thanks for the frankness, on review of the file sent the PKs are not identified (but they are in my original), obviously I have gone about this the wrong way entirely, being new to the forum environment, I thought that it would be less messy and better to reduce the size of the file to only provide the tables and forms that I was having the issue with and so I copied them to a blank database. Big mistake so I have learnt. Going forward is the correct way to send the entire database with just a few records?
    Your feedback is constructive, I will read all your references and try to correct my blunders before trying to solve my original issues.

    Again I appreciate your time and patience and am truly sorry for any inconvenience I have caused.

  13. #13
    Giles64 is offline Novice
    Windows 7 32bit Access 2013
    Join Date
    Jul 2015
    Posts
    14
    Thank you for the modified database, I can see the issues you have raised. I will read the links that Micron has provided and attempt to fix all issues before moving forward.
    Your help and patience is appreciated.

  14. #14
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    6,473
    Again I appreciate your time and patience and am truly sorry for any inconvenience I have caused.
    I certainly admire your appreciation, but you don't have to be so apologetic. No one was born knowing all of this so it's safe to say we were all at the same point at one time. But your attitude is a breath of fresh air!

    I'm not sure what the issue is about pk's. They seem to be there one moment and not the next for me, but maybe not for ssanfu. Maybe it's a version thing. Anyway, the thing that you should invest the most in is normalization - as much as it takes to get a good understanding. It is akin to the footing of a house (some would say foundation, but they've never built a house!). Get it wrong and everything from there on up cracks so you're always fixing. You can always start a new thread about your table schema if need be.

    One thing that I forgot to mention before is to strongly recommend that you ditch the attachment field as it will cause db bloat (file size) issues. It is better to store a folder path to the object, but that's a bit of an advanced topic for now.

  15. #15
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    8,486
    I echo what Micron said about attitude and the attachment field.

    You should have seen my first 2 databases. I had been heavy into Excel, so guess what the table designs looked like. A friend looked a one of the databases (personal use) and told me right to my face that "I had a bad design"! So we've all been there.

    Good luck on your project....
    HTH
    -----
    Steve
    --------------------------------
    "Veni, Vidi, Velcro"
    (I came; I saw; I stuck around.)

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 4
    Last Post: 02-09-2019, 05:38 PM
  2. Replies: 1
    Last Post: 05-22-2017, 05:59 AM
  3. Replies: 5
    Last Post: 07-02-2014, 07:13 AM
  4. Replies: 4
    Last Post: 02-28-2012, 05:26 PM
  5. Setting criteria in a calculated date field
    By SteveReberger in forum Access
    Replies: 0
    Last Post: 10-29-2008, 06:58 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Tech Forums: Microsoft Office Forums