Hot Fox: [Access VBA Central] Re: Loop a Query / SQL Statement

sexta-feira, 11 de setembro de 2009

[Access VBA Central] Re: Loop a Query / SQL Statement

I have an append query that goes and looks for any records that didn't have an entry within 30 days from that date. If there is no entry within 30 days it inserts a -1 record. But the issue is that it's just a one time run.

Example if someone had an entry on 1/1/09 and they had 5 points against them. Then 30 days go by 2/1/09 and they didn't have an infraction. This Append query would enter in a -1 record. But the issue is lets say they didn't have any other infractions for 90 days the Append query only runs once. I would need a way to have it loop through to current date and keep requerying the fact table.

Am I explaining myself well?

-Joe

--- In AccessVBACentral@yahoogroups.com, "whowhatwhenaware" <stirthingsup@...> wrote:
>
> Ok, hate to be rude, but your code simply doesn't make a whole lot of sense.
>
> Can you describe what you're trying to do, not in code, but just in simple terms? What is the original problem you're trying to solve?
>
> Like someone already said, we don't think you need a loop to do this. A simple append query is probably all you need, but unless we understand your goal, we can't help you.
>
> --- In AccessVBACentral@yahoogroups.com, "joefonseca79" <joefonseca79@> wrote:
> >
> > I have a request to do a loop to automate a Query that appends the -1 if the infraction didn't happen. I tried to create a module to do this and then embed this into the Home screen when the app loads. But as you can see I am lacking my VBA Skills. Can someone help me with what I am doing wrong.
> >
> > ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- -----
> >
> > Option Compare Database
> >
> > Function MinusOneLoop(MinusOneLoop) As Long
> >
> >
> > 'Dimension Variables
> > Dim rs As Recordset
> > Dim db As Database
> >
> >
> > 'Loop Through Query Results
> > Set rs = db.OpenRecordset("SELECT Count(*) AS MinusOneCheck FROM FACT AS F GROUP BY F.EMP_ID HAVING (((DateDiff('d',Max([STARTDATE]),Now()))>=30));")
> >
> > Do While rs >= 1
> >
> > DoCmd.OpenQuery ("qry_30Days_APPEND")
> >
> > Loop Until rs = 0
> >
> >
> >
> > End Function
> >
>