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

"Dave" wrote:

> 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
>  
> 

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

"MPM1100" <MPM1100@discussions.microsoft.com> wrote in message 
news:C6308D3C-3156-4D14-B8D5-2FC77AAFFDCA@microsoft.com...
> 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" wrote:
>
>> 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
>>
>> 

On Sat, 8 Mar 2008 08:11:23 -0800, "Dave" <dave@accessdatapros> wrote:

>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.
-- 

             John W. Vinson [MVP]

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

"John W. Vinson" <jvinson@STOP_SPAM.WysardOfInfo.com> wrote in message 
news:lhk5t39152mj7tgns1uo901n60iv375g9q@4ax.com...
> On Sat, 8 Mar 2008 08:11:23 -0800, "Dave" <dave@accessdatapros> wrote:
>
>>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.
> -- 
>
>             John W. Vinson [MVP] 

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

"MPM1100" <MPM1100@discussions.microsoft.com> wrote in message 
news:C6308D3C-3156-4D14-B8D5-2FC77AAFFDCA@microsoft.com...
> 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" wrote:
>
>> 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
>>
>> 

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

"Dave" <dave@accessdatapros> wrote in message 
news:874613C2-FFC2-4E8D-98AB-C956653CA0C5@microsoft.com...
> 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
>
> 

On Sat, 8 Mar 2008 10:26:21 -0800, "Dave" <dave@accessdatapros> wrote:

>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?
-- 

             John W. Vinson [MVP]