Sort cut for multiple Addnew

[MarionD]

Good Morning,
I have a function that calculates Holidays from Easter Sunday..(thanks again to this forum)

The FT1 to FT25 variables are declared as Dates.
I want to send these dates to a local table.
Is there a way of doing this without having to code 25 Addnews?

I was think something like For I=1 to 25
FTDays.AddNew
FTDays![FTDate] = "FT" & I
FTDays.Update
Next I

but that doesn't work
Or am I thinking eutopia?

******************
FT17 = DateSerial(Intjahr, 12, 24)
FT18 = DateSerial(Intjahr, 12, 25)
FT19 = DateSerial(Intjahr, 12, 26)
FT20 = DateSerial(Intjahr, 12, 31)
FT21 = DateSerial(Intjahr, 10, 31)
FT22 = FT1 - 3

FTDays.AddNew
FTDays![FTDate] = FT17
FTDays.Update

FTDays.AddNew
FTDays![FTDate] = FT18
FTDays.Update

FTDays.AddNew
FTDays![FTDate] = FT19
FTDays.Update
********************************
Thanks !

 

[arnelgp]

You can use Sql:

Dim i As intege
For i = 1 to 25
Currentdb.Execute "Insert Into theTableName (FTDate) Select " & Format$(DateSerial(Intjahr, 12, i), "\#mm\/dd\/yyyy\#")
Next

 

[MarionD]

Hi there,
I think maybe I didn't explain too well.

FT1 = DateSerial(Intjahr, Monat, OsterTag) 'TM_OsterSonntag
FT2 = FT1 + 1 'Ostermontag
FT3 = FT1 - 2 'Karfreitag
FT4 = FT1 + 39 'Himmelfahrt
FT5 = FT1 + 49 'Pfingstsonntag

FT1 through to FT25 have a Date value
FT1 = 02/04/2021
FT2 = 03/04/2021
FT3= 02/04/2021 plus 39 days etc.

So using the sql like that would insert the i day of the 12th month .....

I need to insert the value of FT1, then the Value 0f FT2 etc.

Thanks!

 

[The_Doc_Man]

Your design choice of having these dates as variables makes your request nearly impossible because SQL cannot see local variables. SQL could do a bulk insert if those dates were already in a table, but in a local memory array or list of variables, you are stuck.

What ArnelGP suggested is the SQL equivalent to your loop of .AddNew methods. You can do a recordset-based .AddNew directly or an SQL-based INSERT INTO using string substitution for one value at a time. There is no syntax for SQL to directly grab those variables.

The reason is that Access, which manages VBA, does not actually execute SQL. It passes SQL to the ACE DB engine for execution because that is the way Access does SQL. Because that ACE engine is active as a separate task, it has its own memory. Variables are in the Access task. SQL runs in the ACE task. ACE cannot reach back to Access to get something in a bulk operation such as you desire.

Note that if you had chosen an ARRAY of dates FT(1...25) rather than FT1, FT2, FT3, ..., FT25, then a loop would have been really easy.

For I = 1 to 25
    FTDays.AddNew
    FTDays![FTDate] = FT(I)
    FTDays.Update
Next I

 

[MarionD]

Thank you so much for the excellent answer and the explanation! Really appreciated!

I am trying to fix this code that was written in 2001.... it's even more complicated because there is a table, but only contains the Name of the Holiday and an active yes no field. Based on which Holidays they have clicked as active, I am trying to insert only the active holidays into a local table, then print an attendance register where the backcolour of the active holidays are grey!

Thanks again for the help - hope I will manage now

 

[MarionD]

Oh dear - that doesn't work either - error - function FT not defined..
Oh sorry I see now you menat with an array...

 

[theDBguy]

FT1 = DateSerial(Intjahr, Monat, OsterTag) 'TM_OsterSonntag
FT2 = FT1 + 1 'Ostermontag
FT3 = FT1 - 2 'Karfreitag
FT4 = FT1 + 39 'Himmelfahrt
FT5 = FT1 + 49 'Pfingstsonntag

As @The_Doc_Man said, using a table would make it easier to add your data. With that in mind, you could simply store the number of adjustments to your FT1 date (based on what I see from your code snippet). For example, your table might contain simply these:

tblDays
[DaysOffset]
0
1
-2
39
49

Then, in your code, you could use an INSERT query like:

FT1 = DateSerial(Intjahr, Monat, OsterTag) 'TM_OsterSonntag
strSQL = "INSERT INTO TableName (DateField) SELECT #" & FT1 & "# + [DaysOffset] FROM tblDays"
...

Hope that helps...

 

[MarionD]

Thanks for the Input everyone. I've got it doing what I want - not very elegantly, but at least it works.

 

[theDBguy]

Thanks for the Input everyone. I've got it doing what I want - not very elegantly, but at least it works.

Glad to hear you got it sorted out. You might consider posting your solution in case it helps someone else in the future. Good luck with your project.