Solved

Left Function

Posted on 2004-10-22
2,244 Views
Last Modified: 2007-12-19
Hi there,

I have this function, which I use in an update query. It works well...:

Left([ORIG_Cust_ID],Len([ORIG_CUST_ID])-3)

I'm sure you have worked out it deletes the last three characters in a string.

I need to adjust it so that it only carries out the update IF the last three characters are text, not numeric. Should this criteria be added within the criteria box in design mode?

How do I amend the code, how does Access differenciate between text and numbers within a string.

Cheers!

LoveToSpod
0
Question by:LoveToSpod
    15 Comments
     
    LVL 41

    Assisted Solution

    by:shanesuebsahakarn
    Add a new field into your query:

    LastThree: Not IsNumeric(Right$([ORIG_CUST_ID],3))

    with a criteria of:
    True

    That should do it.
    0
     
    LVL 39

    Assisted Solution

    by:stevbe
    IIF(IsNumeric(Right$([ORIG_Cust_ID], 3)=True, Left([ORIG_Cust_ID],Len([ORIG_CUST_ID])-3), [ORIG_Cust_ID])

    Steve
    0
     

    Author Comment

    by:LoveToSpod
    Steve,

    You code comes back as having the wrong number of arguments.

    Shane, I like the look of that code, but I am getting a Data Type Mismatch error. The query queries one field, and updates another. Both fields datatypes are TEXT.

    Any clues?

    LoveToSpod.

    0
     
    LVL 41

    Expert Comment

    by:shanesuebsahakarn
    Hmm, try:
    LastThree: Not IsNumeric(Right$(Nz([ORIG_CUST_ID],""),3))

    The code might fail if there are any null values in ORIG_CUST_ID, but this should fix it.
    0
     

    Author Comment

    by:LoveToSpod
    Shane,

    Thanks for the response:

    Now getting error:

    The expression you enterd has an invalid . (dot) or ! operator or invalid parenthesis.

    The cursor ends up where I have placed the @ symbol.

    LastThree: Not IsNumeric(@Right$(Nz([ORIG_CUST_ID],""),3))

    Cheers,

    LoveToSpod
    0
     

    Author Comment

    by:LoveToSpod
    Ignore that!!!

    I put the code in teh wrong place...

    DOH!!!

    Cheers
    0
     

    Author Comment

    by:LoveToSpod
    OK,

    It still deletes the last three characters even if numeric. Is this because [ORIG_CUST_ID] is a text field, and therefore the code looks at numbers as being text?

    LoveToSpod
    0
     
    LVL 41

    Expert Comment

    by:shanesuebsahakarn
    Did you put in the True criteria?
    0
     
    LVL 10

    Expert Comment

    by:calpurnia
    Please can you post the SQL for your query.
    0
     
    LVL 48

    Accepted Solution

    by:
    Well, Love, you could easily have done a little debugging yourself on Steve's solution:

    > You code comes back as having the wrong number of arguments.

    > IIF(IsNumeric(Right$([ORIG_Cust_ID], 3)=True, Left([ORIG_Cust_ID],Len([ORIG_CUST_ID])-3), [ORIG_Cust_ID])

    IIF(IsNumeric(Right$([ORIG_Cust_ID], 3)), Left([ORIG_Cust_ID], Len([ORIG_CUST_ID])-3), [ORIG_Cust_ID])

    /gustav
    0
     

    Author Comment

    by:LoveToSpod
    Ok, both suggested solutions still delete the last three characters whether numeric or text. As requested I'll post the sql.

    Here is the sql when using Steve's solution:
    UPDATE JobSummary SET JobSummary.CustIDWithoutSuffix = IIf(IsNumeric(Right$([ORIG_Cust_ID],3)),Left([ORIG_Cust_ID],Len([ORIG_CUST_ID])-3),[ORIG_Cust_ID]);

    Here is the sql when using Shane's solution:
    UPDATE JobSummary SET JobSummary.CustIDWithoutSuffix = Left([ORIG_Cust_ID],Len([ORIG_CUST_ID])-3)
    WHERE (((Not IsNumeric(Right$(Nz([ORIG_CUST_ID],""),3)))=True));

    If it's any use, the table is called "JobSummary", and fields [ORIG_CUST_ID] - Text ; and destination field [CustIDWithoutSuffix] - Text.

    Upping the points, thanks for your help...
    0
     
    LVL 48

    Expert Comment

    by:Gustav Brock
    The corrected version of Steve works fine here.
    Have in mind, please, the IDs like ABCD4E5 and HJK5D2 will be regarded as having a numeric trail as 4E5 and 5D2 are numeric.

    /gustav
    0
     

    Author Comment

    by:LoveToSpod
    OK,

    Get this!!!

    I made it work by making Steve's statement negative:

    Was:
    IIF(IsNumeric(Right$([ORIG_Cust_ID], 3)), Left([ORIG_Cust_ID], Len([ORIG_CUST_ID])-3), [ORIG_Cust_ID])

    Now:
    IIF(Not IsNumeric(Right$([ORIG_Cust_ID], 3)), Left([ORIG_Cust_ID], Len([ORIG_CUST_ID])-3), [ORIG_Cust_ID])

    This now works perfectly!!! - WHY?!?! - Before it was ignoring the text and deleting only the numerics?!!

    LoveToSpod

    0
     
    LVL 48

    Expert Comment

    by:Gustav Brock
    > I need to adjust it so that it only carries out the update IF the last three characters are text, not numeric ..

    Well, why didn't I just read what you wrote?
    You can also reverse the expressions:

    IIF(IsNumeric(Right$([ORIG_Cust_ID], 3)), [ORIG_Cust_ID], Left([ORIG_Cust_ID], Len([ORIG_CUST_ID])-3))

    /gustav
    0
     

    Author Comment

    by:LoveToSpod
    Shane,

    Unusual, but I couldn't make your code work!!

    Steve, thanks for providing the right direction,

    Gustav,

    Cheers for the debug, unfortunately I am still struggling away with code, so the debug you provided was not obvious to me.

    Everytime I have a question answered, I learn something new. It's amazing I write some excellent advanced applications, but still do not know some of the fundamentals. One of these is the syntax within access, but I am getting there! That's the beauty of EE huh!

    Thanks all,

    LoveToSpod
    0

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to improve team productivity

    Quip adds documents, spreadsheets, and tasklists to your Slack experience
    - Elevate ideas to Quip docs
    - Share Quip docs in Slack
    - Get notified of changes to your docs
    - Available on iOS/Android/Desktop/Web
    - Online/Offline

    In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
    I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
    Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
    With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

    933 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now