Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
Old June 25th, 2014, 10:19 PM
Registered User
Join Date: Apr 2014
Posts: 4
Thanks: 1
Thanked 0 Times in 0 Posts
Default create an array and fetch into fields under relevant columns

Hi gurus!

I have a query result set [band] like the following below:
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:
FR        UK        US 
AIR       Blur      Bon Jovi 
          Oasis     Green Day 
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:

If Not (rs1.EOF And rs1.BOF) Then 
    rs1.MoveFirst 'Unnecessary in this case, but still a good habit 
    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 
    ' LOOPING ENDS     
But I want to have my array in loop like the following, possible to combine them?
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

Last edited by knvbhk; June 25th, 2014 at 10:21 PM.. Reason: something missed
Old August 21st, 2014, 01:43 PM
Posts: n/a

Good tip, thaks for sharing with us.

Similar Threads
Thread Thread Starter Forum Replies Last Post
To fetch selected fields in reports in asp.net 1.1 SanNil ASP.NET 1.0 and 1.1 Professional 0 February 27th, 2007 04:29 AM
getting data fields from array suni_kutty Crystal Reports 1 November 5th, 2004 06:49 AM
displaying multiple fields in columns rbegonia Classic ASP Basics 3 July 31st, 2004 08:37 PM
displaying multiple fields in columns rbegonia XSLT 0 July 29th, 2004 02:21 PM
Distinct fields from mult columns abbylee26 Classic ASP Basics 4 March 18th, 2004 12:47 PM

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.