[AccessD] The ARRAY function

Jim Lawrence (AccessD) accessd at shaw.ca
Thu Dec 18 19:56:50 CST 2003


Hi Jim:

It is not a right or wrong issue, it is an issue of how a program variable
is handled differently from the variable's contents, from one built in
function to another. I have learned something new.

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 12:21 PM
To: Access Developers discussion and problem solving
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

_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com


****************************************************************************
*******
"This electronic message is intended to be for the use only of the named
recipient, and may contain information from Hudson Health Plan (HHP) that is
confidential or privileged.  If you are not the intended recipient, you are
hereby notified that any disclosure, copying, distribution or use of the
contents of this message is strictly prohibited.  If you have received this
message in error or are not the named recipient, please notify us
immediately, either by contacting the sender at the electronic mail address
noted above or calling HHP at (914) 631-1611. If you are not the intended
recipient, please do not forward this email to anyone, and delete and
destroy all copies of this message.  Thank You".
****************************************************************************
*******

_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com



More information about the AccessD mailing list