UtterAccess.com
We have detected that you are using an unsupported web browser. We recommend you to upgrade your web browser to get the most from UtterAccess forums.
Why should and how can I upgrade?
X   Site Message
(Message will auto close in 2 seconds)

Welcome Guest ( Log In | Register )

 
Reply to this topicStart new topic
> Padding a text field with zeros to fill out rest of field    
 
   
tjmorgan97
post Jul 9 2008, 12:15 PM
Post #1

New Member
Posts: 4



I am trying to do an interface and need my text fields to be padded with "zeros" in the unused null spaces to fill out the rest of the field length. Not quite sure how to approach this, I thought there was a pad function in acces but there isn't. Any suggestions? Was thinking I could count the characters in the field but not sure how to replace the null values with the zeros.
Thanks
Go to the top of the page
 
+
Doug Steele
post Jul 9 2008, 12:23 PM
Post #2

UtterAccess VIP
Posts: 18,565
From: St. Catharines, ON (Canada)



Welcome to UtterAccess.
Are you talking about Nulls (which as Ascii character 0), or are you just talking about spaces?
Assuming you're talking about spaces, you can use code like the following to pad a 20-byte field at the end:
Left([MyField] & String("0", 20), 20)
For the following to pad it at the front:
Right(String("0", 20) & [MyField], 20)
Go to the top of the page
 
+
tjmorgan97
post Jul 9 2008, 01:02 PM
Post #3

New Member
Posts: 4



I tried that and it didn't seem to work. What I have is a 4 digit number in a 13 character field. The interface requires that I convert this to a text string and put in a leading 9 zeros in front of the 4 digit number to forces a complete 13 digit character string. My number is variable so it is not always 4 digits but could be any number from 1 to 13 digits, I just need to pad the left of the characters to make up the difference bettween the length of my characters and 13.
I am trying to do all this in a query that reads the raw data and then converts it to the correct format and writes it to a new table to be exported.
What does the "&" do in the formula you suggeted, I am not familar with that in access?
Oreally do appreciate your help. I used to be good at access years ago but just now am getting to use it again in my new job so I am psyched.
Thanks
Tim
Go to the top of the page
 
+
Doug Steele
post Jul 9 2008, 01:05 PM
Post #4

UtterAccess VIP
Posts: 18,565
From: St. Catharines, ON (Canada)



& is the concatenation character for use with text.
How did you try to use that expression?
Go to the top of the page
 
+
KeesB
post Jul 9 2008, 01:13 PM
Post #5

UtterAccess Enthusiast
Posts: 87
From: the Netherlands



Doug,
I've got no Access on this box so I can't check but have to ask . Thus String("0", 20) gives a string of 20 zero's?
Go to the top of the page
 
+
tjmorgan97
post Jul 9 2008, 01:15 PM
Post #6

New Member
Posts: 4



I tried to use it in an expression in the field line of the query
"Expr1: Left([myfield]![mytable] & String("0",13),13)"
oping to convert a numeric field of 1234 to a string of "0000000001234"
I hope that helps explain a bit more.
Thanks again
Tim
Go to the top of the page
 
+
Doug Steele
post Jul 9 2008, 01:27 PM
Post #7

UtterAccess VIP
Posts: 18,565
From: St. Catharines, ON (Canada)



Yes, String("0", 20) gives a siirng of 20 zeroes, String("X", 20) gives a string of 20 exes, and so on.
Go to the top of the page
 
+
Doug Steele
post Jul 9 2008, 01:32 PM
Post #8

UtterAccess VIP
Posts: 18,565
From: St. Catharines, ON (Canada)



Aargh! And after I told Kees what the function did.
misremembered the syntax.
"Expr1: Left([myfield]![mytable] & String(13, "0"),13)"
And Kees, assuming you're reading this, String(20, "0") gives a string of 20 zeroes, String(20, "X") gives a string of 20 exes, and so on.
Sorry for the confusion.
Go to the top of the page
 
+
KeesB
post Jul 9 2008, 01:34 PM
Post #9

UtterAccess Enthusiast
Posts: 87
From: the Netherlands



thumbup.gif Great, that's just what I needed.
Go to the top of the page
 
+
KeesB
post Jul 9 2008, 01:36 PM
Post #10

UtterAccess Enthusiast
Posts: 87
From: the Netherlands



NP Doug,

I've read it.

BTW shouldn't [mytable] be in front of [myfield]?

Edit: And for this case shouldn't the String go in front of the field?
Edited by: KeesB on Wed Jul 9 14:39:32 EDT 2008.
Go to the top of the page
 
+
Doug Steele
post Jul 9 2008, 01:40 PM
Post #11

UtterAccess VIP
Posts: 18,565
From: St. Catharines, ON (Canada)



Not my day, is it! dazed.gif
Yes, it should have been
"Expr1: Left([mytable]![myfield] & String(13, "0"),13)"
although the [mytable]! is often unnecessary.
Go to the top of the page
 
+
KeesB
post Jul 9 2008, 02:27 PM
Post #12

UtterAccess Enthusiast
Posts: 87
From: the Netherlands



Doug, here I go again sad.gif
ased on tjmorgan97 's example of 1234 doesn't this gives a result of 1234000000000
If that's the case I thinks that it should be
"Expr1: Right(String(13, "0") & [mytable]![myfield], 13)"
BTW. In his last post before this one he say's
<
if it's actually a numeric field will this still work?
Go to the top of the page
 
+
tjmorgan97
post Jul 9 2008, 02:38 PM
Post #13

New Member
Posts: 4



This works great, thanks so much.
And by the way I did use Right instead of Left but pretty much figured out all that by what you typed before. This did exactly what I wanted it to do.
For all you super smart Access people, is there a good reference guide out there you all use for you research or is it all self learned?
Thanks again for the help, you saved me a bunch of trial and error time.
Tim
Go to the top of the page
 
+

Thank you for your support! Reply to this topicStart new topic

Jump To Forum:
 



RSS Search  ·  Go to Top  ·  Lo-Fi Version Time is now: 31st October 2014 - 07:46 AM

Tag cloud: