Removing from end with IF condition (1 Viewer)

mansied

Member
Local time
Today, 01:17
Joined
Oct 15, 2020
Messages
98
Hello
I need help,I have a table in which one field is populated with numbers , I want to truncate this filed to 5 digits( some numbers are less than 5 and some are more).
for this field, I want to put a condition if it is greater than 5 digits, delete the rest of the characters( I mean if it is less than 5 digits leave it ).

in the query, I have this

myfield: Left([myfield],Len([myfield])-2)

how can I add if the condition for it?
I am not sure to add in creteria ..... how can I add if the condition for it ??

Len([myfield])>5 ????

Thank you .........
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 00:17
Joined
Feb 28, 2001
Messages
22,780
If you are truncating from the left, you need no "IF" condition for this. The LEFT(string,size) function description makes it clear that if you have no more characters than the given truncation size, you return all of the input digits. The only catch would be if there were leading (left-side) blanks you didn't want to count. But you can trim blanks beforehand if that could be a problem.

Just use Left( Trim( myfield ), 5 ) and you will get the leftmost 5 characters. If you only had 4 characters, you get all of them. However, the way you asked the question makes me wonder if you are considering use a field constraint. Truncation cannot be done via a field constraint. Constraints only block input, they don't "diddle" with that input.

Depending on how you input this field, you would either truncate it at its source or run a query afterwards. Tell us how the data gets into that field and we can tell you the best way to assure your desired result.
 

Isaac

Lifelong Learner
Local time
Yesterday, 22:17
Joined
Mar 14, 2017
Messages
6,454
As The Doc Man mentioned all you need for this is left(column,5)

I'm curious what kind of data is this?
 

Users who are viewing this thread

Top Bottom