Need help with formatting an ID field

  • Thread starter Thread starter Dave
  • Start date Start date
D

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
 
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
 
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
 
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
Click to expand...

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.
 
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
 
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
 
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
 
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)
Click to expand...

Did you get this working, Dave?
 
Back
Top