[AccessD] Treat string as array

Use of Split() function on compact strings
(Having no pre-existing delimiters)
============================

    Use of Split() function for assigning individual characters of an input string (not having any pre-existing delimiters) to individual elements of an array was demonstrated vide my post dated 25-Mar-2008. 

    This is done by seeding the string with suitable delimiters before applying the Split() function. The seeded delimiter should be chosen in such a manner that it is not likely to be present in the original string.

    In view of JC's subsequent clarification regarding the nature of characters involved, revised function Fn_ConvStringToArray(), based upon this approach, is given below. It uses "<<>>" as a sample delimiter. As stated earlier, the expression on right hand side of statement (A) can also be used directly, as it is based purely upon access built in functions. Fn_ConvStringToArray() merely acts as a wrapper function.

    Note - StrConv() function with vbFromUnicode as the argument, as suggested earlier by Gustav, should be considered the preferred alternative. Fn_ConvStringToArray() as given below, is meant just to demonstrate the feasibility of using Split() function even in absence of pre-existing delimiters.

A.D.Tejpal
------------

' Code in VBA module
'=================================
Function Fn_ConvStringToArray(StrInput _
                                As String) As Variant
    Fn_ConvStringToArray = _
                Split(Format(StrInput, _
                Mid(Replace(String(Len(StrInput), _
                "<"), "<<>>", "<<>>@"), 5)), "<<>>")    ' (A)
End Function
'=================================

  ----- Original Message ----- 
  From: pcs at azizaz.com 
  To: Access Developers discussion and problemsolving 
  Sent: Thursday, March 27, 2008 10:03
  Subject: Re: [AccessD] Treat string as array


  Four days later trailing behind..
  Tested out Gustav's painful exercise... learned something new

  Then had to revisit the split... 
  As John says without a delimiter, the Split function doesn't 
  do much.

  If Delimeter is omitted " " (space) is assumed.
  If Delimeter is provided as "" (zero length string) the 
  whole expression is returned....

  From the VBA Help :
  Split(expression[, delimiter[, limit[, compare]]])

  The Split function syntax has these named arguments:

  Part Description 
  expression Required. String expression containing substrings 
  and delimiters. If expression is a zero-length string(""), 
  Split returns an empty array, that is, an array with no 
  elements and no data. 
  delimiter Optional. String character used to identify 
  substring limits. If omitted, the space character (" ") is 
  assumed to be the delimiter. If delimiter is a zero-length 
  string, a single-element array containing the entire 
  expression string is returned. 
  limit Optional. Number of substrings to be returned; –1 
  indicates that all substrings are returned. 
  compare Optional. Numeric value indicating the kind of 
  comparison to use when evaluating substrings. See Settings 
  section for values. 

  borge


  ---- Original message ----
  >Date: Sat, 22 Mar 2008 22:00:28 -0400
  >From: "jwcolby" <jwcolby at colbyconsulting.com>  
  >Subject: Re: [AccessD] Treat string as array  
  >To: "'Access Developers discussion and problem solving'" 
  <accessd at databaseadvisors.com>
  >
  >Split only works when the string contains "words" or 
  delimited strings.  It does nothing if it is all one big string of characters. 
  >
  >John W. Colby
  >Colby Consulting
  >www.ColbyConsulting.com 

  <<Snipped>>