Autonumber for year 2004

[Qrackerman]

I have a table that was started in 2003 with data that is posted to an ASP page. The autonumber field(ID) is formatted differently on the ASP page to have a prefix on it ie: DM03-0001 DM is a code we use, 03 is the year -0001 is the ID of the record in the database. I need to start from 0 for the year 2004 in the table without losing the records from 2003. Any suggestions??

Ron

 

[WayneRyan]

Ron,

You need to keep the current Autonumber as a key
field, but your "Number" has to be handled with
code. You will have "DM03-0001 DM" & "DM04-0001 DM".

You should really split that up into fields:

KeyPrefix = "DM04"
KeyNumber = "0001"
KeySuffix = "DM"

On display: KeyPrefix & "-" & KeyNumber & " " & KeySuffix

Using the BeforeInsert event you can:

KeyPrefix = "DM" & RIght(Format(Date, "yyyy"), 2)
KeyNumber =  DMax("[KeyNumber]", "YourTable", "[KeyPrefix] = '" & KeyPrefix), 0) + 1
KeySuffix = "DM"

If you don't break it up into fields, you have
a mess:

Dim TheYear As String
Dim TheNumber As String
Dim TheKey
TheYear = "DM" & Right(Format(Date, "yyyy"), 2)
TheNumber = DMax("Mid([KeyField], 6, 4)", "YourTable", "[KeyField] Like " & TheYear & "-*"), 0) + 1
TheKey = TheYear & "-" & TheNumber & " DM"

hth,
Wayne

 

[Qrackerman]

Wayne,

Thanks for the reply. I didn't make my explanation very clear. There is no suffix. I was explaining what the DM was used for. The format is DM03-0001. I am not very good at this so I am a little dense. Should I not code the suffix? Also, am I understanding you correctly by saying I should have these in separate fields inside the database and should do the coding within my ASP page??

Ron

 

[WayneRyan]

Ron,

Because it is a composite key, you will have to construct it.
There is no way for Access to do it.

Access can "globally" put something like "DM03-" in front
of all records for forms and reports, but it can't handle the
cases where you have "DM03-" and "DM04-".

I suggested three fields due to the trailing " DM". If that is
not present (or REALLY a constant) then two fields will work
fine.

It is just a couple lines of code that will give you the number
sequence that you need.

Wayne

 

[Qrackerman]

Wayne,

I have 1149 records currently in my database from 2003. I want to still display them and start the suffix number back to zero for 2004. What I have is DM03-0000 to DM03-1149 and I need to add for this year DM04-0000 to ?

Thanks
Ron

 

[WayneRyan]

Ron,

Since they are all '03 records you can do
the following:

1) Back up your database.

2) Get your table in design mode and make
the two new fields: KeyPrefix and KeyNumber

3) Run an update query setting:
KeyPrefix = "DM03"
KeyNumber = CurrentAutonumberField

Then put the previous code in the Before
Insert event of your form and add a
record.

The new record should be OK, since it will
try to find a "DM04" resulting in a 0 for
the DMax. Then when it adds the one you
should have:

KeyPrefix = "DM04"
KeyNumber = "0001" <--- Oops !!!

You'll want to put the Format function
around the DMax to pad your "number" to
four digits.

Now is the time to think this through,
you can have the KeyPrefix and KeyNumber
both numeric and format them for forms
and reports if you want. Or you can
manipulate them and store them as strings.

The "pure" solution would probably be to
store them as numbers; 2004 and say 7"
Then you can format them for display.
Sorry for the "wrong" turn, but now is
the time to think of these things.

Wayne

 

[Qrackerman]

Wayne,

This is great, thanks. I am not sure if I mentioned that I am not using Access to display the data. I am posting it using ASP from Frontpage on our intranet site. So my code now for the number is:
<%
Dim ID
ID = Replace(FP_Field(fp_rs,"ID"),"DM03-", "")
If Len(ID) = 1 Then
ID = "DM03-000" & ID
Else
If Len(ID) = 2 Then
ID = "DM03-00" & ID
Else
If Len(ID) = 3 Then
ID = "DM03-0" & ID
Else
If Len(ID) = 4 Then
ID = "DM03-" & ID
Else
End If
End If
End If
End If
Response.Write ID%>

Ron

 

[WayneRyan]

Ron,

Don't know ASP, but I'm surprised to see all of those "DM03"s
hard-coded. I would think that one pass thru to clean up
your 03 data would get rid of all hard-coded things.

The rest should just be a quick routine to concatenate and
pad the DM0x-nnnn number formats.

Wayne

 

[Qrackerman]

Wayne,

The "Before Insert event of your form" you spoke of, that would be an Access form?

Ron

 

[WayneRyan]

Ron,

Yes, I don't know ASP, but in Access that would be the one.

Wayne

 

[Qrackerman]

Hey Wayne,

I want to concentrate on the number portion of this post. I have them DM0# thing worked out. What would be the correct way in Access to create the number basing it on the year and autonumber(ID)? If you remember, the last number for 2003 was 1159 and I would like the 1st number for 2004 to be 1 and increment from there.

thanks
Ron

 

[namliam]

Don't post the same question twice!

 

[Qrackerman]

I apologize if you feel I posted the same question twice. I do feel that it does apply to both threads. I am unsure if it would be handled on the Access side or on the ASP side. With different people browsing the 2 different areas, I felt I would be able to get help, not criticism.