[AccessD] The ARRAY function

A.D.Tejpal adtp at touchtelindia.net
Thu Dec 18 22:39:15 CST 2003


Jim (DeMarco),

    My communication was intended for Jim (Lawrence) and meant to reinforce your excellent advice to him.

Regards,
A.D.Tejpal
--------------
  ----- Original Message ----- 
  From: Jim DeMarco 
  To: Access Developers discussion and problem solving 
  Sent: Friday, December 19, 2003 01:50
  Subject: RE: [AccessD] The ARRAY function


  I think you misunderstood me.  Yes you can pass variables to Array as arguments as long as you separate your arguments with comma (as you've done here).  I was pointing out that the string "Tom, Dick, Harry" passed into Array as one string is not the same thing as passing each individual item as separate arguments to Array (whether literal or variable).

  Jim DeMarco

  -----Original Message-----
  From: A.D.Tejpal [mailto:adtp at touchtelindia.net]
  Sent: Thursday, December 18, 2003 1:03 PM
  To: Access Developers discussion and problem solving
  Subject: Re: [AccessD] The ARRAY function


  Jim,

      Basically, there is nothing wrong with the use of variables as arguments to an Array() function. However, it has to be kept in mind that such arguments have to be interpretable as comma-delimited list of values.

      In the code originally sent by you, the whole string represented by variable StrStuff was getting interpreted as one vale (the commas treated as internal to the string). As a result you were getting a single element array and the value of  varArray(0) was correctly being shown as the string containing all the three names.

      Sample code given below illustrates the use of variables as arguments to Array() function.

  Regards,
  A.D.Tejpal
  --------------
  -------------
  Code Start
  -------------
  Sub TestArray()    
  ' Return value of Array function is Variant type
      Dim varArray As Variant, StrStuff1 As String
      Dim StrStuff2 As String, StrStuff3 As String
      
      StrStuff1 = "Tom"
      StrStuff2 = "Dick"
      StrStuff3 = "Harry"
  ' Print strStuff1 To 3
      Debug.Print StrStuff1 & ", " & StrStuff2 & ", " & StrStuff3
  '   Output = Tom, Dick, Harry

  ' Run the ARRAY function to place the contents of 
  ' strings into an array. (arguments for Array function 
  ' have to be in the form of a comma-delimited list of values)
      varArray = Array(StrStuff1, StrStuff2, StrStuff3)
  ' Print the first index.
      Debug.Print varArray(0)
  '   Output = Tom
  End Sub
  -------------
  Code End
  -------------
    ----- Original Message ----- 
    From: Jim Lawrence (AccessD) 
    To: Access Developers discussion and problem solving 
    Sent: Thursday, December 18, 2003 21:23
    Subject: RE: [AccessD] The ARRAY function


    Jim

    Thanks for the information...much appreciated.

    The actual line feed in through variable was:
    "Tom", "Dick", "Harry"  not "Tom, Dick, Harry"

    Visually there is no difference between the manually entered string and the
    'generated' variable.

    I think the split function will work.

    Again THanks
    Jim

    -----Original Message-----
    From: accessd-bounces at databaseadvisors.com
    [mailto:accessd-bounces at databaseadvisors.com]On Behalf Of Jim DeMarco
    Sent: Thursday, December 18, 2003 5:16 AM
    To: Access Developers discussion and problem solving
    Subject: RE: [AccessD] The ARRAY function


    Yes that solved your problem but maybe some explanation on why your original
    code failed might help others?  If not please read on.

    The difference between:
    strStuff = "Tom, Dick, Harry"
    varArray = Array(strStuff)
    Debug.Print varArray(0) 'prints "Tom, Dick, Harry"

    and
    varArray = Array ("Tom", "Dick", "Harry")
    Debug.Print varArray(0) 'prints "Tom"

    is that the Array function needs to be fed the individual array elements as
    separate arguments.  Yes they look comma delimited but it's just to separate
    the arguments.  Feeding in variable that contains strings separated by a
    comma is not the same thing.

    The Split function that worked for you took your string a created an array
    based on the "," delimiter which was then assigned to your variant.

    Jim DeMarco

    -----Original Message-----
    From: Jim Lawrence (AccessD) [mailto:accessd at shaw.ca]
    Sent: Wednesday, December 17, 2003 9:39 PM
    To: Access Developers discussion and problem solving
    Subject: RE: [AccessD] The ARRAY function


    Brilliant; Charlotte

    Many thanks.
    Jim

    -----Original Message-----
    From: accessd-bounces at databaseadvisors.com
    [mailto:accessd-bounces at databaseadvisors.com]On Behalf Of Charlotte
    Foust
    Sent: Wednesday, December 17, 2003 5:57 PM
    To: Access Developers discussion and problem solving
    Subject: RE: [AccessD] The ARRAY function


    If you want to use a variable, use Split instead of Array().  Then you
    don't need all those extra chr(34)s, etc.

    Public Function TestArray()
        Dim strStuff As String
        Dim vararray As Variant
        strStuff = "Tom, Dick, Harry"
        Debug.Print strStuff
        ' Run the Split function to place the contents of the string into an
    array.
        vararray = Split(strStuff, ",")
        Debug.Print vararray(0)

    End Function

    Charlotte Foust

    -----Original Message-----
    From: Jim Lawrence (AccessD) [mailto:accessd at shaw.ca]
    Sent: Wednesday, December 17, 2003 5:28 PM
    To: Access Developers discussion and problem solving
    Subject: [AccessD] The ARRAY function


    Hi All:

    I am running in some strange responses, maybe too much chocolate has
    fuddled the brain but:

    Given the following:
    ' Set strStuff
    strStuff = chr(34) & "Tom" & chr(34) & ", " & chr(34) & "Dick" & chr(34)
    & ", " & chr(34) & "Harry" & chr(34) ' Print strStuff ? strStuff "Tom",
    "Dick", "Harry" ' Run the ARRAY function to place the contents of the
    string into an array. varArray = Array(strStuff) ? varArray(0) ' Print
    the first index. "Tom", "Dick", "Harry" ' OK ????? ' Now type in the
    same information manually or cut and paste... varArray = Array("Tom",
    "Dick", "Harry") ? varArray(0) ' and the results are what they should
    be. Tom

    What is going on and why can a variable not be used in the ARRAY
    function?? :-(

    Any suggestions would be greatly appreciated.
    Jim



More information about the AccessD mailing list