Results 1 to 12 of 12
  1. #1
    Join Date
    Jun 2009
    Posts
    15

    Unanswered: Using DateDiff with field in previous record

    OK, moderately experienced DB guy here and I need help with this one. I have a database that helps me keep track of my homebrewing batches. I keep track, in a subform, the dates that I do things - ie racking, bottling, adding sugar, adding more yeast, adding fruit. What I would love to have to make my life easier is this:

    DateDiff('d',[date_of-action],[date-of-action]-1)

    in other words, i want to use the date field in the previous record as the second date. I have tried using dlookup function to get the second date, but I can't get it to work. Would like to use a query, but am open to other ideas!

  2. #2
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    One way:

    Previous values
    Paul

  3. #3
    Join Date
    Jun 2009
    Posts
    15
    AMAZING! Thank you, thank you, thank you, thank you, thank you!!! LOL I do have one question though. When I run the query, bam, works great. But when I try to have the query results show up in a subform (datasheet view) I get an error. there are only 3 to 6 dates linked to each record, and none of them will show up. Any thoughts? And thanks again!

  4. #4
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    If the query runs on its own, it should run as the source of a form. Is there a reference in the query to a form, which might change if it was a subform? Can you post the db?
    Paul

  5. #5
    Join Date
    Jun 2009
    Posts
    15
    LOL - figured out my problem. Need to pull my data from the query, not the table. Works like a champ! thanks again! now i need to figure out how to set the focus on a calendar that pops up when i click on the date box in the subform. everything i've tried doesn't work.

  6. #6
    Join Date
    Jun 2009
    Posts
    15
    OK, now i have another problem. If I set the form to pull the query results, I can't edit the data, since it is query snapshot. how can i edit this data easily?

  7. #7
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    That is a downside to this method. The subquery causes the whole thing to be read-only. It's best for a report, where you just want to view the data. Two alternatives that come to mind are domain aggregate functions and a custom function. Either should leave the rest of the query editable. I'd probably lean towards the function, if you're familiar with how to create one. You'd pass the relevant values (in my sample db, the car number and date) and it would return the value from the previous record.
    Paul

  8. #8
    Join Date
    Jun 2009
    Posts
    15
    here is the mess of a database!
    thanks again!
    Attached Files Attached Files

  9. #9
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    See if this is doing what you want. I have to work on a project right now, so haven't had time to look at the calendar thing. I never use the ActiveX calendar.
    Attached Files Attached Files
    Paul

  10. #10
    Join Date
    Jun 2009
    Posts
    15
    that is exactly what i was trying to do! Thank you yet again! The calendar part actually works correctly for me. I just can't figure out how to set the focus from a subform to a main form and visa versa.

    thanks again! if you would like that bottle, let me know. i'll send you a list.

  11. #11
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    No problemo. I appreciate the offer, and normally I'd NEVER turn down free beer or wine, but I'd think the cost and hassle of shipping make it impractical. Next time you're in Nevada, you can bring me a bottle!
    Paul

  12. #12
    Join Date
    Jun 2009
    Posts
    15
    LOL - i'm only in WY and will be in Reno in late august for a family wedding. offer still stands though! that little feature is a gold mine for me!

Posting Permissions

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