Add 0 before if <3 digits plus show only first 3 digits

[maqsoom]

Hi ,



I have a field where I have max. 4 digits and I only want to see first 3 digits plus if the digits are <3 then I want to add a '0' in front it.
Can anyone please help how to build the query to use to get these results in one go?

Thanks,
Maqsoom

[ranman256]

In the query, right click on the field in the grid
Format
set to 0000

[Micron]

in 3 out of 1234, in your mind, which are the "first 3"; 123 or 234?
Assuming your data is numeric and not text, with 0000, 1234 should display 1234 because there are four placeholders and four digits in the data. Why you'd want to do this is a curiosity for sure, but to trim one of the digits, I think you'll have to either divide by 10 (which will invoke rounding - probably not good) or use an extraction function like Left, Mid or Right.

Exp1: Format(Left([Field1],3),"0000")

[JoeM]

Perhaps this is what you are looking for:

Exp1: Left(Format([Field1],"0000"),3)

[Micron]

Exp1: Left(Format([Field1],"0000"),3)

I think the trim has to happen before the format. Yours on the left, mine on the right.

Exp1	Exp2
345	0345
123	0123
234	0234

[JoeM]

I think the trim has to happen before the format. Yours on the left, mine on the right.

I think we have different interpretations of the original question.

Original question:

I have a field where I have max. 4 digits and I only want to see first 3 digits plus if the digits are <3 then I want to add a '0' in front it.

The way I read this is that the entries are supposed to be 4 characters long, but some are only 3 (I am presuming that a leading zero has been dropped).
And it sounds like they only want to return/see the first 3 characters (including any leading zeroes).

So, if that is correct, the following entries should return the following values:
123 should return 012 (because they actually want the 3 left-most characters of "0123").
1234 should return 123

That is exactly what mine does.

You obviously interpreted the question differently than me. Due to the ambiguity of the question, it really isn't clear which one of us has the correct interpretation.

[Micron]

Yes, I'm assuming if 1234 show 0123. If 123, show 0123. You probably have it right.

[JoeM]

You probably have it right.

Can't be too sure. It could be interpreted either way, from what I see.
I guess only the user really knows for sure!