Left pad field with zeros

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.

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

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.                              

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

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.

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.

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)

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.

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.
 

appreciate your taking a second glance. You're right, I had glossed over the digit length part.

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

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

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!

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?

brewdog

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

tailored

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. https://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. https://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

Per recommendation, force-accepted by
Netminder
CS Moderator