If less than 5 digits in VBA (1 Viewer)

stu_c

Registered User.
Local time
Today, 19:57
Joined
Sep 20, 2007
Messages
366
hi all
I want a button that when clicked if the field is less than 5 digits to automatically put in the leading Zeros how do you do this?

ButtonName: BTNClickSearch
Field: TxtRefNumber

reason for this is if its more that 5 than a different ref number is searched :)
 

Gasman

Enthusiastic Amateur
Local time
Today, 19:57
Joined
Sep 21, 2011
Messages
10,853
Use the Format() function, though that would change it into a string, which is what you would need for leading zeroes, but be aware.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 14:57
Joined
Feb 19, 2002
Messages
36,871
Do not rely on the user to press a button, you can do this automatically in the control's AfterUpdate event.
Code:
If Len(Me.TxtRefNumber) < 5 Then
    Me.TxtRefNumber= Format(Me.TxtRefNumber, "00000")
End If
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 02:57
Joined
May 7, 2009
Messages
16,810
to avoid "invalid use of Null":

If Len(Me.txtRefNumber & "") < 5 Then
...
...


Or just simply Format it without checking for its length.

Me!txtRefNumber = Format(Me!txtRefNumber, "00000")
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 14:57
Joined
Feb 19, 2002
Messages
36,871
The Format() above does not error if the control is null.

But the If isn't necessary. It is there for human understanding.
 

stu_c

Registered User.
Local time
Today, 19:57
Joined
Sep 20, 2007
Messages
366
to avoid "invalid use of Null":

If Len(Me.txtRefNumber & "") < 5 Then
...
...


Or just simply Format it without checking for its length.

Me!txtRefNumber = Format(Me!txtRefNumber, "00000")
thank you!
just for my knowledge in learning if the ref number had a bracket such as 123/45 but wanted it to be 00123/45 how would this be achived?
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 19:57
Joined
Sep 12, 2006
Messages
14,901
hi all
I want a button that when clicked if the field is less than 5 digits to automatically put in the leading Zeros how do you do this?

ButtonName: BTNClickSearch
Field: TxtRefNumber

reason for this is if its more that 5 than a different ref number is searched :)

A number is a number irrespective of the leading digits. If you need a string then your need to manage differently.

eg
007
0007
00007
000007

are all number 7. If the leading zeroes make a difference (as with a phone number) then you need to treat it as a string.
 

Kevin.Meers

New member
Local time
Today, 19:57
Joined
Oct 5, 2012
Messages
5
If you always want 5 chars and Me!txtRefNumber is less than 10000 a simple solution would be :-
Right("0000" & Me!txtRefNumber, 5)
 

Users who are viewing this thread

Top Bottom