Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2004
    Posts
    10

    Unanswered: Query Left Chars in Field

    Here is some sample data:

    02 Seattle-WA List A
    02 Seattle-WA List B
    03 LosAngeles-CA
    etc..

    So what I want to do is grab only the characters Left of the "-" + 2.. so that the results will be:

    02 Seattle-WA
    02 Seattle-WA
    03 LosAngeles-CA

    I dont know what the code should be in the field property of the query... anyone have any ideas?

    Thanks.

  2. #2
    Join Date
    Jan 2004
    Posts
    492

    Re: Query Left Chars in Field

    Originally posted by dataMonkey
    Here is some sample data:

    02 Seattle-WA List A
    02 Seattle-WA List B
    03 LosAngeles-CA
    etc..

    So what I want to do is grab only the characters Left of the "-" + 2.. so that the results will be:

    02 Seattle-WA
    02 Seattle-WA
    03 LosAngeles-CA

    I dont know what the code should be in the field property of the query... anyone have any ideas?

    Thanks.

    Use this query and change col to your column name, and your_table to the table name.
    Code:
    SELECT substr(col, 1, instr(col, '-')+2)
    from your_table

  3. #3
    Join Date
    Feb 2004
    Posts
    142
    =left(FieldName,Length)
    for length do instr(1,FieldName,"-")+2

    Result
    =Left(FieldName,instr(1,FieldName,"-")+2)
    KC

  4. #4
    Join Date
    Mar 2004
    Posts
    10
    Thanks! worked perfectly.

  5. #5
    Join Date
    Mar 2004
    Location
    Nashville, IN
    Posts
    5
    If the field were being displayed in a form, another way would be to control the display with an oncurrent event at the form level:

    Private Sub Form_Current()
    Dim count As Integer
    count = InStr(Me![location], "-") + 2
    Me![location] = Left(Me![location], count)
    End Sub

Posting Permissions

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