Results 1 to 4 of 4
  1. #1
    arrowmakers is offline Novice
    Windows 10 Access 2016
    Join Date
    Jun 2019
    Posts
    1

    Datediff problem

    I am about to throw this computer out the window, please help......
    I have a query that returns all the value I require, except, that the date is all in one column , nothing I can do about that
    As all the date data is in the same Column I can not use datediff to calculate the date difference, is ther any options open to me I think I have tried all
    options, but being new to access Im not certain if I have other options ie:SQL statement (which I know very little about) or something else



    my data reports back like this......

    dt | Agent_ID | Agent_name | date difference(This field is the one needed)

    20/11/2018 | 11156 | Me |
    22/11/2018 | 11452 | Me |
    30/11/2018 | 23145 | Me |
    Any help would be greatly appreciated

    Cheers

  2. #2
    Join Date
    Jun 2010
    Location
    Belgium
    Posts
    1,035
    Hi,

    could you please give a bit more info: do you mean all data is in one column? If it's only the date that's in 1 column, I would reply: that's normal if it is saved as a date you should have no difficulty to use datadiff.
    And what's the date difference you are trying to calculate: the difference between the given date and the current date, or do you want to compare with another field?

    Kind regards

  3. #3
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    It sounds like they want to use the DATEDIFF function across multiple records on a single field, something Access (or any Relational Database) isn't really designed to do. It is meant to be used across multiple fields within a single record.
    In Relational Databases, the order of the records is not really supposed to matter. Someone once made the metaphor of thinking of it as "bag of marbles", all mixed up, with no order.

    What you want to do can be done, but it isn't really easy or straightforward. One way is to use VBA to create an ordered recordset, and loop through the records, one-by-one, and writing the difference to blank table field that you have (not really a dynamic solution, you would need to run the VBA code every time your data changed).

    A more dynamic approach can be to try to use an embedded query, like shown here: https://dba.stackexchange.com/questi...the-second-col
    What gets tricky is that either the first or last record would have nothing to compare to, and that could cause errors.

    All things being equal, this kind of analysis is much easier to do in Excel than Access.

  4. #4
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Problem with DateDiff
    By Helge12 in forum Access
    Replies: 4
    Last Post: 01-20-2018, 03:30 PM
  2. datediff problem
    By sdel_nevo in forum Programming
    Replies: 5
    Last Post: 06-28-2015, 05:26 AM
  3. Replies: 13
    Last Post: 10-12-2011, 11:08 AM
  4. datediff problem
    By dseeburg in forum Queries
    Replies: 3
    Last Post: 04-07-2011, 06:05 AM
  5. DateDiff
    By mrkaye in forum Forms
    Replies: 18
    Last Post: 11-19-2010, 08:19 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
  •  
Other Forums: Microsoft Office Forums