Dilemma for Key Field

  • Thread starter Thread starter Jonathan Wolgamuth
  • Start date Start date
J

Jonathan Wolgamuth

I have a database that I want to use to track corporate records.
Others have determined that they want to use a key field that is a six
character date (like 092006) followed by a four character number (like
0001).

Is there a relatively painless way to get my form to "create" this key
field based on the date and the next available number?

Thanks in advance for any help.
 
Try something like this:

Format(DMax("Right$([MyKeyField],4)","MyTable","Left$([MyKeyField],6) = " &
Format(Date,"mmddyy"))+1,"0000")

This works against the current date, which you'll need to replace if you
want to use a different date.

You could put this in the Default property of a textbox.

Barry
 
Johnathan,
Well, as with any counter field, eventually you'll go past 4 digits, so if "and 4
digits" is an iron clad rule, then that solution would be quite a different matter.
Let's assume that the date portion is always 6 digits, and let the counter value grow
without restriction.

Use a separate primary key field (ex. OrderID/autonumber) that you'll use in the
application design as the key field to and establish maintain relationships, and only
display the "created" (ex. ClientRef) to the client. The OrderID can be hidden, and for
your use only... the ClientID is just a meaningful value for their reference. ClientRef
will be a text field...

Using the BeforeUpdate event of the form...
ClientRef = Format(Date(),"mmddyy") & OrderID
 
This is best done using two different fields. The character date part you
can do with Format(Date,"mmyyyy")
However, for sorting purposes you really should make it come out as YYYYMM.
That way, all of one year will sort together; otherwise, you would get
092006
092007
102006
102007
etc.
So use Format(Date,"yyyymm")
Then, use an Integer field to create the Sequence number. It is almost a
certainty you will go beyond the four digits and create a problem. It has
not been that long ago there was a post where someone had gotten into exactly
that pickle and was looking for a way out. This you can do in the Current
event of your form for new records only:

If Me.NewRecord Then
Me.txtSeQNum = Nz(DMax("[SEQ_NUM]", "MyTableName", "[DATE_PART] = '" &
Me.txtDatePart & "'"), 0) +1
End If
(txtDatePart being the yyyymm as above)
 
Jonathan said:
I have a database that I want to use to track corporate records.
Others have determined that they want to use a key field that is a six
character date (like 092006) followed by a four character number (like
0001).

Is there a relatively painless way to get my form to "create" this key
field based on the date and the next available number?

Thanks in advance for any help.
Click to expand...

I appreciate all the replies and have it solved now--you've all been
very helpful! Thanks!
 
Back
Top