Left pad field with zeros

I have two fields I need to send out to a text file to be picked up by the Mainframe.  The first needs to be a five byte field containing the orders quantity (integer).  The Access 2.0 table currently stores this field as an integer and it is usualy the value 1.  It needs to look like this 00001 in the text field.  
The second field is a currency field that needs to look like 000001125. Nine bytes in the text file with no decimal representing the $11.25 value in the table.  
What function(s) do I use to these fields ready to export to the text field. It doesn't appear that the export spec does this for me, so, I feel that I must export text fields already formatted.

Thanks for the assistance.
tailoredAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

kaldrichCommented:
Someone else may have a better answer than this, but if there are a limited number of zeros, you can do one of two things.  First, if you know how many zeros there are supposed to be (for example, if in your first case you never get a number higher than 9 or any decimals), you can use Format (and I think it would be '00000#') in your first case; you'd multiply your second number by 100 and then apply the format to the result.

The second thing you can do if you do not know the length is to apply the format with a series of nested Ifs; e.g., IF(Number < 10, Format(number,'00000#),IF(Number < 100, Format(number,'0000##'),   . . . etc.

Hope this is of use.
0
brewdogCommented:
Here's code I developed for this exact type of purpose:

Public Sub AddZeroes(strTable as string, strField as string, intLength as integer)

   dim rs as recordset
   set rs = currentdb.openrecordset(strTable)
   rs.movefirst
   do until rs.eof
      do until len(rs(strfield)) = intLength
         rs(strField) = "0" & rs(strField)
      loop
      rs.movenext
   loop  

End Sub

To use this for your two occasions, you'd do this from behind a command button on a form or wherever:

Call AddZeroes("YourTable", "Quantity", 5)
Call AddZeroes("YourTable", "YourCurrencyField", 9)

Hope this helps . . .

brewdog
0
blakeh1Commented:
yes like Kaldrich said

create a query and use the following expressions for the fields using the amount of 0 for the total number of characters for the field, below assumes a 5 char field ie 1 becomes 00001 and 85.25 becomes 08525

use this for the number field
 Format([MyIntegerFieldname],"00000")

use this for the currency
 
Format([Moneyfieldname]* 100,"00000")
 export the query
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

brewdogCommented:
Oops, I forgot one piece. This line:

   rs(strField) = "0" & rs(strField)

should be replaced with:

   rs.edit
   rs(strField) = "0" & rs(strField)
   rs.update

sorry about that.                              
0
TrygveCommented:
It doesn't have to be that complicated. If you base your export on a query you can use the following format statements to get the results you need.

1) format([FieldName],"00000")

format(1,"00000") equals 00001

2) format([FieldName],100,"000000000")

format((11.25)*100,"000000000") equals 000001125
0
brewdogCommented:
Trygve: hm. That's what kaldrich and blakeh1 both said. :o) I like the generic-ness of the code; import it into any database, call it once, and boom! I'm done.

To be honest, though, I'm surprised that Access exports data correctly the way you guys suggest it. (I believe you, it just surprises me.) Since format only refers to appearance and not data, and the export process involves data, I would think Access would ignore it. Guess not.
0
TrygveCommented:
brewdog: If it is the same, I don't know: It looks a bit the same, but with all due respect, I don't think the proposed version would work 100%.

My suggestion goes not on the format of the field in the table, which you are correct about only affects the appearance and will most likely have no effect if the tables is exported directly.

My statements are to be used in the query that the export is based on. If you base the export on a query it will export the result of the query and not the underlaying data.
0
PShieldsCommented:
If the format answer doesnt work try this.  I did this is a query so price is the fieldname that is currency.
newval is a newly created field.

newval:Right(1000000000+([price]*100),9)
0
brewdogCommented:
Trygve: you're right that your comment is different from kaldrich's in that way, but blakeh1 says "export the query" at the end of his comment . . .

clever, PShields. I still like my generic for portability, but I like your idea for a one-time shot.
0
TrygveCommented:
brewdog: Format([Moneyfieldname]* 100,"00000") which would return a 5 character text instead of 9. But I agree the proposed solution is quite equal when I look closer at it.
 
0
brewdogCommented:
appreciate your taking a second glance. You're right, I had glossed over the digit length part.
0
blakeh1Commented:
Yes i was saying to use as he format function in a calculated field, not in the format property, as far as the number of zeros, i was just giving 5 as an example saying to use the number of 0 for the amount of chars the field should be.
on a side note however, the format property will work as well if when you export the query you check off the option to Save as Foramtted. This will actually write the data as displayed without having to use the format function in a calculated field
0
tailoredAuthor Commented:
Blakeh1,

I used your suggestion and formatted the fields in a make table query and then exported the table out to text.
It worked nicely.

Please submit proposed answer.

Tailored
0
brewdogCommented:
tailored: at the upper right-hand corner of each of our comments should be a little "Accept Comment As Answer" label you can click to award blakeh1 the points.

Glad you've got a solution that works!
0
TrygveCommented:
Today I read in some article that it is possible to use Format(123,"!00000") to get 12300, but I was not able to reproduce this in Access. Anyone know if this is VB syntax and/or possible in VBA?
0
tailoredAuthor Commented:
brewdog

Thanks for leading me through this.  I'm still such a novice at this stuff.

tailored
0
bruintjeCommented:
Hi tailored,

Any update on this question?

in an effort to clean up old open questions

your options are:

1. Accept a Comment As Answer (use the button next to the Expert's name).
2. Close the question if the information was not useful to you, but may help others. You must tell the
participants why you wish to do this, and allow for Expert response.  This choice will include a refund
to you, and will move this question to our PAQ (Previously Asked Question) database.  If you found information
outside this question thread, please add it.
3. Ask Community Support to help split points between participating experts, or just comment here with
details and we'll respond with the process.
4. Delete the question (if it has no potential value for others).
 --> Post comments for expert of your intention to delete and why
 --> You cannot delete a question with comments, special handling by a Moderator is required.

For special handling needs, please post a zero point question in the link below, include the question

QID/link. http://www.experts-exchange.com/jsp/qList.jsp?ta=commspt

Please click this Help Desk link for Member Guidelines, Member Agreement and the Question/Answer process:
Click you Member Profile to view your question history and keep them all current with updates as the
collaboration effort continues. http://www.experts-exchange.com/jsp/cmtyHelpDesk.jsp

------------>  EXPERTS:    Please leave any comments regarding this question here on closing recommendations
if this item remains inactive another seven (7) days.

:O)Bruintje
0
NetminderCommented:
Per recommendation, force-accepted by
Netminder
CS Moderator
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.