469,366 Members | 2,313 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,366 developers. It's quick & easy.

How to preserve possible leading zeros in a number?

21
Is there a way to preserve possible leading zeros in a number even when it is passed as text?
I have this code on a form which runs off a command button taking two values from user input, passing them to a function and is placing the returned value as a string back in a thrid textbox ('regNo').
The function 'generatecode' is correctly returning a string of digits which may be between 6 to 11 digits long and might contain between 0 and 3 leading zeros. The problem occurs at line 8 when I try to place this string in the text box.
Expand|Select|Wrap|Line Numbers
  1. Dim pass as String
  2. Dim text1 as String, text2 as String
  3.  
  4. text1 = me.textbox1
  5. text2 = me.textbox2
  6. pass = generatecode(text1, text2)
  7. debug.print pass
  8. Me.regNo = pass
  9. debug.print Me.regNo
  10.  
for example the first debug.print is returning 0045678, whilst the second is returning 45678.
It all works fine if there are no leading zeros in the number (but returned as a string) that the function calculates.

Any help gratefully received.
Deekay
Feb 5 '11 #1

✓ answered by Lysander

If push comes to shove and you can't fix it anyother way, create a dummy table and bind RegNo to a text field in that table. btw, what version of Access are you using?

15 9437
Lysander
344 Expert 100+
What is regNo? You say it is a textbox on the form but what type of field is it bound to, a text field or a numeric field. If a text field, it should work fine.

If regNo is unbound, try forcing it to a string using

Me.reNo=chr(34) & pass & chr(34)

and see if that works.
Feb 5 '11 #2
Deekay
21
Hello Lysander
Good try but this is generating an error - and yes the textbox is unbound. It's meant to generate a code for sending as part of an e-mail.
Deekay
Feb 5 '11 #3
ADezii
8,800 Expert 8TB
Is the Return Value of the generatecode() Function a String, explicitly stated, as in:
Expand|Select|Wrap|Line Numbers
  1. Public Function generatecode(strArg1, strArg2) As String
Feb 5 '11 #4
Lysander
344 Expert 100+
Ok, using Access 2003, I have just created a simple form, added an unbound textbox to it and a command button.

The command button has the following code
Expand|Select|Wrap|Line Numbers
  1. Dim strString As String
  2. strString = "0001234"
  3. Text4 = strString
  4.  
And when i hit the command button Text4 displays 0001234

Have you put any formating or anything on the textbox RegNo, because I used a bog-standard default textbox and it worked.
Feb 5 '11 #5
Deekay
21
I haven't used any formatting on the text box - somehow it assumes that the value it is being given is a number and trimming any leading zeros. (Incidentally I also tried temporarily bidning it to a text field and it then behaves properly, so the problem appears to be happening when the textbox is unbound.
I will also try your code to see what happens. It it words I can then compare all of the properties.
Feb 5 '11 #6
Lysander
344 Expert 100+
If push comes to shove and you can't fix it anyother way, create a dummy table and bind RegNo to a text field in that table. btw, what version of Access are you using?
Feb 5 '11 #7
Lysander
344 Expert 100+
Another idea. pass works, so, you could set an interger to the value of len(pass) and then use something like

RegNo=format(pass,"0000000") where the number of zeros are the value of len(pass)

Bit of a cludge but it should work.

Still not sure why you are loseing leading zeros in the first place. I am running 2 commercial databases where the primary keys are all 001, 002, 003 etc and never lose the leading zeros.
Feb 5 '11 #8
ADezii
8,800 Expert 8TB
@Deekay - honestly, I cannot get this NOT to work. Any chance you can Upload the Database, or a portion of it, for us to see? In this manner we can see first hand what the problem is.
Feb 5 '11 #9
Deekay
21
Thabnks for the various suggestions. As you say there is a workaroubnd and I have decided to adopt a similar solution. I have discovered there is no problem if the value is passed to a bound textbox - the leading zeros only get stripped when passing to an unbound textbox. Also I can't use the len(...) idea since there is not way of telling what length (i.e. number of digits) is going to be returned. However the idea of binding it to a dummy filed appears to work, so I will use this.
It does seem stange thought, that VBA doesn't have a function that forces this string to be passed excatly as is. I tried CStr(... and Str(... and neither solved the problem.
As I have a solution i will close this thread now. Thanks for your time.
Feb 5 '11 #10
ADezii
8,800 Expert 8TB
Not sure it you are still initerested or not, but you can try explicitly Formatting the Return Value of the Function, then pass it to a Text Box, as in:
Expand|Select|Wrap|Line Numbers
  1. Dim pass As String
  2.  
  3. 'Simulating the Return Value from generatecode(Text1, Text2)
  4. pass = "0012345"
  5.  
  6. Me![txtTest] = Format$(pass, String$(Len(pass), "0"))
Feb 5 '11 #11
The statement

"regNo = format(CLng(pass),"0000000")"

will change the string value to a long integer value, with the leading 0's.

But in that "regNo" is already showing the correct number, simply setting the field's "Format" property to "0000000" will restore the leading 0's.
Feb 5 '11 #12
ADezii
8,800 Expert 8TB
The function 'generatecode' is correctly returning a string of digits which may be between 6 to 11 digits long and might contain between 0 and 3 leading zeros.
@David Blackman - That will not work since the Return Value from generatecode() can be anywhere from 6 to 11 Digits, that's why the Length of the Return Value needs to be evaluated and the proper Format String generated.
Feb 5 '11 #13
NeoPa
32,185 Expert Mod 16PB
Deekay, I think an important point in all of this is that while String variables will always hold data as strings, TextBox controls on a form are more like Variant variables in that they determine the type of the sata on the fly, depending on their current contents. If you want their values to be specifically strings, than I suggest you pass them into string variables before passing those as parameters to your generatecode() procedure.
Feb 6 '11 #14
ADezii
8,800 Expert 8TB
If you want their values to be specifically strings, than I suggest you pass them into string variables before passing those as parameters to your generatecode() procedure.
Isn't this exactly what happened? The variable were Declared as Strings, set to the Values of Text1 and Text2, then placed as Arguments to the generatecode() Function. Even the Return Value is Declared as String.
From Post #1:
Expand|Select|Wrap|Line Numbers
  1. Dim pass as String 
  2. Dim text1 as String, text2 as String 
  3.  
  4. text1 = me.textbox1 
  5. text2 = me.textbox2 
  6. pass = generatecode(text1, text2) 
Feb 6 '11 #15
NeoPa
32,185 Expert Mod 16PB
Indeed, but the problem isn't noticed in the value of pass, which is displayed in line #7, but only in Me.regNo, which is displayed in line #9 and which is a control.
Feb 7 '11 #16

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

2 posts views Thread by r.magdeburg | last post: by
5 posts views Thread by samik_tanik | last post: by
1 post views Thread by mmmgood1 | last post: by
6 posts views Thread by Clint Stowers | last post: by
5 posts views Thread by OneDay | last post: by
6 posts views Thread by Rich Raffenetti | last post: by
6 posts views Thread by JimmyKoolPantz | last post: by
reply views Thread by zhoujie | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.