LEN question

[Bob]

Hi,

I've been trying to figure out a query for the following a condition
and been having difficulty.

00005
00006
007
00008
00009

I have large lists where "00" is missing from the beginning of the
number. I believe it's an update query with Len=3 but I haven't gotten
to far past that.

Thanks for any help you can provide.

Bob

 

[Jeff Boyce]

Bob

Numbers can be displayed with leading zeros, but these are meaningless, and
aren't stored. That is, all the following represent the same value:
5
05
005
0005
00005

Are you planning to do calculations/math with your "numbers", or are they
actually "codes", like zip codes, and should be text rather than numeric?

If you create a query and add a field of Len([YourNumber]), and add a
criterion under that new field of "<5" (without the quotes, and using your
example), you will see all the "numbers" of length less than 5.

 

[Bonnie]

Hey Bob! I posted a variation of your question on August
13 called "Need to fill in leading zeros" and got the
PERFECT reply from Steve Clark. He said:

Using an update query, you can concatenate zeros to the
existing string, then extract nine characters from the
right.

Right$("000000000" & ShortSSN,9)

So, if ShortSSN = "1234", it will result in "000001234".

This is what I did with that info: I have a query with one
grid field named FixSSN: Right$("000000000" & [F2],9) and
another grid field with Len[F2] and in the criteria line
<9. This identifies which ones are less than the desired
total characters. Next, I have an update query that
updates my field [F2] to equal [FixSSN]. I'm sure the
experts can do it in one query but my stack works fine so
I only change the ones that need it.

HTH-Bonnie

 

[Bob]

Hey Bob! I posted a variation of your question on August
13 called "Need to fill in leading zeros" and got the
PERFECT reply from Steve Clark. He said:

Using an update query, you can concatenate zeros to the
existing string, then extract nine characters from the
right.

Right$("000000000" & ShortSSN,9)

So, if ShortSSN = "1234", it will result in "000001234".

This is what I did with that info: I have a query with one
grid field named FixSSN: Right$("000000000" & [F2],9) and
another grid field with Len[F2] and in the criteria line
<9. This identifies which ones are less than the desired
total characters. Next, I have an update query that
updates my field [F2] to equal [FixSSN]. I'm sure the
experts can do it in one query but my stack works fine so
I only change the ones that need it.

HTH-Bonnie

Thanks, Bonnie.

 

[Douglas J. Steele]

Hey Bob! I posted a variation of your question on August
13 called "Need to fill in leading zeros" and got the
PERFECT reply from Steve Clark. He said:

Using an update query, you can concatenate zeros to the
existing string, then extract nine characters from the
right.

Right$("000000000" & ShortSSN,9)

So, if ShortSSN = "1234", it will result in "000001234".

This is what I did with that info: I have a query with one
grid field named FixSSN: Right$("000000000" & [F2],9) and
another grid field with Len[F2] and in the criteria line
<9. This identifies which ones are less than the desired
total characters. Next, I have an update query that
updates my field [F2] to equal [FixSSN]. I'm sure the
experts can do it in one query but my stack works fine so
I only change the ones that need it.

Why bother worrying about whether or not they're incorrect? That formula
will leave the correct ones as is.

 

[Bonnie]

So we can notify the client and they can hopefully correct
the file themselves. This whole process occurs when a
client file is not loaded in the proper format to
automatically go into our system. So my purpose is
to 'fix' files to then load into our system and help our
clients not need my intervention.

Not being a programmer, I'm getting where I need to be the
best I can and learning lots from you guys in the process.

Thanks again for your help!

-----Original Message-----

Hey Bob! I posted a variation of your question on August
13 called "Need to fill in leading zeros" and got the
PERFECT reply from Steve Clark. He said:

Using an update query, you can concatenate zeros to the
existing string, then extract nine characters from the
right.

Right$("000000000" & ShortSSN,9)

So, if ShortSSN = "1234", it will result

in "000001234".


This is what I did with that info: I have a query with one
grid field named FixSSN: Right$("000000000" & [F2],9) and
another grid field with Len[F2] and in the criteria line
<9. This identifies which ones are less than the desired
total characters. Next, I have an update query that
updates my field [F2] to equal [FixSSN]. I'm sure the
experts can do it in one query but my stack works fine so
I only change the ones that need it.

Why bother worrying about whether or not they're incorrect? That formula
will leave the correct ones as is.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)





.