ProjectID Default Expresion

J

JackCGW

I use an expresion in the default value that creates a [ProjectID] in the
main tabkle of my DB. The expresion creates a number that is basically a
julian date with the minutes added on the end like this:
=Format(Date(),"yyyy") & Format(Now(),"y") & Format(Time(),"mm")
The result is Year, day of the year and current minute eg; 200522511.
I would like to replace the minutes with an incremental number so the first
project of the day would be 1 and so on...
I can't seem to figure out a simple way of doing this.
Jack
 
L

Larry Daugherty

As you assume control of the bits of your application as you appear to
be doing you have to anticipate that doing something on your own won't
be as easy as formatting the result of a ready made function.
Thinking through your solution the first time requires a bit of effort
[Translation: there isn't a "simple" way. There are ways that are
made up of simple steps.]. I typically include a table for User
Options in each application. You don't have to show the users
everything in the table. The record format is: Autonumber,
VariableName, VariableValue, VariableNotes

Assuming you do the above, I'd have one record for the last used
sequence number and another for the last date used. I would then
create a function procedure that returns the sequence number. You
don't need to pass the function any variables because the system date
is the source of one argument for comparison and the saved date is the
other. In your function, compare the system date to the saved date.
If the system date is newer, store the number 1 in the sequence record
and the system date in LastSeqDate record and return the number as the
function's value. However, if the dates are equal then retrieve value
in the sequence number record, increment it and store it back in the
record and make that the value returned by your function. I'll leave
it to your imagination to convert the above date results into the
Julian dates you want to use with one caveat: do your math with the
full year, month and day before converting to Julian. The year has to
play in the date calculations. And, of course, you could return the
Julian date separately and concatenate the result of your function to
it.

There are other ways to get there.

HTH
 
Top