Page 2 of 2 FirstFirst 12
Results 16 to 19 of 19
  1. #16
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    604

    Quote Originally Posted by Ajax View Post
    dates are stored as numbers - and a number is a number as far as format is concerned.
    You're absolutely right! I knew that and I even knew that the time part of a date datatype was stored as a fraction of the day but my brain isn't firing on all cylinders these days... I'm going to blame it on all the self isolation.

  2. #17
    Ajax is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    8,232
    Since the field is the result of a calculation each day could commulative rounding error come into play?
    for each day no. Can't get my head around summing multiple days, but my guess would be around 10,000 days might be +- 1 second

    edit actually think this is the calc

    if 1 second =0.0000115740740740741

    then to increment this to display an extra second would require half this amount (assuming normal rounding rules)

    0.00000578703703703705

    which comes to 172800 (2 days worth of seconds) of errors, all in one direction.

    So bearing in mind the calculation of elapsed time will normally come from deducting one time from another so will 'round' anyway, I suspect summing any multiple of days will be correct unless at least 172800 records all have an error of 0.00000000000000000001 in the same direction

  3. #18
    mindbender is offline Competent Performer
    Windows XP Access 2007
    Join Date
    May 2013
    Posts
    263
    Quote Originally Posted by Ajax View Post
    just a suggestion. Have a subform on your form and in the sourceobject put

    query.nameofcrosstab

    with regards formatting, not tested but in the query design, show the properties for the value column and set the format property to hh:mm:ss or possibly shorttime

    I tried this and it threw an error You can't use a pass-through query or a non-fixed-column crosstab query as a record source for a subform or subreport and I get it but trying to find a way. Basically this report is a monthly phone report where the previous day gets added every day. So some months will be 30, some 31, some 28 depending on the year.

    Maybe there is another way of doing this? I would just like it to be in crosstab format so the dates run across the top for ease of reading

  4. #19
    Ajax is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    8,232
    that error message does not make sense to me - you are not using it as a recordsource, but as an object. But it may be that a passthrough query cannot be a source object- I've never tried. Just noticed (I only picked up from post#4) you said it creates a table, in which case try table.nameoftable

    with regards the format property, if you cannot get your passthrough query to convert to a time using the equivalent of the format function (you said passthrough query, for sql server see this link https://docs.microsoft.com/en-us/sql...l-server-ver15 , for other rdbms's you'll need to research) and you are creating a table then the only way would be to have code to loop through the table fields and setting the format property as required - see this link https://bytes.com/topic/access/answe...roperties-code

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

Similar Threads

  1. Formatting Number in text field
    By MTSPEER in forum Queries
    Replies: 1
    Last Post: 05-11-2017, 01:13 PM
  2. Conditional number formatting
    By numberguy in forum Reports
    Replies: 6
    Last Post: 06-19-2014, 02:55 PM
  3. Chart, keeping number formatting from query
    By Gabriel2012 in forum Reports
    Replies: 3
    Last Post: 12-20-2012, 11:36 AM
  4. Number formatting issues in VBA.
    By Fish218 in forum Forms
    Replies: 1
    Last Post: 03-09-2012, 12:34 PM
  5. Formatting a number field for datasheet view
    By RamiMohaisen in forum Access
    Replies: 3
    Last Post: 02-08-2012, 05:46 PM

Posting Permissions

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