Need help with formatting an ID field

[Dave]

Access 2003

Field is "dispatch_id' and is a text field
There is also a field called "dispatch_date" in the same table

So I need the value assigned to the dispatch_id (when the record is created
from a form) to be something like this:
03011086

Where the first 6 characters are the date of the dispatch and the last
character (but could be more the 1 character) is the number of that dispatch
for that date.

So in my example the dispatch_id was the 6th record created "FOR" the
dispatch date of March 11,2008

I think I need to do this as code on the data entry form with out any change
to table structure.

Hope I explained this well enough.

Thanks in advance,

dave

 

[MPM1100]

This is not a difficult task...

(1) Create a procedure in the form's 'BeforeInsert' Event
(2) Query your records to look up the last/max value for the date in question.
(3) Take the resulting value and increment as desired
(4) Assign the new value to your id field

Hope this helps...
MPM1100

 

[Dave]

Thanks MPM1100,

may not be detailed enough for my limited skill set but I will give it a try
and get back to you with the results.

dave

 

[John W. Vinson]

Access 2003

Field is "dispatch_id' and is a text field
There is also a field called "dispatch_date" in the same table

So I need the value assigned to the dispatch_id (when the record is created
from a form) to be something like this:
03011086

Where the first 6 characters are the date of the dispatch and the last
character (but could be more the 1 character) is the number of that dispatch
for that date.

So in my example the dispatch_id was the 6th record created "FOR" the
dispatch date of March 11,2008

I think I need to do this as code on the data entry form with out any change
to table structure.

Hope I explained this well enough.

Thanks in advance,

dave

This is generally A Bad Idea. Fields should be "atomic", having only one
value; constructing a composite key is generally not good design!

Store your date, in a date field, and look it up when necessary. To calculate
the number of dispatches in a day, store the date and time in the date field
and use a Query to count the number of records.

You *can* do this, as suggested elsewhere in this thread - but it's really a
1960's way of managing data, and in a proper database it's not a good
approach.

 

[Dave]

Thanks John, I always value your input.
I this case however I am stuck with an existing DB with over 100,000 records
that have been created this way.
The DB was built with an Access backend and a Net.Framework front and the
"Source Code" was not provided.
So as I saw it the only alternative was to "rebuild" the front end in Access
(they need a couple of new fields).
I (and they) want to minimize the change in the "look and feel" and current
structure of the data.

Thanks

Dave

 

[Dave]

OK - going to need a little more help/detail here.
I can use the query wizard to get at least part of my needed info but do not
know how to put that on the "BeforeInsert" event.

dave

 

[Dave]

wait! I may be close.
I was able to find the SQL for the query I created with the wizard.


SELECT Dispatch.dispatch_id, Dispatch.dispatch_date
FROM Dispatch
WHERE (((Dispatch.dispatch_date)=[forms]![frmDispatch]![dispatch_date]));

but still needs some fine tuning (does not get a count to find the lase
entry for the date in question)

dave

 

[John W. Vinson]

wait! I may be close.
I was able to find the SQL for the query I created with the wizard.


SELECT Dispatch.dispatch_id, Dispatch.dispatch_date
FROM Dispatch
WHERE (((Dispatch.dispatch_date)=[forms]![frmDispatch]![dispatch_date]));

but still needs some fine tuning (does not get a count to find the lase
entry for the date in question)

Did you get this working, Dave?