Transpose SQL to VBA

[McArthurGDM]

Hello,

I am looking to insert some SQL into a RunSQL command, but I am having issues doing so.

Here is the SQL code:

INSERT INTO Initiative ( Initiative_ID, Main_ID, Initiative_Name, Description_Initiative, Plan_Component, Initiative_Requested, Initiative_AoF )
SELECT [2015UnmatchInitiative].Initiative_ID, [2015UnmatchInitiative].Main_ID, [2015UnmatchInitiative].Initiative_Name, [2015UnmatchInitiative].Description_Initiative, [2015UnmatchInitiative].Plan_Component, [2015UnmatchInitiative].Initiative_Requested, [2015UnmatchInitiative].Initiative_AoF
FROM 2015UnmatchInitiative
WHERE ((([2015UnmatchInitiative].Main_ID) Like "*" & "Apr" & "*"));

Note where "Apr" is.

I want this code to cycle through a for loop with a string array. In particular:

Dim strQuery(1 To 16) As String

strQuery(1) = "Jan"
strQuery(2) = "Feb"
strQuery(3) = "Mar"
strQuery(4) = "Q1"
strQuery(5) = "Apr"
strQuery(6) = "May"


strQuery(7) = "Jun"
strQuery(8) = "Q2"
strQuery(9) = "Jul"
strQuery(10) = "Aug"
strQuery(11) = "Sep"
strQuery(12) = "Q3"
strQuery(13) = "Oct"
strQuery(14) = "Nov"
strQuery(15) = "Dec"
strQuery(16) = "Q4"

I want it to loop through for all 16 possibilities.

I am not sure if it's the way that I am writing the code, the syntax or what... but this dosen't work:

for i = 1 to 16

DoCMD RunSQL("INSERT INTO Initiative ( Initiative_ID, Main_ID, Initiative_Name, Description_Initiative, Plan_Component, Initiative_Requested, Initiative_AoF ) SELECT [2015UnmatchInitiative].Initiative_ID, [2015UnmatchInitiative].Main_ID, [2015UnmatchInitiative].Initiative_Name, [2015UnmatchInitiative].Description_Initiative, [2015UnmatchInitiative].Plan_Component, [2015UnmatchInitiative].Initiative_Requested, [2015UnmatchInitiative].Initiative_AoF FROM 2015UnmatchInitiative WHERE ((([2015UnmatchInitiative].Main_ID) Like "*"" & strQuery(i) & ""*"));")

Next i

Any help writing this so it does work would be appreciated.

Thanks

[ranman256]

RunMyQuery "Nov"

sub RunMyQuery (byval pvParam)
dim sSql as string

  sSql = INSERT INTO Initiative ( Initiative_ID, Main_ID, Initiative_Name,  Description_Initiative, Plan_Component, Initiative_Requested,  Initiative_AoF )
SELECT [2015UnmatchInitiative].Initiative_ID,  [2015UnmatchInitiative].Main_ID,  [2015UnmatchInitiative].Initiative_Name,  [2015UnmatchInitiative].Description_Initiative,  [2015UnmatchInitiative].Plan_Component,  [2015UnmatchInitiative].Initiative_Requested,  [2015UnmatchInitiative].Initiative_AoF
FROM 2015UnmatchInitiative
WHERE ((([2015UnmatchInitiative].Main_ID) Like "*" & pvParam & "*"));

docmd.runsql sSql
end sub

[McArthurGDM]

Thanks,

I'm not sure how passing arguments by values and references works. How does this cycle through the 16 values I showed?

[June7]

ranman - forgot to enclose literal text within quote marks.


You have a table/query for each year - 2015UnmatchInitiative?

Could you explain what you are really trying to accomplish - why do you need an INSERT action? Why using LIKE and wildcard instead of =?

One approach would be to populate an array with the 16 values - then in loop pull value from each array element with:

Dim aryParams(16) AS String
aryParams(0) = "Jan"
...
aryParams(15) = "Q4"
For n = 0 to 15
...
WHERE ((([2015UnmatchInitiative].Main_ID) Like "*" & aryParams(n) & "*"));
CurrentDb.Execute sSql
Next

Another approach would involve a table of the 16 values.

[McArthurGDM]

Ok, so something I tried was this:

Dim sSQL As String

Dim strQuery(1 To 16) As String

strQuery(1) = "Jan"
strQuery(2) = "Feb"
strQuery(3) = "Mar"
strQuery(4) = "Q1"
strQuery(5) = "Apr"
strQuery(6) = "May"
strQuery(7) = "Jun"
strQuery(8) = "Q2"
strQuery(9) = "Jul"
strQuery(10) = "Aug"
strQuery(11) = "Sep"
strQuery(12) = "Q3"
strQuery(13) = "Oct"
strQuery(14) = "Nov"
strQuery(15) = "Dec"
strQuery(16) = "Q4"

    For i = 1 To 16
   
        sSQL = "INSERT INTO Initiative ( Main_ID, Initiative_ID, Initiative_Name, Initiative_Requested, Initiative_AoF ) SELECT [2016UnmatchInitiative].Main_ID, [2016UnmatchInitiative].Initiative_ID, [2016UnmatchInitiative].Initiative_Name, [2016UnmatchInitiative].Initiative_Requested, [2016UnmatchInitiative].Initiative_AoF FROM 2016UnmatchInitiative WHERE ((([2016UnmatchInitiative].Main_ID) Like " * " & chr(34) & strQuery(i) & chr(34) " * "));"
   
        DoCmd.RunSQL sSQL
    
    Next i

I get a run time code in the sSQL string text designation (highlighted in bold) that says Run-Time error 13 "TYPE MISMATCH"...

Any idea as to why? Probably because of my use of chr(34), but when I write it another way... I get the same error (show below):

 sSQL = "INSERT INTO Initiative ( Main_ID, Initiative_ID,  Initiative_Name, Initiative_Requested, Initiative_AoF ) SELECT  [2016UnmatchInitiative].Main_ID, [2016UnmatchInitiative].Initiative_ID,  [2016UnmatchInitiative].Initiative_Name,  [2016UnmatchInitiative].Initiative_Requested,  [2016UnmatchInitiative].Initiative_AoF FROM 2016UnmatchInitiative WHERE  ((([2016UnmatchInitiative].Main_ID) Like " * "'" &  strQuery(i) & "'" * "));"

[ssanfu]

Add a Debug.Print statement before the Docmd statement.
Also, is "i" declared as an integer?

(Didn't see anything highlighted)

For i = 1 To 16
   
        sSQL = "INSERT INTO Initiative ( Main_ID, Initiative_ID, Initiative_Name, Initiative_Requested, Initiative_AoF ) SELECT [2016UnmatchInitiative].Main_ID, [2016UnmatchInitiative].Initiative_ID, [2016UnmatchInitiative].Initiative_Name, [2016UnmatchInitiative].Initiative_Requested, [2016UnmatchInitiative].Initiative_AoF FROM 2016UnmatchInitiative WHERE ((([2016UnmatchInitiative].Main_ID) Like " * " & chr(34) & strQuery(i) & chr(34) " * "));"

   Debug.Print sSQL

'        DoCmd.RunSQL sSQL    '<= commented out.
    
    Next i

Post a debug line to see how it is constructed......

[ssanfu]

Oops, just saw a missing ampersand

 Like " * " & chr(34) & strQuery(i) & chr(34) " * "));"

Should be

 Like " * " & chr(34) & strQuery(i) & chr(34) & " * "));"

Still try the Debug Print statement........

[McArthurGDM]

Thanks,

The issue was with syntax. Running SQL strings can be tricky. The correct syntax that I had to use was

"INSERT INTO Initiative ( Main_ID, Initiative_ID, Description_Initiative, Initiative_Name, Initiative_Requested, Initiative_AoF ) 
SELECT [2016UnmatchInitiative].Main_ID, [2016UnmatchInitiative].Initiative_ID, [2016UnmatchInitiative].Desc_Init, [2016UnmatchInitiative].Initiative_Name, [2016UnmatchInitiative].Initiative_Requested, [2016UnmatchInitiative].Initiative_AoF 
FROM 2016UnmatchInitiative WHERE ((([2016UnmatchInitiative].Main_ID) Like  " "*" & "Mar" & "*""));"

Notice the extra quotes around the LIKE function. Not sure why this worked, but it did. There are so many variations of syntax it seems that I can't wrap my head around the pattern.

[June7]

The doubled quotes are to force inner quotes to be treated as literal text and serve as delimiters around the text parameter. Alternative is to use apostrophes:

Like '*Mar*'));"

Note the reduced concatenation. But if you want the month to be dynamic by reference to a control on form:

Like '*" & Me.comboboxname & "*'));"

Or by reference to array element as shown in post 4. However, I erred in that example and used quote mark instead of apostrophe. Copy/paste strikes again. Sorry.