Results 1 to 14 of 14
  1. #1
    Star Lounger
    Join Date
    Aug 2001
    Location
    Cleveland, Ohio, Ohio, USA
    Posts
    76
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Date calculation (2000 SR1)

    Good Evening All,
    I am using the following expression in a query to calculate the Travel time. The underlining table fields are set to Date/Time type with a Default of Now().

    TravelTime: (([TravelToOut]-[TravelToIn])+([TravelFromOut]-[TravelFromIn])+([MiscOut]-[MiscIn]))*24

    I found that the calculation is not always working. In the last 13 records entered, it calculated the Travel time only 4 times - I have a total of 3263 records. I have found that if I use NZ([fieldname]) it works, but I do not have Null values in the fields. But I'm not sure if this is the correct / best method. Not sure if I could use DateDiff() because I need the time in hours. Or do I need to enclose the field names in # - #[fieldname]#. The Travel time is used in several reports and other calculations.

    Thank You
    Jim

  2. #2
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Date calculation (2000 SR1)

    ... Why not use Date Diff in days and multiply the total by 24 to convert to hours?
    Regards,

    Gary
    (It's been a while!)

  3. #3
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Date calculation (2000 SR1)

    >>I found that the calculation is not always working. In the last 13 records entered, it calculated the Travel time only 4 times - I have a total of 3263 records. I have found that if I use NZ([fieldname]) it works, but I do not have Null values in the fields. <<

    You say that "the calculation is not always working", but you didn't say what is going wrong. Is it returning the wrong value? A Null value? or what? Then you say it works when you use NZ(), but that you don't have any Null values. It seems to me that you do.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  4. #4
    Star Lounger
    Join Date
    Aug 2001
    Location
    Cleveland, Ohio, Ohio, USA
    Posts
    76
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date calculation (2000 SR1)

    Mark, Good Afternoon,
    When I would run the query, the Travel time data field in the records would be blank - like a Null value. But in the table field, it shows 12:00 AM. Because of what you said about a Null value, you make me think about how I'm setting the Default value. I'm using 0 as the Default which gave me the 12:00 AM in the Table. I didn't think that 0 would give me a Null.

    Thank you for your time.
    Jim

  5. #5
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Date calculation (2000 SR1)

    I don't quite understand what you are doing. In your recent post, you talked about setting the time values to 0. Yet in your original post, you mentioned using Now() as the default value. All date fields have a date and time component, even though you may think you are using only one or the other. Date fields are kept as double floating point numbers, in which the date is kept to the left of the decimal point, and the time is kept to the right of the decimal (fractions of a day).
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  6. #6
    Star Lounger
    Join Date
    Aug 2001
    Location
    Cleveland, Ohio, Ohio, USA
    Posts
    76
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date calculation (2000 SR1)

    Mark,
    I appoligize!. My original post was wrong. The Default is 0 not Now() as stated. What I wanted was a time in the fields for the clock time. Because 0 gave me 12:00 AM in the table, I thought it was working properly. Apparently the use of 0 in a Date/Time field is improper and it needs to be a true time notation - 00:00 AM or something like that. I'm a little confused in that 0 worked for over 3250 records and not in the 9 records. And errors were not in consective records. Hope this clears things up.
    Jim

  7. #7
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Date calculation (2000 SR1)

    I don't know why it should work most of the time and not others. I guess when you use a value Access isn't expecting (that is, it expects a date/time format), then you can't predict what will happen.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  8. #8
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Date calculation (2000 SR1)

    0 or 00:00 is midnight, and if you don't have a date portion to the date/time, then you can't reasonably expect to do date math with it.

    If you have a null in *any* of the fields in your expression, you'll get a null as a result because of the way that the arithmetic operators work. That's probably why Nz makes it work.
    Charlotte

  9. #9
    Star Lounger
    Join Date
    Aug 2001
    Location
    Cleveland, Ohio, Ohio, USA
    Posts
    76
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date calculation (2000 SR1)

    Good Morning,

    Can I interpret from what you are saying here, is that I Have to use a Default value of, say, #1/1/2002# with a Format for the way I want the time to show?
    "... and if you don't have a date portion to the date/time, then you can't reasonably expect to do date math with it. ..."

    Or should I use #1/1/2002 12:00:00 AM# to make sure that Access will use the time I want?

    I guess, I assumed to much in thinking that Access would handle Time - without an associated date - the way Excel does. And "Assumed" made a Big one out of me. I also assumed that because I had a Default value of 0 for the time fields in the table that I would Not have Null values. This is not true because when I went back to the table, I found several Null values hence the NZ made it work. I did not catch that the [MiscIn] and [MiscOut] fields were Null in the 9 records.

    Because I did not use a date value, is this why I had Null values?

    Thanks,
    Jim

  10. #10
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Date calculation (2000 SR1)

    Date/Times are tricky, and Excel handles them differently from Access. If you have a time formatted field, there's normally a date in there as well. Otherwise, you get a wierd default date (if I recall, it's 12/30/99), which can confuse your date math if a shift runs past midnight. Assuming you used something like Now() to insert the value into the field, you have the date and time for the current day, which is, I think, what you want.

    I can't tell you why you had nulls in those fields. When you say you have a default value of 0 for the time fields, what exactly do you mean? Usually default values are inserted when the new record is created, and I shouldn't think you would want anything inserted into all the fields at that point, only into the initial time field.
    Charlotte

  11. #11
    Star Lounger
    Join Date
    Aug 2001
    Location
    Cleveland, Ohio, Ohio, USA
    Posts
    76
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date calculation (2000 SR1)

    "Date/Times are tricky, " and Frustrating!!! <img src=/S/brickwall.gif border=0 alt=brickwall width=25 height=15> When I use the #1/1/2001 01:00:00 AM#, then I have to enter the date also - there are 19 different times as requested by the person using the database. Have not tried #1/1/2001# - right now, I would guess that it would ask for the date also. I have reset the table Default to #01:00:00 AM#. Do I also need to set the Default for the time fields on the Form? I set the Default to 0 for the Field Properties in the Table Design so that the field would have 12:00 AM or what ever time I use as Default in the new record when it was created. This will show us two possiblities, 1) the data entry person did not enter the value from the time log, or 2) the guys did not write the times down. also, I want a default time value in the fields so I do not have to worry about Null values in my calculations, but maybe I will have to work with the Nulls.

  12. #12
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Date calculation (2000 SR1)

    Sorry, but I don't understand what you're trying to do or why you want to use 1/1/2001 as a default date or 1AM as a time. Are the times being typed in or entered by using the Now() function to insert the current date and time?

    If they're being typed in, you're going to have to grab the time they've entered and concatenate a date to it in the BeforeUpdate event of the control. Otherwise, you have no way to handle travel time over more than a midnight to 23:59.59 time period. I would also recommend you use a 24-hour clock for these calculations so there is no confusion between 12AM and 12PM.
    Charlotte

  13. #13
    Star Lounger
    Join Date
    Aug 2001
    Location
    Cleveland, Ohio, Ohio, USA
    Posts
    76
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date calculation (2000 SR1)

    "What I'm trying to do", calculate time spent for travel to and from a job site, equipment load and unload time at both the company site and job site along with some other misc. times. Also have labor hours for each type of job each employee does. This is for a Landscaping company. "Why I want to use a default date or time" was to prevent Null values in the table so I would not have to worry about them in the calculations. It made no difference to me what the value for the default was, I was trying to work with what you had said in earlier posts.

    If the data entry person has the time values, she will type them in, other wise I wanted a default value entered for the reasons stated above. As for " grab the time they've entered and concatenate a date to it in the BeforeUpdate event of the control", I used the following code which I found in post #124770;

    Private Sub LoadIn_BeforeUpdate()
    Dim WorkDate, LoadIn As Date
    LoadIn = Format(WorkDate + LoadIn, "m/d/yy hh:nn AM/PM")
    End Sub

    and with the AfterUpdate event, with neither event working from what I see when I go to the table to look at the data. I have the format in the table set to m/d/yy hh:nn AM/PM. I would think that AfterUpdate is where that one would want to do the concatenation. All of the times entered for each job are from 7 AM through 6 PM on the same day. None of the times would cross midnight..

    I guess only 2 problem are left, 1) that as I mentioned previously, why when I use a default value would it not be automatically entered in the table. How would you handle this problem? 2) check the various groups of time values - Travel to, Travel from, etc to make sure the calculated time falls within a range - I have to work on that one.
    Thanks,
    Jim

  14. #14
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Date calculation (2000 SR1)

    First, I have to point out that you're declaring WorkDate as a variant but I don't see it getting populated anywhere. Is that what you meant to do?

    The critical thing is that if you want the hours to calculate properly, then all the times need to be associated with the same date. That means, you *do* have to worry about what date value gets entered. You could simply use the Date() function to return the current date and add the input time value to that. I don't see why you're trying to format at all, since format only applies to display, not to the underlying value. Once you have a date/time to insert into the field, the format properties of the control/field will take care of the display.
    Charlotte

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •