loop help with inserting records

  • Thread starter Thread starter moonmirage
  • Start date Start date
M

moonmirage

I have a function that returns an integer - let's call that integer
'x'.

How do I insert new records in a table equal to 'x' and insert
something that's modified by x-1 on each pass through the loop? I'm
trying to understand how this would function in Access and the loop
function help doesn't explain it very well.

In particular, I've got [startDate] and [endDate] - short date
formatted. I have the difference between the two in months. Let's
say 1/1/2000 and 3/1/2000. This would return '3' from my function and
I would like 3 new records added to my table with inputs of 1/1/2000,
2/1/2000, and 3/1/2000.

Please help or just point me towards the correct direction if there's
an easier way to do what I want.
 
First create a table CountNumber with field CountNUM containing numbers from
0 (zero) to whatever your maximum spread will be. Use this query,
substituting your table and field names. If it will always be months then
change [Enter increment - d, m, q] to "m" in the DateAdd function.
SELECT [Change Requests].[Date open], DateAdd("d",[CountNUM],[Date open]) AS
[Days after]
FROM [Change Requests], CountNumber
WHERE (((Weekday(DateAdd([Enter increment - d, m, q],[CountNUM],[Date
open]))) Between [startDate] And [endDate]) AND
((CountNumber.CountNUM)<=[returning integer]))
ORDER BY [Change Requests].[Date open], DateAdd("d",[CountNUM],[Date open]);

Once you have it pulling as many records as you need then edit it to an
append query.
 
I have a function that returns an integer - let's call that integer
'x'.

How do I insert new records in a table equal to 'x' and insert
something that's modified by x-1 on each pass through the loop? I'm
trying to understand how this would function in Access and the loop
function help doesn't explain it very well.

In particular, I've got [startDate] and [endDate] - short date
formatted. I have the difference between the two in months. Let's
say 1/1/2000 and 3/1/2000. This would return '3' from my function and
I would like 3 new records added to my table with inputs of 1/1/2000,
2/1/2000, and 3/1/2000.

Please help or just point me towards the correct direction if there's
an easier way to do what I want.
Click to expand...

There are several ways to do this, but the simplest doesn't involve any code
or looping at all!

Instead, create a little utility table named Num with one Long Integer field N
as its Primary Key.

Fill this table with values from 0 through the largest x you'll ever need - be
generous, even with 10000 rows this is a tiny table.

Use an Append query like

INSERT INTO yourtable
SELECT DateAdd("m", N, [StartDate])
FROM yourtable, Num
WHERE N < DateDiff("m", [StartDate], [EndDate])
AND <other criteria to select the record which should supply the start and end
dates>

John W. Vinson [MVP]
 
Thanks for the replies - I've tried to implement both things and I
must not understand it completely just yet.

Here's what I've got (I'm just trying to do this on a blank database):

Table: MonthsBetween
Fields:
startDate (short date),
endDate (short date),
projectId (AutoNumber, primary key)
moneyTotal (currency)
monthsBetween (Long Int)

Table: Num (created as per instructions) with numbers 1-1000.

Table: MonthsEntry
projectId (Number)
selectedMonth (Date/Time) (Formatted to mm-yyyy)
moneyMonth (currency)

I currently have the following query selecting projectId from
[MonthsBetween] with the function returning the integer:

MonthsDiff: DateDiff("m",[startDate],[endDate])-
IIf(Format([endDate],"dd")>=Format([startDate],"dd"),-1,-1)

And it will always be in months - but I'm not sure how to create the
append query to insert project id and then the new function utilizing
the Num table both replies have suggested. I got error messages
trying to implement the new query where it said I wasn't selecting a
field for the new query to go to. The other field in the MonthsEntry
will be null.

Once again, much thanks for the time and help!


I have a function that returns an integer - let's call that integer
'x'.
Click to expand...
How do I insert new records in a table equal to 'x' and insert
something that's modified by x-1 on each pass through the loop? I'm
trying to understand how this would function in Access and the loop
function help doesn't explain it very well.
Click to expand...
In particular, I've got [startDate] and [endDate] - short date
formatted. I have the difference between the two in months. Let's
say 1/1/2000 and 3/1/2000. This would return '3' from my function and
I would like 3 new records added to my table with inputs of 1/1/2000,
2/1/2000, and 3/1/2000.
Click to expand...
Please help or just point me towards the correct direction if there's
an easier way to do what I want.
Click to expand...

There are several ways to do this, but the simplest doesn't involve any code
or looping at all!

Instead, create a little utility table named Num with one Long Integer field N
as its Primary Key.

Fill this table with values from 0 through the largest x you'll ever need - be
generous, even with 10000 rows this is a tiny table.

Use an Append query like

INSERT INTO yourtable
SELECT DateAdd("m", N, [StartDate])
FROM yourtable, Num
WHERE N < DateDiff("m", [StartDate], [EndDate])
AND <other criteria to select the record which should supply the start and end
dates>

John W. Vinson [MVP]
Click to expand...
 
Back
Top