AutoFill Field based on more then one piece of data

W

WBullock

I have a field called CEID.

I want it to always start with CE
Then I want it to be the date listed as yymmdd format without the /
Then I want it to assign an "A" after that example CE041026-A then
CE041026-B and so on.

How do I do this?
 
J

John Vinson

I have a field called CEID.

I want it to always start with CE
Then I want it to be the date listed as yymmdd format without the /
Then I want it to assign an "A" after that example CE041026-A then
CE041026-B and so on.

How do I do this?
Click to expand...

Well... DON'T.

This is called an "intelligent key". That's not a compliment. Storing
multiple kinds of data (dates for instance) in a field maybe made
sense in the 1950's or 60's when flat-file databases could have only
one key, but it makes no sense now. In addition, you're limiting
yourself to 26 records... someday you'll need a 27th.

Can you consider using just a simple, concealed autonumber ID, and
storing your date field in a date field?


John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
 
W

WBullock

We rarely get 26 calls in one day. This is the Number assigned to all cases.
It needs to be unique and this is how the company I am doing the work for
would like to number them. I will talk to them about the limitation. I
tested seperating the fields into 3. First field contains the CE, second
field contains the date, and the third contains the A, B, C, etc. I ran into
two problems.

When I concatinated, the date came in with the slashes ( \ ). ALso didn't
know how to auto assign A then B then C etc.
Can you still help me?
 
J

John Vinson

We rarely get 26 calls in one day.
Click to expand...

"Rarely" is the bane of my existance in such cases. What will you do
when you need to get "On Beyond Zebra"? Just discard the information
on those last two calls on the busy day?
This is the Number assigned to all cases.
It needs to be unique and this is how the company I am doing the work for
would like to number them. I will talk to them about the limitation. I
tested seperating the fields into 3. First field contains the CE, second
field contains the date, and the third contains the A, B, C, etc. I ran into
two problems.
Click to expand...

Ok... if you insist. You asked:

I want it to always start with CE
Then I want it to be the date listed as yymmdd format without the /
Then I want it to assign an "A" after that example CE041026-A then
CE041026-B and so on.


In the Form's BeforeInsert event (and yes, you MUST use a form; table
datasheets don't have usable events) click the ... icon and invoke the
code builder. Edit the code to something like this (I'm assuming your
table is named MyTable, your field named CaseNumber, and the form
control named txtCaseNumber - change these as appropriate):

Private Sub Form_BeforeInsert(Cancel as Integer)
Const strAlpha As String = "ABCDEFGHIJKLMNOPQRSTUVWXYZ!!"
Dim strCaseNumber As String
Dim strLetter As String
Dim vPrevious As Variant
' Set up the constant and date portion of the code
strCaseNumber = "CE" & Format(Date, "yymmdd") & "-"
' find the largest existing code for this day, if any
vPrevious = DMax("[CaseNumber]", "[MyTable]", _
"[CaseNumber] LIKE " & strCaseNumber & "*")
If IsNull(vPrevious) Then
' first call of the day, use A
strCaseNumber = strCaseNumber & "A"
Else
' later call, find the last letter
strLetter = Right(vPrevious, 1)
strCaseNumber = strCaseNumber & _
Mid(strAlpha, InStr(strAlpha, strLetter) + 1, 1)
End If
Me!txtCaseNumber = strCaseNumber
End Sub

This will assign CE041027-! to the 27th and all later calls; this will
cause an error if (as you should) you have CaseNumber as your table's
primary key. In a professional app I'd use error trapping to handle
this but this should at least get you started.


John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
 
W

WBullock

OK I can't get this code to work. I am attaching it to a before insert on
the form. The field name in the table is CEID. The field name in the form
is CEID also. The form is opening as a data entry form. I have doubled
checked the form and the code is word for word what you wrote. The only
thing is I am opening this form from a button on another form. Is this ok?

WHat might be wrong?

John Vinson said:
We rarely get 26 calls in one day.
Click to expand...

"Rarely" is the bane of my existance in such cases. What will you do
when you need to get "On Beyond Zebra"? Just discard the information
on those last two calls on the busy day?
This is the Number assigned to all cases.
It needs to be unique and this is how the company I am doing the work for
would like to number them. I will talk to them about the limitation. I
tested seperating the fields into 3. First field contains the CE, second
field contains the date, and the third contains the A, B, C, etc. I ran into
two problems.
Click to expand...

Ok... if you insist. You asked:

I want it to always start with CE
Then I want it to be the date listed as yymmdd format without the /
Then I want it to assign an "A" after that example CE041026-A then
CE041026-B and so on.


In the Form's BeforeInsert event (and yes, you MUST use a form; table
datasheets don't have usable events) click the ... icon and invoke the
code builder. Edit the code to something like this (I'm assuming your
table is named MyTable, your field named CaseNumber, and the form
control named txtCaseNumber - change these as appropriate):

Private Sub Form_BeforeInsert(Cancel as Integer)
Const strAlpha As String = "ABCDEFGHIJKLMNOPQRSTUVWXYZ!!"
Dim strCaseNumber As String
Dim strLetter As String
Dim vPrevious As Variant
' Set up the constant and date portion of the code
strCaseNumber = "CE" & Format(Date, "yymmdd") & "-"
' find the largest existing code for this day, if any
vPrevious = DMax("[CaseNumber]", "[MyTable]", _
"[CaseNumber] LIKE " & strCaseNumber & "*")
If IsNull(vPrevious) Then
' first call of the day, use A
strCaseNumber = strCaseNumber & "A"
Else
' later call, find the last letter
strLetter = Right(vPrevious, 1)
strCaseNumber = strCaseNumber & _
Mid(strAlpha, InStr(strAlpha, strLetter) + 1, 1)
End If
Me!txtCaseNumber = strCaseNumber
End Sub

This will assign CE041027-! to the 27th and all later calls; this will
cause an error if (as you should) you have CaseNumber as your table's
primary key. In a professional app I'd use error trapping to handle
this but this should at least get you started.


John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
Click to expand...
 
Top