Sequence Number

[Krash878]

I have hunted for the proper way to identify this and have come up null. In my company we have a sequence number that is applied to all work we do. The number is 11 digits. The first 3 digits apply to the bank, the next set of 4 applies to the Julian date and the last 4 apply to the item number. What is this called when you put numbers together to make a full number?

ok the real question now

When a user puts in the sequence number how can I have the table automaticly take digit 4567 and but it in another feild and translate to a date?

In Julian the 1001 breaks down to be January 1 2001

the first digit is the year and the year and the next 3 are the day of the year.
There can only be 365 days in julian or 366 if i is a leap year.

 

[RickSpr]

Informally, I guess you could call it a composite field.

When you concatenate data into a single field, the individual components lose their identity in the database, which leads to the kind of problem you're having. It would be better design, philosophically speaking, to store these components in separate fields. That wouldn't be convenient for your users, though, so you would probably want to let them key it into a single control and have code behind the form (in the control's AfterUpdate event procedure) break it down into the individual components using string functions (especially Mid$).

But that still wouldn't solve your problem completely, because you're using a Julian date, which isn't directly supported in Access. You're going to have to use code in the control's AfterUpdate event procedure to extract the Julian date digits (see the Mid$ function). The table can't do this for you, you have to write code. You'll also have to use code to convert it into an Access date type, if that's what you want. The following VBA statement would do it for you, given that you've already extracted the Julian date digits into a string called strJulian:
theDate = DateSerial(Val(Left$(strJulian,1)) + 2000, 1, 1) + Val(Right$(strJulian,3)) - 1
Note, though, that this will only work until December 31, 2009. That can't be helped: You've only got one digit for the year, so your Julian dates can only represent dates in a 10-year period. After that, the Julian dates start repeating and you won't get the right date any more. Rick Sprague