Re: Loop query
From: Chris Mills (phad_nospam_at_cleardotnet.nz)
Date: Fri, 30 Jul 2021 14:37:15 +1200
I'm sorry, I can't write your function for you even if I understood it.
If you don't know VBA then that sortof puts the killer on it. Back to
expressions I guess! Perhaps the Queries newsgroup can help.
Though most of the stuff can be done in queries (or sub-queries to break the
problem up), your point 3A stumps me (ie how to associate a record with the
next record so as to compare dates, all in an expression). I would probably
have run through the table storing intervals in another field, not really
pretty but simple.
[date of absence] >Date()-365
seems to me better than
[date of absence]+365 >Date()
for purely practical reasons in writing a query, including I'm not sure about
adding a number I'd use DateAdd("y",-1,Date()).
If you're going to continue with Access, y'know you can't ignore learning VBA
"neenmarie" <email@example.com> wrote in message
> Thx for you reply. It sounds like what I need, but is beyond me. I've only
worked with expressions in a query, not modules and don't know VBA. Can you
help me more?
> From what you've written, it sounds like I need VBA to put in a module for
> 1) a recordset in a module that gives me [employee
SocNumber],[absence],[date of absence] where [date of absence]+365 >Date()
..... (To give me only absences within the last year - year old absences are
> 2) a recordset in a module that gives me the sum on the above absences by
> 3) I then need to take care off 90 day drop-offs while keeping a running
> 3A) Can I find the next absence date (next record) per SS#? To be able to
compare each current record's absence date to the next record's absence date
(by SS#) to be able to say something like "If [AbsenceDate] + 90 <
> (This would allow me to credit the employee with an absence for going 90 or
more days without missing any time)
> 3B) If there is no "NextAbsence", I would need something like..."If
[AbsenceDate] + 90 < Date() or Now(), -1,0".
> I hope that all makes sense. And thank you so much for your help.