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:
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!
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!
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.
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.
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.
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!