Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
Password Reminder
Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
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 tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developersí questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Thread Tools Display Modes
  #1 (permalink)  
Old June 25th, 2014, 10:19 PM
Registered User
Points: 19, Level: 1
Points: 19, Level: 1 Points: 19, Level: 1 Points: 19, Level: 1
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
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
Reply With Quote
  #2 (permalink)  
Old August 21st, 2014, 01:43 PM
Posts: n/a

Good tip, thaks for sharing with us.
Reply With Quote

Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off

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 03:29 AM
getting data fields from array suni_kutty Crystal Reports 1 November 5th, 2004 05: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 11:47 AM

All times are GMT -4. The time now is 09:40 AM.

Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.