Left Function

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
LoveToSpodAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

shanesuebsahakarnCommented:
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
stevbeCommented:
IIF(IsNumeric(Right$([ORIG_Cust_ID], 3)=True, Left([ORIG_Cust_ID],Len([ORIG_CUST_ID])-3), [ORIG_Cust_ID])

Steve
0
LoveToSpodAuthor Commented:
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

shanesuebsahakarnCommented:
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
LoveToSpodAuthor Commented:
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
LoveToSpodAuthor Commented:
Ignore that!!!

I put the code in teh wrong place...

DOH!!!

Cheers
0
LoveToSpodAuthor Commented:
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
shanesuebsahakarnCommented:
Did you put in the True criteria?
0
calpurniaCommented:
Please can you post the SQL for your query.
0
Gustav BrockCIOCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
LoveToSpodAuthor Commented:
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
Gustav BrockCIOCommented:
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
LoveToSpodAuthor Commented:
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
Gustav BrockCIOCommented:
> 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
LoveToSpodAuthor Commented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.