Input Mask: SB-01

[Iram]

Hello.
I am using Access 2000 and I need to create an input mask that will give me
two letters a dash and two numbers with the first number possibly being a
zero i.e.
SB-01
SB-02
SB-03
SB-04
SB-11

Could you please help with this input mask?

Thanks.
Iram

 

[Trini Gal]

Iram,

Maybe you should take a look at this
http://www.databasedev.co.uk/add_prefix.html

Hope this helps.

 

[Iram]

By the way, the letters could be any letters and have to be capital i.e.
LO-01
LO-02
SM-01
SM-02

Thanks.
Iram/mcp

 

[BruceM]

You could define the table field as a text field, set its input mask to
LL\-00;0, then set its format to >. The format will force the first two
letters to capital letters, even if the user enters them as lower case. The
change will be seen after the user exits the control; it will not compel use
of the Shift key.
This is one of several ways of accomplishing your objective. If the numbers
are to be in sequence you may wish to consider something other than making
them user-defined.

 

[Iram]

Thanks Bruce. I'll try it...

You could define the table field as a text field, set its input mask to
LL\-00;0, then set its format to >. The format will force the first two
letters to capital letters, even if the user enters them as lower case. The
change will be seen after the user exits the control; it will not compel use
of the Shift key.
This is one of several ways of accomplishing your objective. If the numbers
are to be in sequence you may wish to consider something other than making
them user-defined.

 

[Iram]

Is there any way to create an Input Mask that would give something like this
upon creating a new record?

06/05/22-SB-01

Where 06/05/22 (today's date) and -SB would be populated automatically and
all we would have to do is type in 01, 02, 03 at the end?

Each day we have many checks come in and we organize them into batches.
The field in question is the Primary key in the main form and the subform
has all of the reocurring checks that are related to this batch number. SB,
SM, LO stand for different office names and 01, 02, 03... stand for different
batches in the same day.
It would be nice if this field could be populated each day completely by
knowing what today's date is and then sequentially adding to the batch number
for each new batch that day. Would you know of a way to do this?

Thanks.
Iram/mcp

 

[BruceM]

That is an entirely different question than you asked initially. It can be
done, but not with an input mask. How is the office name selected? Is
there a combo box for selecting the office name when you start a new record?
Is two digits (up to 99 records per office per day) adequate?

 

[Iram]

Sorry about the new question.
Actually I have three different forms, one form for each office. I was
looking at putting the input mask on the form field called Packet#. Each
office puts in about 20 batches a day into the system so 99 would be plenty
sufficient. I would like for that field to understand what the current date
is then put the Office i.e. SB and then the sequential batch number 01, 02,
03 for each new batch.
The main thing is since this field is a Primary key it has to be unique.
That is why we have been manually typing in the date office and batch number
to make each batch unique.

Thanks.
Iram/mcp

 

[John Vinson]

Is there any way to create an Input Mask that would give something like this
upon creating a new record?

06/05/22-SB-01

Where 06/05/22 (today's date) and -SB would be populated automatically and
all we would have to do is type in 01, 02, 03 at the end?

Each day we have many checks come in and we organize them into batches.
The field in question is the Primary key in the main form and the subform
has all of the reocurring checks that are related to this batch number. SB,
SM, LO stand for different office names and 01, 02, 03... stand for different
batches in the same day.
It would be nice if this field could be populated each day completely by
knowing what today's date is and then sequentially adding to the batch number
for each new batch that day. Would you know of a way to do this?

Well... I'd say, DON'T.

This is called an "Intelligent Key" - and that's not a compliment!
Storing three disparate types of information in one field is A Very
Bad Idea, and a good recipe for trouble down the line.

I'd suggest instead using *three* fields - a date/time field, default
value Date(), to record the date the record was entered; a two-byte
text field for the office (you can use a Combo Box to fill this),
defaulting to the user's office; and an integer sequence field. You
can use code in your form's BeforeInsert event to fill in the sequence
number:

Private Sub Form_BeforeInsert(Cancel as Integer)
Me!txtSeqNo = NZ(DMax("[SeqNo]", "YourTableName", "[Office]='" &
Me!cboOffice & "' AND CheckDate = #" & Date() & "#")) + 1
End Sub

Use the AfterUpdate event of the Office combo if one user may be
entering data for multiple offices.

You can make these three fields a joint Primary Key; or, if you
prefer, create a unique Index on the three fields, and use an
Autonumber primary key.

John W. Vinson[MVP]

 

[BruceM]

Iram,

A few additions to what John has written. I think you will find that
something like the following will give you the desired format. BatchDate is
a field in tblYourTable. As John suggested, set its default value to
=Date(). That way today's date will appear on all new records. SeqNo is
the field in which the sequence number is stored. txtSeqNo is an unbound
text box in which the number is concatenated.

Me.SeqNo = Nz(DMax("[SeqNo]", "tblYourTable", "[BatchDate] = #" & Date &
"#")) + 1
Me.txtSeqNo = Format(Date, "mm/dd/yy") & "-SB-" & Format(Me.SeqNo, "00")

If there is to be a choice of offices then the part of John's code that
referenced [Office] would need to be added.

Note that in a multi-user environment you need to guard against duplicating
this number. A quite effective way is to use a command button to assign the
number, then immediately save the record.

Is there any way to create an Input Mask that would give something like
this
upon creating a new record?

06/05/22-SB-01

Where 06/05/22 (today's date) and -SB would be populated automatically and
all we would have to do is type in 01, 02, 03 at the end?

Each day we have many checks come in and we organize them into batches.
The field in question is the Primary key in the main form and the subform
has all of the reocurring checks that are related to this batch number.
SB,
SM, LO stand for different office names and 01, 02, 03... stand for
different
batches in the same day.
It would be nice if this field could be populated each day completely by
knowing what today's date is and then sequentially adding to the batch
number
for each new batch that day. Would you know of a way to do this?

Well... I'd say, DON'T.

This is called an "Intelligent Key" - and that's not a compliment!
Storing three disparate types of information in one field is A Very
Bad Idea, and a good recipe for trouble down the line.

I'd suggest instead using *three* fields - a date/time field, default
value Date(), to record the date the record was entered; a two-byte
text field for the office (you can use a Combo Box to fill this),
defaulting to the user's office; and an integer sequence field. You
can use code in your form's BeforeInsert event to fill in the sequence
number:

Private Sub Form_BeforeInsert(Cancel as Integer)
Me!txtSeqNo = NZ(DMax("[SeqNo]", "YourTableName", "[Office]='" &
Me!cboOffice & "' AND CheckDate = #" & Date() & "#")) + 1
End Sub

Use the AfterUpdate event of the Office combo if one user may be
entering data for multiple offices.

You can make these three fields a joint Primary Key; or, if you
prefer, create a unique Index on the three fields, and use an
Autonumber primary key.

John W. Vinson[MVP]