Hi gurus!
I have a query result set [band] like the following below:
Code:
Band Country
AIR FR
Bon Jovi US
Oasis UK
Blur UK
Green Day US
Metalica US
My final view I want is like the one as below, I have already used VBA to create a temp table [tmpBand] with 3 columns, namely FR, UK & US, column names created depends on Country in [band] above:
Code:
FR UK US
AIR Blur Bon Jovi
Oasis Green Day
Metalica
I want to loop through the [band], so I want to create arrays, for example, arrayFR = "AIR"; arrayUK = "Blur vbCrLf Oasis" and arrayUS = "Bon Jovi vbCrLf Green Day vbCrLf Metalica". Then fetch the relevant arrays under the correct country columns. I tried to insert into [tmpBand] but seems not successful. What I should do? My orginal code is something like:
Code:
If Not (rs1.EOF And rs1.BOF) Then
rs1.MoveFirst 'Unnecessary in this case, but still a good habit
' LOOPING STARTS
Do Until rs1.EOF = True
'DO SOMETHING HERE TO CREATE ARRAYS LIKE countryBandFR, countryBandUK, countryBandUS......
'THEN countryBandFR="AIR"; countryBandUK="Blur,vbCrLf,Oasis"; countryBandUS="Bon Jovi,vbCrLf,Green Day,vbCrLf, Metalica"
'INSERT the countryBand arrays to the temp table [tblTempBandCountry_Case_N] under relevant columns
rs1.MoveNext
Loop
' LOOPING ENDS
Else
But I want to have my array in loop like the following, possible to combine them?
Code:
for my array in loop, I want to have something like those below, array myFriends will store those values from the loop and join together:
Dim strFriends(0 To 6) As String, lngPosition As Long
strFriends(0) = "Alpha"
strFriends(1) = "Bravo"
strFriends(2) = "Charlie"
strFriends(3) = "Delta"
strFriends(4) = "Echo"
strFriends(5) = "Foxtrot"
strFriends(6) = "Golf"
Dim myFriends As String
'This will produce the following string: "Alpha, Bravo, Charlie, Delta, Echo, Foxtrot, Golf"
myFriends = Join(strFriends, ", ")
MsgBox myFriends
create an array and fetch into fields under relevant columns
