SQL query loop

[Timoo]

Hi,

Up to now I have to say this forum is The Best!
Found só many solutions here, all in bits and pieces of code!
Thanks you all, for posting here.

Now, I have a small, little problem.
I want to update columns in a table, 1 by 1 (12 months in a row).
This is the code I use:

UPDATE tbl_Retail_CM_2012 INNER JOIN CalcLastYear ON tbl_Retail_CM_2012.[Dealer] = CalcLastYear.[Cust No] SET CalcLastYear.[1] = [tbl_Retail_CM_2012]![Total Retails]
WHERE (((tbl_Retail_CM_2012.Month)=1));

I do this for all 12 months, calling query by query in a macro. And I want to transform it into a module, so I can get rid of all the queries. How should I do this?

Thanks in advance,
Timo

 

[DavidAtWork]

try this:
Dim i As Integer

For i = 1 to 12
UPDATE tbl_Retail_CM_2012 INNER JOIN CalcLastYear ON tbl_Retail_CM_2012.[Dealer] = CalcLastYear.[Cust No] SET CalcLastYear.[1] = [tbl_Retail_CM_2012]![Total Retails]
WHERE (((tbl_Retail_CM_2012.Month)= " & i & "))

Next i

David

 

[DavidAtWork]

sorry need to add the Docmd.RunSQL("UPDATE tbl_Retail_CM_2012 INNER JOIN CalcLastYear ON tbl_Retail_CM_2012.[Dealer] = CalcLastYear.[Cust No] SET CalcLastYear.[1] = [tbl_Retail_CM_2012]![Total Retails]
WHERE (((tbl_Retail_CM_2012.Month)= " & i & "))")
inside the For loop

 

[Timoo]

thank you, this works indeed!

Question: right now I am plugging a lot of SQL queries into VB.
Running them one after another as a series of updates.
Would there be a more clever way of doing so?

Thanx,
Timo

 

[DavidAtWork]

Well that depends on what you're updating, if it's repetitive updating like your original post, then a Loop method might be doable. If you post some more details, to get a better understanding of what's involved
David

 

[Timoo]

Hi David,

First of all, thanks for your help.
Then: Now I get a compile error.
"compile error: constant expression required"

Let me start with the beginning, because it goes wrong on the first line:

[I][B]Function Rebuild_YTD(iYear As Integer)[/B][/I]
    Dim i As Integer
    Dim iPrev As Integer
    Dim iFirst As Integer
    Dim strSQL As String
     
    iPrev = iYear - 1
    iFirst = iYear - 8

.....

     'Import Retail information
    Const strPath As String = "\\wat-be8k230\DICE EMEA Aftermarket\03. Parts Customer Service\DICE-EMEA Parts\Install_Base\import\Retail_" & iYear & ".xlsx"

The last line is where it fails on iYear

I call the function through a macro (name: RebuildYTD_2012) with a button like this: RunCode Rebuild_YTD(2012)
Any idea why it fails?

Thanx,
Timo

 

[Timoo]

Ok, solved it by removing the 'Const' strPath 'as string' and replacing it with a Dim + strPath = "stringzzz"