Composite Document Number

[Teady91]

Hi all,
We have a convention in our company for naming documents that are saved in a "controlled format".
I am building a database to keep track of these documents, authors, dates, revisions etc...

The naming convention looks like this:

####-123-****

#### being a four digit Project Number

1 corresponds to discipline Ie civil, electrical, mechanical
2 corresponds to Doc type ie Drawing, Calculations, Schedule...
3 corresponds to subtype ie single line diagram, overview, schematic etc.

and **** is just a sequential number.

At present I have a form that generates this number when i select the discipline, doc type, subtype and project number from their several combo boxes.

What i am curious about is, is there a way that i could start with a document number and dissect it so that i can display the doc type etc in separate controls?

 

[John Big Booty]

You should be able to dissect your number using a combination of various String functions. Have a look at Left(), Right(), and Len() amongst others.

 

[Teady91]

Thanks john, i will have a play with those. I am still not quite sure how i am going to set this up, maybe an on current event will return the values i want from the string functions in the combo boxes... i guess then i wont have to store all the values, just have a unbound box for each component drawing from the doc number... i will see what i can do and let you know how i go thanks

 

[Galaxiom]

I would definitely consider storing the components of the document number as separate fields and concatenating them for display.

 

[Teady91]

Yea galaxiom, I thought that would be the way to go but i need to reference that number as a whole quite often for opening and moving docs, filtering sub-forms etc i know that what im doing is not the best database design since you shouldn't store a calculated field but i think it makes my coding easier. I have decided to store both.
Can you see any detrimental problems that might arise from this down the track?

Thanks for your reply

 

[boblarson]

You don't need to store both. Store the parts and then create a BASE QUERY which you will use for almost everything INSTEAD of using the table. Then you can have it concatenated for use there and NOT store it.

 

[Teady91]

Thanks bob, I will take that on board. I think i need to redesign my thinking before i redesign my db :s