Page 1 of 2 12 LastLast
Results 1 to 15 of 21
  1. #1
    Join Date
    Aug 2004
    Posts
    40

    Unanswered: Calculate Date Difference in an Access 2002 Query?

    Is it possible to calculate the date difference between the previous record and the next record in an Access 2002 query recordset?

    I already have a table called PastResults which contains a Date field that looks like this:
    Date
    20/8/4
    22/8/4
    27/8/4

    I also have a working query which includes the Date field from the PastResults table. The present query outcome looks like this:
    Date
    20/8/4
    22/8/4
    27/8/4

    I would like to enhance the existing query by including a new calculating field called DaysDiff which will return the days elapsed between dates in the query recordset. The query outcome should be enhanced to look like this:
    Date DaysDiff
    20/8/4 0
    22/8/4 2
    27/8/4 5

    I have limited skills and need all the help I can get. Many thanks in advance.

  2. #2
    Join Date
    Aug 2004
    Posts
    40
    simply put something similar to this in a new query field in your query.

    SomeDataDiff: [Date1] - [Date2]
    Last edited by hpicken; 08-27-04 at 19:41.

  3. #3
    Join Date
    Aug 2004
    Posts
    40
    Or as per izyrider has suggested a couple of threads earlier

    Code:
    	  	
    Join Date: Dec 2002
    Location: Préverenges, Switzerland
    Posts: 1,466
    cheat!
    subtract the two dates.
    access dates are days-point-fractionalDays
    
    ...but the officially documented way is:
    daysDifference = datediff("d", oneDate, anotherDate)
    By the way don't use the word datediff as the field name as I suggested in my first reply, use anothe name.

  4. #4
    Join Date
    Jul 2004
    Posts
    125
    'This requires a function call from the query which passes the 1st value to a
    'public function, stores that value in a holding variable, then passes the 2nd
    'value and evaluates it with the holding variable, then replaces the new value
    'in the holding variable and returns the result and so on . . .
    'There also must be a mechanism which clears the holding variable before the
    'first record of the recordset.

    'To do this:
    '1. Create a module and declare a public variable at the module level:
    Public varHoldDate as Variant

    '2. Create a Public Function to clear the variable:
    Public Function fnClearHoldDate()' I name all my functions starting with fn.
    varHoldDate = Null ' Clear the variable before running the query.
    End Function

    'Create a macro:
    '3. the 1st macro line calls fnClearHoldVar():
    RunCode =fnClearHoldVar()

    '4. 2nd macro line calls the query:
    OpenQuery . . . QueryName

    '5. In the query add a field:
    DateDiffCalc: fnCalculateDiff([Date])

    '6. The date difference function call.
    Public Function fnCalculateDiff(ByVal varNewDate As Variant) As Variant
    ' If variable is Null, place the 1st passed date in the holding variable
    If IsNull(varHoldDate) Then
    varHoldDate = varNewDate
    fnCalculateDiff=0 ' 1st date difference is 0
    Else
    fnCalculateDiff = DateDiff("d", varNewDate, varHoldDate) ' assign function's return value.
    varHoldDate = varNewDate ' place new date into the holding variable for the next record.
    End If

    End Function

    You can copy and paste this into your module. I hope this helps and if you have a problem, upload a sample db in Access 2000 and I'll check it for you.

  5. #5
    Join Date
    Aug 2004
    Posts
    40
    "Good Morning All...in particular INEUW" and many thanks for taking the time and trouble to assist me. I have followed your instructions to the letter and double checked it all. Everything should be in place correctly. Unfortunately, When I attempt to run the query I receive a Microsoft Access Message which reads: The specified field [Date] could refer to more than one table listed in the FROM clause of your SQL statement I will some more help to correct this. I am using Access 2002, so I will not attempt to upload anything for you just yet. I believe we are close to solving this. Thanks again. Ronald A. Dixon

  6. #6
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    still there!

    [date] is a reserved word - it's a real bad name for a field.

    ...assuming you fix the name to "myDate" and you still get the same error, then you tell access which myDate you are talking about using
    [queryName].[myDate]
    or
    [tableName].[myDate]

    i.e. [whereItComesFrom].[ItsName]

    izy

    afterthought:

    ...run some tests on ineuw's solution before you commit to it: Access' query optimiser may "decide" not to call the function and may just "assume" that the function returns the same result as the previous call. test by having two dates the same at the top of your query - duplicate dates might persuade the optimiser to make this decision.

    if duplicate dates are possible in your db, you can probably fool the optimiser by including a unique value in the function call, as in:

    get a guaranteed-unique value into your query e.g. a primary key ("myID" long integer in my example) from one of the tables concerned.

    modify the function to:
    '6. The date difference function call.
    Public Function fnCalculateDiff(ByVal varNewDate As Variant, junk as long) As Variant

    modify the call to:
    '5. In the query add a field:
    DateDiffCalc: fnCalculateDiff([Date], [myID]) 'but dont use DATE
    currently using SS 2008R2

  7. #7
    Join Date
    Aug 2004
    Posts
    40
    Yes, I'm still here IZY, I take your point about "DATE" being a bad idea for a fieldname. I can see that clearly now. I will make a start now renaming and changing all references to the DATE field throughout my entire database. I would like to use the alternative fieldname "RaceDate" if you think that will help make things more clearer. I will await your comments on the new fieldname before doing anything else.
    Kind Regards,
    Ronald A. Dixon

  8. #8
    Join Date
    Apr 2004
    Location
    Sydney Australia
    Posts
    369
    What if you add another date field then

    I would make two little macros....with one of the macros doing a RunMacro action for the following macro.

    The first action in the macro to run the macro would be
    GoToControl...your date field
    Sort your existing date field A to Z
    GoToRecord..First

    Then it would run the following macro (RunMacro action) the required number of times.

    GoToControl....The Existing Date Field
    DoMenuItem...Copy. I think that is called DoCommand in later Access
    GoToRecord...Next
    GoToControl...your new date field
    DoMenuItem......paste

    That should result in each entry in your new date field being the same as the entry in your existing date field from the prevous record (previous when sorted A to Z

    For the Repeat Count you can ether enter a number or put

    =[Forms]![YourFormName![TextBoxName]

    The TextBox pn the form could be =Count([IDNo]) or whatever

    Or you can put an expression like

    [Forms]![YorForm]![IDNo] Is Not Null

    Once you have the two dates on the same row then it should be OK.

    PS. I fogot to mention that the count for the Repeat should be one less than the number of records otherwise a new record will be created with a date from the last records existing date field in the new date field. Also, the existing date field you have must have no empty values because copy will not work.

    Mike

  9. #9
    Join Date
    Jul 2004
    Posts
    125
    I apologize for not indicating that [Date] was just an example for the actual field name. As you read the additional replies in this post, there is excellent advice from the other contributors as to never using Access and VB reserved words, as well as how to refer to fields in a query, by including the source table name.

  10. #10
    Join Date
    Aug 2004
    Posts
    40
    Thank You Inuew! I will be a little while longer in making the necessary fieldname changes to my entire database. I am working with a database copy until such time as I resolve things. I propose to rename the existing "Date" field in my PastResults table as RaceDate unless you can see any problems with that? I would really appreciate it if you could find the time to edit the instructions you kindly gave me earlier to include my new field names where necessarry. My query is called qryLyn. My table is called PastResults and my date field will be called RaceDate. Many thanks again to all. Ronald A. Dixon

  11. #11
    Join Date
    Jul 2004
    Posts
    125
    I can do better than that and I uploaded an attachment with 2 procedures. One sets the database folder as the output folder and the 2nd generates a tab delimited text file of your tables, fields and field types. You can reimport the tab delimited text file into a table and analyze your field names as to which need correction.

    To use these procedures, import the file to a module and be sure to reference the DAO library in your references. There are some notes in the procedures as well.
    Attached Files Attached Files

  12. #12
    Join Date
    Aug 2004
    Posts
    40
    Inuew, many thanks again, I have the attachment on my desktop and I've imported it into a new module called Module1. If I understood your reference to DAO library correctly I have ticked the box MicrosoftDAO3.6 Object Library on the Tools - References menu. I'm afraid I have no idea what to do with it now, or how to run it. Should I leave your notes in or take them out? I'm assuming your notes are the two paragraphs that are prefixed with the ' symbol? I think I need to take a break at this point and regroup my senses. I will work steadily through the copy of my database and change all the field names that may not have the correct notation and/or contain words that may cause confusion in the future. On completion of that task I propose to follow your earlier step by step instructions and hope to make the correct fieldname changes to your code. Being completely new to code I find the whole VBA IDE enviroment quite daunting at present. I have invested in three books Access 2002 The Complete Reference, Beginning Access 2002 VBA and Access 2002 VBA Handbook so I'm hoping to gain more experience over the next few months. At present it is almost a case of monkey see monkey do with me and I do need everything spelling out in layman's terms in order to follow it. Sorry to be such a pain but I am most grateful to you all. Kind Regards, Ronald A. Dixon

  13. #13
    Join Date
    Jul 2004
    Posts
    125
    Sorry again: Leave the notes in the procedure, the single quote ' causes the compiler to ignore the lines. This is the common way for programmers to leave notes about the procedures.

    Return to the reference library, (DAO 3.60 is a the right choice) highlight it and click on the Up arrow on the right handside of the display and move the DAO 3.60 selection upwards as far as it goes. This places the library in the maximal prioritised position, otherwise it may report an error. Close the library.

    Also in the module, look on the toolbar under Debug, click on Compile DAOLib. This will tell you if my procedure is OK, or not. (Let me know if it isn't.)

    To run the procedure:
    Make sure the tables are closed. Place the cursor anywhere within the fnOuputTableVars() function body and tap the F5 key which runs the procedure. The action is instantaneous, so you won't see anything. (Run is also found on the toolbar). Also, if you tap F8, the procedure will start step by step and when you had enough of it, just tap F8.

    Open the database folder and you'll see the text file. You can open it with the Notepad or any text editor if you wish to see the contents. Close the file when finished. You can recreate this text file as often as you wish. It overwrites the previous creation.

    In the database, click on Files\Get external data\Import. Select text files, at the bottom of the file dialog find the folder and point to text file and go through the steps of import, but do not select a primary ID key.

    This will create a table containing the table names, field names and the declared variable type. There are no Field name in the export file, so you can rename them if you wish from Field1, Field2, Field3, . . etc.

  14. #14
    Join Date
    Aug 2004
    Posts
    40
    INEUW, I have stepped through your last set of instructions. The results are as follows: Reference Library went well the DAO 3,60 is sitting in third place as high as it will go. Debug was not so good, my only compile option was compile [my project name] no sign of any DAOLib. I clicked on it anyway and nothing happened that I could see and the compile option was then greyed out immediatly. On Run proceedure, when I pressed the F5 key I saw nothing as you rightly predicted. When I pressed F8 repeatedly it stepped through the code but not in sequence it doubled back on itself and finally went around in circles. I closed the window using the red cross in the corner and a message told me it would stop the debug. Everything else went very well and exactly to instruction, and I now have a new table called TableVars. I am printing off all your messages and I will work through them all again when I get the field names sorted. Finding this forum has given me new hope and renewed energy to continue to improve my Access database. I am grateful to all that have taken the time and trouble to help me. Kind regards, Ronald A. Dixon

  15. #15
    Join Date
    Jul 2004
    Posts
    125
    I am glad it worked for you. The word DAOLib appears on Access 2000 toolbar as part of the description of the Debug button and it's irrelevant as long as you understood the concept. When the Debug button greys out, this means that it compiled properly and there are no errors. Otherwise it would stop, highlight the error and give you the error message. The only reason I mentioned the F8 key, is to acquaint you with the some of the tools available to you as you begin to program. This step through option is helpful whether you'll use, DAO, ADO or macros in your procedures, and gives you a chance to test your ideas as you progress. You'll find a number of other helpful tools for coding (within Access) and in time you will have your own collection of programming tools library to suit your methods. In the meanwhile, on this and other forums, you'll find numerous great programmers, each with their own original approach (since there are many ways to skin a cat, especially in VBA), different programming styles and solutions. The most important aspect of seeking help is, to provide as clear as possible explanation of the problem in plain English and we'll take it from there.

Posting Permissions

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