sql command or condition

[fastmike]

hi i was wondering if anyone can tell me a condition or a command in which a user will be able to enter only 6 digit number. like 123456. if he tries to enter 234 the database should not accept. is this possible also? any help would be great.
Thanks

 

[Fonz]

Use an Input Mask on the control in a Form like
999999 or 000000 or ###### where:

0 Digit (0 to 9, entry required, plus [+] and minus [–] signs not allowed).
9 Digit or space (entry not required, plus and minus signs not allowed).
# Digit or space (entry not required; spaces are displayed as blanks while in Edit mode, but blanks are removed when data is saved; plus and minus signs allowed).

All the best:
Mark

 

[fastmike]

Thanks alot. i was just wondering if there is any possibility that if a user try to enter 4 numbers the rest of the 2 digit can be fill out by 00 like for instance user enter
2345
is it possible if there is any command that database can automatically fill the rest 2 digit by
002345
thaNKS alot for your help

 

[KeithG]

Does the user enter this on a form? If so you could use the Len function in the After_Update event of the text box to add zeros if applicable.

 

[fastmike]

everything is working fine. thanks for the info. The user enters this in the database i guess forms. i am a newbie to access so still trying to learn. and where can i put that Len function?

 

[fastmike]

yes the user enter this in the form. but where can i use that LEN function i am newbie to access sorry. and what is that LEN function

 

[KeithG]

In the After_update event of the textbox on the form.

 

[fastmike]

i typed this giving me an error or still ask me to enter six numbers.

Number: LEN("HELLO")

 

[KeithG]

You need to put some thing like the below code in the AfterUpdate event of the text box that contains the number.

if len(Me.[TextBoxName])>6 then

for x=len(me.[TextBoxName]) to 6
me.[TextBoxName]="0" & me.[TextBoxName]
next x

Also you will want to change this fields data type to text(if it is not already) because a true number can not start with a zero.

 

[fastmike]

Thanks abunch dude let me try it and see if i can make this thing work. i appreciate all your help.

 

[fastmike]

one more question the input mask still remains the same 000000 in the field or do i have to change it also.
Thanks

 

[KeithG]

I would get rid of it.

 

[fastmike]

Thanks bro. i will keep you updated thanks alot for your help.

 

[fastmike]

hi. here is the code i typed in afterupdate basically in VB.

Private Sub Combo0_AfterUpdate()
If Len(Me.[Combo0]) > 5 Then
For x = Len(Me.[Combo0]) To 6
Me.[Combo0] = "0" & Me.[Combo0]
Next x

End If
End Sub

My textboxname is Combo0. but nothing is happening. i can now enter as many number as i want instead of 6. any help would be great and when i type this whole code in afterupdate it gives me macro error.

 

[Fonz]

This is how you do it...

STICK with what I told you to do :
In the control, set the Input mask as -

999999 or 000000 or ###### where:

0 Digit (0 to 9, entry required, plus [+] and minus [–] signs not allowed).
9 Digit or space (entry not required, plus and minus signs not allowed).
# Digit or space (entry not required; spaces are displayed as blanks while in Edit mode, but blanks are removed when data is saved; plus and minus signs allowed).

now in the After update of the control (make it a TEXT BOX called MyNumber

Sub MyNumber_AfterUpdate()

If not isnull(MyNumber) then
' this will format the number with leading zeros upto a length of 6,
' increase the no of zeros to increase the size of the number
MyNumber = Format(MyNumber, "000000")
End If

End Sub

Cut and paste the Sub above into the after update of the control. Make sure that the control name is changed to YOUR control name (if you did not call it MyNumber).

And that will work ... or your money back!
All the best
Mark

 

[fastmike]

hey Mark.
Thanks for replying. my control box name is Number. so i replaced Mynumber to Number and paste it in the after_update box and when i try to enter the numbers in the Number field like for instance i tried to enter 3 digit that input mask 000000 give me a msg that i had to type 6 numbers + when i enter six numbers it says that the macro is not saved or something like that what is that bro?

 

[fastmike]

hey Mark.
Thanks for replying. my control box name is Number. so i replaced Mynumber to Number and paste it in the after_update box and when i try to enter the numbers in the Number field like for instance i tried to enter 3 digit that input mask 000000 give me a msg that i had to type 6 numbers + when i enter six numbers it says Microsoft access cant find the macro. any help would be great

 

[fastmike]

Thanks alot fonz i got it i appreciate all your help . Thanks gUys