Page 1 of 2 12 LastLast
Results 1 to 15 of 30

Thread: =Left() (2003)

  1. #1
    2 Star Lounger
    Join Date
    Mar 2004
    Location
    Portland, Oregon
    Posts
    173
    Thanks
    0
    Thanked 0 Times in 0 Posts

    =Left() (2003)

    OK, I give up.... I know this is easy, but I keep getting a "type mis match"
    What I'm trying to do is take a "FullName" field and separate the first and last names.
    I thought the function I wanted was either =Right() or =Left(). But I cannot get it to work

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

    Re: =Left() (2003)

    Both Right and Left functions have required arguments. Please post the exact way you are trying to use them.
    Charlotte

  3. #3
    2 Star Lounger
    Join Date
    Mar 2004
    Location
    Portland, Oregon
    Posts
    173
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: =Left() (2003)

    I'm using a query, my field names are "fldRaRaContact" (where the data lives) and then I added "fldContactNameLast" and "fldContactNameFirst"
    My function is in the query and looks like: =Left([fldRaRacontact]instr([fldRaRaContact]," ")-"1") (Actually, I've tried so may variations I'm not even sure if this is the most current "try").

  4. #4
    2 Star Lounger
    Join Date
    Mar 2004
    Location
    Portland, Oregon
    Posts
    173
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: =Left() (2003)

    Still didn't get it right
    Left([fldRaRacontact],InStr([fldRaRacontact]," ")-1)

  5. #5
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: =Left() (2003)

    If you are using an equal sign in the query grid, you should not. But I think that would give you another kind of error. The error you are getting suggests that fldRaRacontact is not a text field. Is that possibly the case? It appears to me that your construction is correct.
    Wendell

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

    Re: =Left() (2003)

    One problem with that approach is that you have made no provision for a null field and that is never safe. The statement itself runs in a query for me if I replace the fieldname in square brackets with a field from a table in my database. I only see a type mismatch if I paste the statement into the immediate window and execute it. When are you seeing the type mismatch error?
    Charlotte

  7. #7
    2 Star Lounger
    Join Date
    Mar 2004
    Location
    Portland, Oregon
    Posts
    173
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: =Left() (2003)

    In this particular case I know for a fact there are no Null fields. And, I also know it is a text field because there is text in the field...hummm

  8. #8
    2 Star Lounger
    Join Date
    Mar 2004
    Location
    Portland, Oregon
    Posts
    173
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: =Left() (2003)

    Very interesting: I left this morning and closed Access and am just getting back to this... I did NOT get the error this time, BUT I'm not getting any result either.
    Here is my code:
    Left([fldRaRacontact],InStr([fldRaRacontact]," ")-1)
    This should be an update query, right?

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

    Re: =Left() (2003)

    Why don't you post the entire query SQL. There is nothing wrong with the statement itself, so there must be something wrong with the way you're using it. Without seeing the SQL, we just guessing at best.
    Charlotte

  10. #10
    2 Star Lounger
    Join Date
    Mar 2004
    Location
    Portland, Oregon
    Posts
    173
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: =Left() (2003)

    OK, how can I post just the query? The database is too big...

  11. #11
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: =Left() (2003)

    While looking at the query in Design view go to View...SQL and the query will be shown as a piece of text. Post that piece of text.
    Regards
    John



  12. #12
    2 Star Lounger
    Join Date
    Mar 2004
    Location
    Portland, Oregon
    Posts
    173
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: =Left() (2003)

    OK, here it is:
    UPDATE tblRaRAContacts SET tblRaRAContacts.fldRaRAcontact = [fldRaRaNamefirst]
    WHERE (((tblRaRAContacts.fldRaRAcontact)=Right([fldRaRacontact],InStr([fldRaRacontact]," ")-1)));

  13. #13
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: =Left() (2003)

    If fldRaRacontact don't contain a space, the expresion :
    Right([fldRaRacontact],InStr([fldRaRacontact]," ")-1)))
    will give an error.

    What are you trying to do ?
    Francois

  14. #14
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: =Left() (2003)

    Don't you want something like this

    UPDATE tblRaRAContacts SET tblRaRAContacts.[fldRaRaNamefirst = Right([fldRaRacontact],InStr([fldRaRacontact]," ")-1);

    or

    UPDATE tblRaRAContacts SET tblRaRAContacts.[fldRaRaNameLast= Right([fldRaRacontact],InStr([fldRaRacontact]," ")-1);

    Depends on the order of the names in the current data.

    The sql you posted says :make RaRaContact equal to the firstname when RaRacontact is equal to the right hand end of RaRaContact.
    Regards
    John



  15. #15
    2 Star Lounger
    Join Date
    Mar 2004
    Location
    Portland, Oregon
    Posts
    173
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: =Left() (2003)

    I have a "full name field" which contains the first and last name (with a space between the two). I'm trying to separate them into First Name field and Last name field.

Page 1 of 2 12 LastLast

Posting Permissions

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