How to preserve possible leading zeros in a number?

[Deekay]

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.

Dim pass as String
Dim text1 as String, text2 as String
 
text1 = me.textbox1
text2 = me.textbox2
pass = generatecode(text1, text2)
debug.print pass
Me.regNo = pass
debug.print Me.regNo
 

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

[Lysander]

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.

[Deekay]

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

[ADezii]

Is the Return Value of the generatecode() Function a String, explicitly stated, as in:

Public Function generatecode(strArg1, strArg2) As String

[Lysander]

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

Dim strString As String
strString = "0001234"
Text4 = strString
 

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.

[Deekay]

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.

[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?

[Lysander]

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.

[ADezii]

@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.

[Deekay]

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.

[ADezii]

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:

Dim pass As String
 
'Simulating the Return Value from generatecode(Text1, Text2)
pass = "0012345"
 
Me![txtTest] = Format$(pass, String$(Len(pass), "0"))

[David Blackman]

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.

[ADezii]

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.

[NeoPa]

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.

[ADezii]

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:

Dim pass as String 
Dim text1 as String, text2 as String 
 
text1 = me.textbox1 
text2 = me.textbox2 
pass = generatecode(text1, text2) 

[NeoPa]

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.