Help on creating a referance number

[red skelton via AccessMonster.com]

Hi everyone and Merry Christmas,
I have an interesting reference number that I'm trying to program that will
display on a form. I hope I can explain this enough. there are a total of
14 characters in this number. The first 7 are static, the the next three are
the julian day (excluding the year, just the day), the next two are static
and the last two need to increase starting at 01 for each new Reference
number inputed that day, Example; 1234ABC(always the same) 001 (julian
julian julian) XX (always the same) 01 (increasing by 1 for each number).
Does anyone have an idea for me as to where to even start this equasion? If
it helps any, the form's control source is a query. Any help would be
greatly appreciated.

Respectfully,

Red

 

[Guest]

It's almost never a good idea to have a compound column as you suggest. I'd
split the components of the reference number into separate columns.
1-7 Static1
8-10 JulDay
11-12 Static2
13-14 Sequence
You can still display it on the form as a single field. Just concatenate the
components in your Query e.g. SELECT Static1 & JulDay & Static2 & Seq As
RefNum

If this to be the primary key of your table, make all four components a
combined primary key. Look in Access help for how to do this. This will
provide the insurance that the key is unique. You can use the DMax function
(look in Access help) to figure out the next sequence number to be assigned.

- Dorian

 

[red skelton via AccessMonster.com]

Dorian,
Thanks for the response. Was off yesterday sorry I didn't get back to you.
I have a question for you. I have created seperate columns on my table as
you said below. I have no problem with the two static fields, but I cant
figure out the Julian date or the Sequence. I tried numerous types of code
found here in this forum to convret a date into Julian but the field just
shows blank. Also, I have taken your advice on using Microsoft help and it
is opointing me to using a RunningSum property for my last two numbers. I
cant find anywhere on my text box properties that gives me an option of
runningsum. Any suggestions on either problem? Thanks for your time.
VR,
Red

It's almost never a good idea to have a compound column as you suggest. I'd
split the components of the reference number into separate columns.
1-7 Static1
8-10 JulDay
11-12 Static2
13-14 Sequence
You can still display it on the form as a single field. Just concatenate the
components in your Query e.g. SELECT Static1 & JulDay & Static2 & Seq As
RefNum

If this to be the primary key of your table, make all four components a
combined primary key. Look in Access help for how to do this. This will
provide the insurance that the key is unique. You can use the DMax function
(look in Access help) to figure out the next sequence number to be assigned.

- Dorian

Hi everyone and Merry Christmas,
I have an interesting reference number that I'm trying to program that will

[quoted text clipped - 11 lines]

Red