Results 1 to 9 of 9
  1. #1
    Join Date
    Jan 2002
    Location
    London
    Posts
    73

    Unanswered: How to use Char(13) in a query

    Hi i have a whole table full of address which i have extract from Oracle and i want split the addresss in to different fields. Eg Address1, Address2, Town, Country, PoctCode. At present everything is in 1 field. When i have imported the data the Address field looks like below

    4 CRESCENT WEST(sqaure symbol) CLEVELYS (sqaure symbol) BLACKPOOL (sqaure symbol) LANCS.

    I know you have to use the char(13). Has anyone got anyidea how to write this in a query or vba

    Thanks

  2. #2
    Join Date
    Dec 2003
    Location
    Dallas, TX
    Posts
    1,004
    Hi Hemish,

    chr(13) is the carriage return (CR). Also chr(10) is the line-feed (LF) character. Together they form the NewLine pair. That doesn't actually place anything in different fields, just visually arranges them on another new line. Are you in fact wanting to Extract or Strip the Address data out and place in another Field OR in another Table?? As for pulling out just those Address fields you imported you will find numerous posts to that effect. I myself have never had to use that feature but it has something along the lines of [[b] Left, Mid, Right functions. Hang around and some help will be on the way.

    have a nice one,
    BUD

  3. #3
    Join Date
    Dec 2003
    Location
    Dallas, TX
    Posts
    1,004
    MESSAGE REMOVED
    Last edited by Bud; 01-28-05 at 05:59. Reason: silly thing posted twice...REMOVED

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    As Bud says form your address by adding the new line symbol it is made up of ANSI(13) & ANSI(10) characters
    Id suggest you change the query, if you can, to add those characters to the extract query
    eg
    select Address1 & chr$(13) & chr$(10) & address2 & chr$(13) & chr$(10).....


    as to stripping out an individual line of the address then I suppose you could search for the occurance to those characters in the address using instr
    Last edited by healdem; 01-28-05 at 06:14.

  5. #5
    Join Date
    Jan 2002
    Location
    London
    Posts
    73

    Char (13)

    Hi

    I have tried the follwing and it has not work. Not sure where i'm going wrong

    Below is the query in SQL
    SELECT WATFORD_SL_ACCOUNTS.CUCODE, WATFORD_SL_ACCOUNTS.CUNAME, WATFORD_SL_ACCOUNTS.CUADDRESS
    FROM WATFORD_SL_ACCOUNTS;

    Field CUADDRESS is the field name, below is the example of how it looks

    WESTGATE DEPT STORE ◘ PARK ROAD ◘PETERBOROUGH◘CAMBRIDGESHIRE


    Evertime there is a ◘ i want to create a new field in the query. Eg Address1., Address2, Town etc......

    Cheers

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Hemish
    try the following sub
    you will need to supply the varaddress with the address to be split
    it will return upto 5 lines of address
    it was tested (and worked) to search for Chr$(13) & Chr$(10), the fact that you are only seeing one square in your system indicates that the host is adding a single on printable character rather than the two strTestChar is looking for. You will need to experiment to find which character to search for, or ask you Oracle DBA's what they are including.

    Code:
    Private Sub SplitAddress(varAddress As String, varAddr1 As Variant, varAddr2 As Variant, varAddr3 As Variant, varAddr4 As Variant, varAddr5 As Variant)
    varAddr1 = "" 'set up some defaults....
    varAddr2 = ""
    varAddr3 = ""
    varAddr4 = ""
    varAddr5 = ""
    Dim strTestChar As String
    strTestChar = Chr$(13) & Chr$(10)
    If Len(varAddress) = 0 Then Exit Sub 'its a null address so return null
    Dim intStartAt As Integer, intEndAt As Integer
    intEndAt = InStr(1, varAddress, strTestChar)
    If intEndAt > 0 Then 'we have found something.....
      varAddr1 = Left$(varAddress, intEndAt - 1)
      intStartAt = intEndAt + 2
      intEndAt = InStr(intStartAt, varAddress, Chr$(13) & Chr$(10))
      If intEndAt > 0 Then 'we have found something.....
        varAddr2 = Mid$(varAddress, intStartAt, intEndAt - intStartAt)
        intStartAt = intEndAt + 2
        intEndAt = InStr(intStartAt, varAddress, Chr$(13) & Chr$(10))
        If intEndAt > 0 Then 'we have found something.....
          varAddr3 = Mid$(varAddress, intStartAt, intEndAt - intStartAt)
          intStartAt = intEndAt + 2
          intEndAt = InStr(intStartAt, varAddress, Chr$(13) & Chr$(10))
          If intEndAt > 0 Then 'we have found something.....
            varAddr4 = Mid$(varAddress, intStartAt, intEndAt - intStartAt)
            intStartAt = intEndAt + 2
            intEndAt = InStr(intStartAt, varAddress, Chr$(13) & Chr$(10))
            If intEndAt > 0 Then 'we have found something.....
              varAddr5 = Mid$(varAddress, intStartAt, intEndAt - intStartAt)
            Else
              varAddr5 = Mid$(varAddress, intStartAt)
            End If
          Else
            varAddr4 = Mid$(varAddress, intStartAt)
          End If
        Else
          varAddr3 = Mid$(varAddress, intStartAt)
        End If
      Else
        varAddr2 = Mid$(varAddress, intStartAt)
      End If
    Else
      varAddr1 = Mid$(varAddress, intStartAt)
    End If
    End Sub

  7. #7
    Join Date
    Jan 2002
    Location
    London
    Posts
    73
    Hi Heldman,

    Thanks for your code i really do appreciate it. What di you mean by
    you will need to supply the varaddress with the address to be split

    varAddr1 = "" 'set up some defaults....
    varAddr2 = ""

    Do i need to do the date source in these field?

    Cheers

  8. #8
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    when you call the sub routine
    you will need to pass the value of the address to be split as varAddress
    the sub returns the lines as varaddr1,2,3,4 & 5

    you will need toidentify what character your oracle db in inserting to produce the square symbol. it will probably be chr$(10) or chr$(13) but not both, so you will need to identify that and place it in the strtestchar

    the call to the function could be

    SplitAddress(strWholeAddress, strLine1, strLine2, strLine3, strLine4, strLine5)
    where strWHoleAddress is the address to be split
    strLine1..5 are the lines of the address returned as part of the call.

  9. #9
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Here's a simpler split() function that I wrote in frustration with Access not having any array utilities:

    Code:
    Public Function Split(strIn As String, d As String) As Variant
    Dim arrReturn() As String
    Dim i As Integer, s As String, cnt As Integer
    
    'Originally written by teddy@jorun.net
    'Please keep these lines in tact, feel free to
    'add/change/elaborate.  I'd love to hear
    'from you too!
    
    'trim delimiter from right of string if present
    If Right(strIn, 1) = d Then
        strIn = Left(strIn, Len(strIn) - 1)
    End If
    
    'Alias strIn
    s = strIn
    
    'Determine number of elements to be returned based on occurrance of delimiter
    While InStr(s, d) > 0
        s = Right(s, Len(s) - InStr(s, d))
        cnt = cnt + 1
    Wend
    
    'Re-alias original strIn
    s = strIn & d
    
    'Set arrReturn to contain correct number of elements to be returned
    ReDim arrReturn(cnt) As String
    
    'Parse string and dump to array
    For i = 0 To UBound(arrReturn)
        arrReturn(i) = Left(s, InStr(s, d) - 1)
        s = Right(s, Len(s) - InStr(s, d))
    Next i
    
    'return array
    Split = arrReturn
    
    End Function
    Basically it returns a variant array containing one string per element. You could doctor that up a bit for your uses. Perhaps pulling one row at a time into the array, then posting the result as a new record in your table.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •