For loop using variable in select statement (1 Viewer)

[Lkwdmntr]

Hello, I'm trying something that is a bit difficult but will be worth it if it works.
I need to copy records from the table "tblMealcomponentsPerWeeklyPlan" with the "WeeklyPlanIDFK"=Tempvars!TempPlanID and the "DOW"=1. When inserting the records I need to add a day to the date "Date: DateAdd("d",1,[MealDate])". Then I need to do that for the rest of the days of the week(2-7).

I was hoping to put a For Loop in my code to accomplish this. I think I could use the loops counter to add a day to the "Date: DateAdd("d",1,[MealDate])" . I'm thinking something like this "Date: DateAdd("d",i,[MealDate])" where "i" is the loop variable.

I have the SQL statement that works with the query, but I sure don't want to have a different query for each day of the week. I also need to do this for each meal category (3-9). I would actually need a total of 49 queries to pull this off. You can see why I'm looking for a better solution.

Here is the append query that works for copying Monday's data into Tuesday for all the meal components for meal category 3. I know it's a bit confusing, but I think I explained it pretty well.

INSERT INTO tblMealComponentsPerWeeklyPlan ( WeeklyPlanIDFK, ActionsPerWeeklyPlanIDFK, MealDate, WeekDay, DOW, ComponentType, FoodItem, Qty, Measurement, Sequence, MealCatagory )
SELECT tblMealComponentsPerWeeklyPlan.WeeklyPlanIDFK, tblMealComponentsPerWeeklyPlan.ActionsPerWeeklyPlanIDFK, DateAdd("d",1,[MealDate]) AS [Date], tblMealComponentsPerWeeklyPlan.Weekday, 2 AS Expr1, tblMealComponentsPerWeeklyPlan.ComponentType, tblMealComponentsPerWeeklyPlan.FoodItem, tblMealComponentsPerWeeklyPlan.Qty, tblMealComponentsPerWeeklyPlan.Measurement, tblMealComponentsPerWeeklyPlan.Sequence, tblMealComponentsPerWeeklyPlan.MealCatagory
FROM tblMealComponentsPerWeeklyPlan
WHERE (((tblMealComponentsPerWeeklyPlan.WeeklyPlanIDFK)=[TempVars]![TempPlanID]) AND ((tblMealComponentsPerWeeklyPlan.MealCatagory)=3) AND ((tblMealComponentsPerWeeklyPlan.DOW)=1));

I am not sure where to begin writing the code, I know something has to be done for accessing the database, but not sure about all the steps.

I know this is a big ask, but I'm hoping this is cake for some of the pro's out there.

 

[pbaldy]

Sounds like this type of thing:

For D = 1 To 7
  For M = 3 To 9
    'build and execute SQL using variables
  Next M
Next D

You incorporate the variables like:

"...AND ((tblMealComponentsPerWeeklyPlan.MealCatagory)= " & M & ") AND..."

 

[Lkwdmntr]

Sounds like this type of thing:

For D = 1 To 7
  For M = 3 To 9
    'build and execute SQL using variables
  Next M
Next D

You incorporate the variables like:

"...AND ((tblMealComponentsPerWeeklyPlan.MealCatagory)= " & M & ") AND..."

 

[Lkwdmntr]

What about the "Date: DateAdd("d",D,[MealDate])"? How would I use the "D" variable in the sql?

 

[pbaldy]

Try

"...ActionsPerWeeklyPlanIDFK, DateAdd('d'," & D & ",[MealDate]) AS [Date],..."

 

[pbaldy]

You may find this of assistance:

SQL Converter - AccessUserGroups.org

Convert your Access/Jet SQL queries into T-SQL to run on Microsoft SQL Server. It supports converting the most commonly used functions.

accessusergroups.org