Re: Loop query
From: neenmarie (neenmarie_at_discussions.microsoft.com)
Date: Fri, 30 Jul 2021 06:09:02 -0700
I'm just starting to understand that I need to learn VBA. It's on my list. I've been able to get the results I wanted with expressions and didn't realize I needed it until recently. After looking for an answer to this question, I've just started to realize what it can do for me.
Thx for you help anyhow. When you write that you can't write a function for me.....is that a personal guideline or a rule of this forum that I stepped over?
"Chris Mills" wrote:
> 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" <firstname.lastname@example.org> 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
> 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
> > 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 <
> > (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