Re: Loop query

From: Chris Mills (phad_nospam_at_cleardotnet.nz)
Date: 07/30/04


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.

BTW
[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
forever.
Chris

"neenmarie" <neenmarie@discussions.microsoft.com> wrote in message
news:744BAE90-F3B0-45B0-B5D6-6DB5D61F52D4@microsoft.com...
> 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
the following:
> 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
not counted)
> 2) a recordset in a module that gives me the sum on the above absences by
SS#
> 3) I then need to take care off 90 day drop-offs while keeping a running
balance, so...
> 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 <
[NextAbsenceDate],-1,0"
> (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.
>
> Janine
>