validate in access 16, to start with a specific character (1 Viewer)

akika

Registered User.
Local time
Today, 09:12
Joined
Aug 7, 2018
Messages
102
hi,

In a form, i have a text field how can i add a validation for that.
Should always start with a Character followed by a space and numbers.
e.g A <Space> 0123

A <Space> 9876
the length of the numbers after the space should have length of 4. How to add the leading zeros to it.
 

isladogs

MVP / VIP
Local time
Today, 16:12
Joined
Jan 14, 2017
Messages
18,186
If the field always starts with 'A ' then I suggest you use a number field and format using 0000. This will automatically add one or more preceding zeroes as required.
Then in a query concatenate using 'A ' & [FieldName]
 

mike60smart

Registered User.
Local time
Today, 16:12
Joined
Aug 6, 2017
Messages
1,899
Hi

IN the Design View of your Table add the name of the field required and make it a Text Data Type.

Leave your Cursor in this field and then in the General Section select Input Mask and make the mask this :-

>L\ 0000;0;_
 

essaytee

Need a good one-liner.
Local time
Tomorrow, 03:12
Joined
Oct 20, 2008
Messages
512
Since everyone beat me to it, I'll just post a link to the inputmask and not include the rest.
 

akika

Registered User.
Local time
Today, 09:12
Joined
Aug 7, 2018
Messages
102
Ive tried the mask in the general section but its taking all characters as input.
>L\ 0000;0;_

is there another change to do or can we do it via VBA code?
On click after update

to accept first character A <space><4number>
 

essaytee

Need a good one-liner.
Local time
Tomorrow, 03:12
Joined
Oct 20, 2008
Messages
512
From the link in post #4


So for the bare bones of it:
Code:
"A "0000

I tested that umpteen times, admittedly I didn't get it right the first time but the answer was in the docs. BTW, that is a space directly after the "A".
 

Attachments

  • 2019-04-26_8-20-12-inputmask.jpg
    2019-04-26_8-20-12-inputmask.jpg
    26 KB · Views: 265

isladogs

MVP / VIP
Local time
Today, 16:12
Joined
Jan 14, 2017
Messages
18,186
Interestingly, if you use format \A 0000 for the reasons explained above, Access converts it to "A "0000
If you don't need a space but use "A"0000, Access converts it to \A0000
 

akika

Registered User.
Local time
Today, 09:12
Joined
Aug 7, 2018
Messages
102
HI
Can u pls help, ive tried to add the input mask in attached db
But when entering the value from the form its not triggering the validation

can you pls check & let knw whts wrong? thxs
 

Attachments

  • emplCode.zip
    563.7 KB · Views: 45

essaytee

Need a good one-liner.
Local time
Tomorrow, 03:12
Joined
Oct 20, 2008
Messages
512
There's no inputmasks against any of the fields (in the form) in the db you uploaded.

Edit: Add the inputmask to the form field. Also, remove the @ symbol from Format property of the the field. If you leave it there, your "A " part will not be displayed.

Edit: Edit: Use the inputmask I posted in post #6
Code:
"A "0000
Use this as a known starting point. How you have it set up at the moment (in your table), any letter can start the string. I'm sure you said it must be "A " followed by a space. I personally wouldn't worry about an inputmask at the table level but if you want your users to enter data directly via the table, then well, yes, set it up.
 
Last edited:

akika

Registered User.
Local time
Today, 09:12
Joined
Aug 7, 2018
Messages
102
ive removed the format @ and used the "A "0000, in the table design.
but its accepting when inputing same in the form.

should another steps be done?
 

Attachments

  • emplCode (2).zip
    565.5 KB · Views: 45

essaytee

Need a good one-liner.
Local time
Tomorrow, 03:12
Joined
Oct 20, 2008
Messages
512
You need to also add the InputMask to the textbox control on the form.
 

akika

Registered User.
Local time
Today, 09:12
Joined
Aug 7, 2018
Messages
102
thanks a lot
it's working
btw is it possible to make the last character optional ?
 

essaytee

Need a good one-liner.
Local time
Tomorrow, 03:12
Joined
Oct 20, 2008
Messages
512
thanks a lot
it's working
btw is it possible to make the last character optional ?

Have you checked the docs? I supplied a link in an earlier post, all the information is in there. I haven't got access to Access at the moment but check out 9.
 

akika

Registered User.
Local time
Today, 09:12
Joined
Aug 7, 2018
Messages
102
hi,
ive tried to use "A "000;0 input mask
but last number / character not becoming optional.
 

Mark_

Longboard on the internet
Local time
Today, 09:12
Joined
Sep 12, 2017
Messages
2,111
Something special to note;
IF you want to force "A " followed by 4 (or less) digits, why not only prompt for 4 digits? You can always format for what you put into your table AFTER they've entered it. This will avoid several issues, not the least of which is users pressing one to many/few spaces for entry.
 

akika

Registered User.
Local time
Today, 09:12
Joined
Aug 7, 2018
Messages
102
hi,
This one is working..
im using input mask "A "0000a;0;_ the last character is treated as optional
and allow input of the 4character if less character is input getting error msg.

I need a help for.. ive added an before_update on the field Emplcode to check if the code entered already exists. It's throwing the error message "Code already exists" when i input the optional character e.g A 24455

But if i enter "A 2445" that already exist in the table. It's not throwing the error ..
can u pls help?

Private Sub Form_Code_BeforeUpdate(Cancel As Integer)
If DCount("*", _
"CUSTOMER", _
"[EmplCode] = """ & Me.Form_Code.Text & """") > 0 Then
Cancel = True
MsgBox "Code already exists", vbOKOnly, "Warning"
End If
End Sub
 

Attachments

  • emplCode3.zip
    526 KB · Views: 52

Users who are viewing this thread

Top Bottom