Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Left Function

Posted on 2004-10-22
15
Medium Priority
?
2,250 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
Comment
Question by:LoveToSpod
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 3
  • 3
  • +2
15 Comments
 
LVL 41

Assisted Solution

by:shanesuebsahakarn
shanesuebsahakarn earned 300 total points
ID: 12379559
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
stevbe earned 500 total points
ID: 12379858
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
ID: 12379934
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
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 12379955
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
ID: 12380019
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
ID: 12380041
Ignore that!!!

I put the code in teh wrong place...

DOH!!!

Cheers
0
 

Author Comment

by:LoveToSpod
ID: 12380125
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
ID: 12380584
Did you put in the True criteria?
0
 
LVL 10

Expert Comment

by:calpurnia
ID: 12380621
Please can you post the SQL for your query.
0
 
LVL 52

Accepted Solution

by:
Gustav Brock earned 600 total points
ID: 12382405
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
ID: 12398502
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 52

Expert Comment

by:Gustav Brock
ID: 12398619
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
ID: 12398678
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 52

Expert Comment

by:Gustav Brock
ID: 12398709
> 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
ID: 12398763
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

Featured Post

Enroll in October's Free Course of the Month

Do you work with and analyze data? Enroll in October's Course of the Month for 7+ hours of SQL training, allowing you to quickly and efficiently store or retrieve data. It's free for Premium Members, Team Accounts, and Qualified Experts!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

610 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