Results 1 to 5 of 5
  1. #1
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Atlanta
    Posts
    568
    Thanks
    7
    Thanked 1 Time in 1 Post

    Need help with a query (97)

    I use Access to store a lot of data but I only occasionally need to make any changes so I'm fairly green at this. I have a field called Categories with entries like this:

    Chapter 1
    Section 7
    Technical Note 2

    and I need to transfer the text part to a field called Extension and the number part to a field called Chapter. All three fields are text fields. Any help would be greatly appreciated, I don't want to have to manually update 1800 records.

    Ronny
    Ronny Richardson

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Need help with a query (97)

    Activate the Modules tab of the database window.
    Click New.
    Copy the following code into the module:

    <img src=/w3timages/blueline.gif width=33% height=2>

    Function FirstPart(varText)
    Dim lngPos As Long
    If IsNull(varText) Then
    FirstPart = Null
    ElseIf InStr(varText, " ") = 0 Then
    FirstPart = varText
    Else
    lngPos = Len(varText)
    Do While Mid(varText, lngPos, 1) <> " "
    lngPos = lngPos - 1
    Loop
    FirstPart = Left(varText, lngPos - 1)
    End If
    End Function

    Function LastPart(varText)
    Dim lngPos As Long
    If IsNull(varText) Then
    LastPart = Null
    ElseIf InStr(varText, " ") = 0 Then
    LastPart = Null
    Else
    lngPos = Len(varText)
    Do While Mid(varText, lngPos, 1) <> " "
    lngPos = lngPos - 1
    Loop
    LastPart = Mid(varText, lngPos + 1)
    End If
    End Function

    <img src=/w3timages/blueline.gif width=33% height=2>

    Close this module and give it a name, for example basFunctions.
    Switch to the Queries tab.
    Click New.
    Select the table, click Design View, then OK. (Might be slightly different in '97, don't remember exactly)
    Add the Extension and Chapter field to the query grid.
    Turn the query into an Update query (Query | Update Query)
    In the Change to line, enter FirstPart([Categories]) under Extension and LastPart([Categories]) under Chapter.
    Click the Run button or select Query | Run to execute the query.
    Attached Images Attached Images
    • File Type: png x.png (1.9 KB, 0 views)

  3. #3
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Atlanta
    Posts
    568
    Thanks
    7
    Thanked 1 Time in 1 Post

    Re: Need help with a query (97)

    Thank you, thank you.

    This worked almost without a hitch. When the lounge emailed your post to me, it changed

    Do While Mid(varText, lngPos, 1) <> " "

    to the following:

    Do While Mid(varText, lngPos, 1) & lt;> " " (without the space but longe changes that to HTML character here)

    and that gave me a compile error. Once I noticed the problem, it did not hit me that < was HTML so I just deleted it as extra characters so I ended up with > rather than <>. That seems to have worked fine anyway.

    Thanks again, you saved me from a lot of work.

    Ronny
    Ronny Richardson

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Need help with a query (97)

    This happens from time to time; I have never been able to find out under which circumstances a bracket is converted to HTML code.

    In this case, > by itself works fine, since a space is less than any other character in text comparisons.

  5. #5
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Need help with a query (97)

    The interesting thing is that if you copy the code from the message on the site, you'll find that it doesn't change the less than sign to an HTML code. It seems to be something in the engine that creates emails from the posts that works that particular odd magic. <img src=/S/crazy.gif border=0 alt=crazy width=15 height=15>
    Charlotte

Posting Permissions

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