Results 1 to 6 of 6
  1. #1
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Virginia, USA
    Posts
    1,560
    Thanks
    37
    Thanked 1 Time in 1 Post

    Expression doesn't work (2003)

    In my table, tblStationDirectory, I have a field named RSID. It contains up to four characters that represent four levels of our organization, from highest to lowest. In a record that needs all four levels, it might look like this: 1N5G. I've built a query to parse the four elements into separate fields: BdeCode, BnCode, CoCode, StnCode. I'm not good with expressions, so I turned to Microsoft.com for help. There I found in article #286238 the following expression:
    Zip Code
    Original Entry in [ZipCode]: "98052-6399"
    Returned by Expression: 98052
    Expression: Expr: Left([ZipCode],5)

    I thought, what better source for a usable expression, and that expression appears to be just what I'm looking for. I pasted it into my query (BdeCode field) and modified it as follows:
    Expr: Left([BdeCode],1)

    I figured this expression would look at a record that reads 1N5G and put "1" in the BdeCode field. Instead, I get an error message:
    "The expression you entered has an invalid dot (.) or ! operator or invalid parentheses."

    All I did was change "ZipCode" to "BdeCode" and 5 to 1. So what's wrong?

  2. #2
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Expression doesn't work (2003)

    Where in your query did you past it? Is sounds as if your query is selecting your BDECode field in the field line and then you are also putting the left([bdecode],1) in the criteria field as well. This causes a conflict.

    Try your expression in the field line with nothing in the criteria. I.e., in the field line type Expr: Left([BdeCode],1)
    Regards,

    Gary
    (It's been a while!)

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

    Re: Expression doesn't work (2003)

    You state that RSID is the four-character field, so I'd expect:
    <code>
    BdeCode: Left([RSID],1)

    BnCode: Mid([RSID],2,1)

    CoCode: Mid([RSID],3,1)

    StnCode: Right([RSID],1)</code>

  4. #4
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Virginia, USA
    Posts
    1,560
    Thanks
    37
    Thanked 1 Time in 1 Post

    Re: Expression doesn't work (2003)

    Hans, I thought at first that the expressions you provided were exactly on point. I've discovered a little problem, and it stems from my own failure to paint a complete picture from the start. I said earlier that RSID is a four-character field. That's true...but there's more.

    The RSID for the top organizational level (BdeCode) is simply one character

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

    Re: Expression doesn't work (2003)

    Use
    <code>
    StnCode: Mid([RSID],4,1)</code>

  6. #6
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Virginia, USA
    Posts
    1,560
    Thanks
    37
    Thanked 1 Time in 1 Post

    Re: Expression doesn't work (2003)

    Perfect! Thank you, Hans! <img src=/S/salute.gif border=0 alt=salute width=15 height=20>

Posting Permissions

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