Not sure how to code this...

[aiikahn]

For example:

- the first request for the day would be "KEZ6313001AA" ...the 2nd would be "KEZ6313002AA" ...the 3rd "KEZ6313003AA"

- for the next day, it would be "KEZ6314001AA" ..."KEZ6314002AA" ...& so forth

- now the 'KEZ' is a constant string so it would always begin with "KEZ"; however, the rest is more complicated to concatenate.

- the next 3 numbers consist of the '6' from 2006 and the '313' is for the n-th day out of 365.

- the final alphanumeric number '001AA', '002AA' are the sequence numbers ('AA' will also stay the same); this number will reset to '001' each day.

I greatly would appreciate any assistance anyone could offer. Thank you!!!

v/r

aiikahn

 

[aiikahn]

What happens if this application is still "live" 3 years from now? Are you going to be happy with the year reverting to 0 so that all new records will sort before all the old records?
What happens if you have more than 999 requests for a given day?
What is the point of storing 5 characters that never change in each record?
When we used to make fields like this in the old mainframe systems, we never stored the field as a single string. It was always broken up into its atomic parts.

In order to work with this efficiently, you really need to break up the field into at least three parts. The year part, the day part, and the sequence number itself. You would find the next sequence number by using the Max() function.

Me.SeqNum = Nz(DMax("SeqNum", "YourTable", "YearPart = Year(Date()) AND DayPart = DatePart("y", Date())),0) + 1

I am just trying to improve a particular tracking system in our office on how we handle requests for work. The current system is being done manually by logging the data into a logbook. I am trying to generate a control number that would autopopulate a given textbox in a form in MS Access.

The zero ('0') would work just fine for the year part; the records will only stay for 2 years until they're finally archived.

The 'KEZ' is the 3 letter code for the department; while the 'AA' in the end is basically the subsequence of the control number given to the customer. If a customer has more than one request, we would simply mark them with 'AB', 'AC', 'AD', etc.

It's never happen to the department before but I hope we won't ever get more than 999 customers in just one day with more than 999 requests. We wouldn't have the manpower for that...

 

[gemma-the-husky]

surely you can just build up your string with concatenating parts

ie KEZ & something else & something else etc.

you might need to use some format command to change the number into a fixed char string with leading zeros.

I think the point Pat is referring to is that if you expect the dbs to establish the next sequential numbers then its so much easier if each bit of the entire code is kept distinct. If you have to try to find the middle bit of the string, you will have to break it all down again anyway.

It is importnat to bear in mind that the presentation of data is generally independent of the storage mechanism. ie if you store the data in segments, you can still show it as a combined string, although if you have a long established database, it might be a bit hard to split it all now.

 

[pbaldy]

Were the answers on the other site you posted this on last week not to your liking? If so, it would be polite to reply there to that effect.

 

[aiikahn]

I apologize for that, Paul. I'm new with forums and I think I responded to it. I'm still kinda pinging on how it works. I do appreciate your assistance and thank you. I've been really rusty with coding and I'm still trying to recall how certain functions work in access. I didn't mean to give that impression...I truly apologize...

Honestly, I'm trying to figure out what Nz() that Pat was using...but I know I'll be able to get it all together...Thank you so much to all of you!