Auto increment field in table with both a prefix and a date function

[Mwaki]

Hey!

I am trying to create auto increment values for a short text field that have the following format

RN-0818-0001
RN-0818-0002
.... and so on

I have entered the default value in the field I want to use as

=Format("RF") & "-" & Format(Date(),"mmyy") & "-" ....

But unfortunately I can't seem to figure out what the last part should be in order for it to auto increment.

Any help?

 

[plog]

In a database each discrete piece of information goes into its own field. So if you are putting that into one field, don't. You would have 3 fields, one for the RN part (Text), one for the Date part (preferably a date) and then one for the id (number).

Unfortunately, what you have described isn't an autoincrementable field. You've designed a custom auto number (search this forum for that term). At the beginning of September you want the id portion to reset to 1, correct? That's not an auto increment, that's going to take some coding.

If however you truly do want an autoincrement (where September just gets the next number), then you can use an autonumber for that 3rd field I described above. This would be my recommendation. I mean, what's so special about numbers that they have to reset each month? In terms of uniqueness (which is the point of all this) 437 is just as unique as 1.

 

[Mwaki]

Let me add a bit more context to my question. I already have an autonumber in the same table known as "Quote ID". The field I am trying to create is supposed to create reference numbers for each quote that I create i.e. RN-0818-0001. I want the reference number to indicate the month, year and unique incremental number. I was really trying to avoid coding for this.

I am however, willing to let it not reset at the beginning of each month. I would need to add more zeros though. Is it possible to bypass coding for this?

 

[jdraw]

Can you tell us more about why you want this?

Why not record a QuoteDate, then you can search, report or whatever?

You can have a routine to concatenate/calculate/manipulate field values and display a concocted code if necessary.
If you really need a sequential number you can work with Dmax(some value) +1

 

[Mwaki]

Well, there is already a date field in the table. There, I will record the date the quotation was issued. However, I need to create a reference number that would be used when I am eventually issuing a proforma invoice and a receipt. This is where the this important number should be generated.

 

[jdraw]

Then, in your table have a field for ProformaInvoiceDate, and use it and some vba to create your concocted code. However, your requirement might become more clear if you mocked up a sample of your proposed output.

 

[Mwaki]

I would post a pic but I cannot seem to find the button to post an image .
The Quotation table is supposed to have stored the quotations that I would be issuing to plausible clients. If it is approved, I would need to create an invoice then a receipt for the same work. However, not every quotation would get approved. So the quote ID looks like this QU0001, QU0002, QU0003...
And the reference number would be like the one I had posted above.
Is there any further clarification I could give here?

 

[Mwaki]

Here is a screenshot. I found the upload button

 

[jdraw]

Here is a link to various info on Database Planning and Design (and more).

Most will advise you to not have embedded spaces in Access table or object names.

 

[plog]

1. I agree with jdraw--no spaces in any names (table or field). Just makes coding more difficult. Jam the names together seperated by capitals: QuoteID, ReferenceNumber, etc.

2. Use the correct data type for your data. Call it [Date of Quote] all you want, but you are storing it as text, that's incorrect. Store it as a DAte/Time.

3. Reference number goes away. It will not exist in a table, it will exist as a calculation wherever you need it to. Most likely in a query, where you will then use it in reports.

What you would do is build a query on Quotations and create a field like so:

ReferenceNumber: "RN-" & Format([DateOfQuote], "mm & "-" & Format(QuoteID, "0000")


No need to store what you can caluclate.

 

[Mwaki]

I started on the DB yesterday so thank you for the heads up with the spaces. I will implement it promptly. Also, I had just started on the quotations table when I realized that the RefNo would be very instrumental in the entire DB. I understand your suggestion @plog when you say that I could calculate it. However, The nature of the DB would require me to have it stored permanently as it is what would be needed to make payments and track various consignments in case there are any issues in shipments. To use the quote date would not fly as I could issue more than one quotation a day. The issue with that format is that not all of them lead to invoices and hence it would misrepresent the business if I am issuing the second invoice of the month and it is RN-0818-0100

 

[jdraw]

Mwaki,

From experience-- make sure you understand WHAT you are trying to do; write it down; rework the description until it's clear; build a rough model; test it ; adjust as necessary, THEN
design the physical database.

Many people confuse WHAT is required with HOW to build it in Access( or other DBMS).
Spend the time to confirm WHAT before jumping too quickly into HOW.

Good luck.

 

[plog]

the RefNo would be very instrumental in the entire DB

Incorrect. The RefNo is purposeless, especially to the database. It exists because you want to put it on a piece of paper or have humans refer to it or because of some business rule. The database can produce it, but it has no function within the database.

If you need a way to link tables you use an autonumber primary key. You have that in this table (QuoteID). When you want to link records between that table and another, you store the QuoteID in that other table.

At this point I suggest you read up on normalization and a few database tutorials. Especially primary and foreign keys. Again, your RefNo is worthless to the database.

 

[Mwaki]

You're right @jdraw.
I am rushing this. I will take some time and do what you've recommended. I need to get it right.
I'll be back if I get stuck again. You guys are really helpful!

 

[jdraw]

In the link I provided on Database Planning and Design, spend some time with the tutorials from RogersAccessLibrary. Work through 1 or 2 (about an hour), then write a clear description of your needs and build a model based on what you learned during your working through the tutorial(s).
You have to do the work to appreciate the process--But you will learn and what you learn can be used with any database.
Good luck.

 

[The_Doc_Man]

3. Reference number goes away. It will not exist in a table, it will exist as a calculation wherever you need it to. Most likely in a query, where you will then use it in reports.

Just to be absolutely clear: This statement, though probably true for your situation, in fact could be true or false DEPENDING ON INTENT! Since you have another way to link dependent table data to the independent table (or, if you prefer, child to parent linkages), it is probably true that you don't need it.

I'll spare you the full writeup I usually give for people in the planning stage. However, I will add to the consensus that right NOW is the most important part of your process - designing and THINKING about the design. Take good notes. Make drawings of data flow. Whatever works for you to help you think about something and REMEMBER your intentions, do that. Build yourself a road map (logically speaking) of where you want to go and what you want to do.

This might sound like a joke, but in a way it is not: Treat your project like you were planning a really great multi-stop vacation with your favorite partner. Plan it out to know what you will see and how you will get there. Almost like a vacation triptych that you might get from a commercial travel service. The idea is simple: If you don't have a good road map, (a) how will you get where you want to go? and (b) how will you be sure you got there?

Nicklaus Wirth, the "father" of the PASCAL programming language, was once quoted as saying that 80% of all programming problems originate from bad data layout. So spend some time up front to be sure your data is laid out well. And good luck with your project.

 

[jdraw]

Well said , Doc!

 

[Pat Hartman]

I can't tell whether or not you are including the date in the custom number so just in case, I would make it yymm rather than mmyy because the sorting will make more sense. you will see items in chronological sequence rather than all the January's for all years followed by all the February's for all years, etc.

 

[arnelgp]

you need a Form and a Function in a Standard
Module to create the reference number.

For the form, try testing it in Datasheet form first.
copy this code to a New Module:

Public Function fncNewReference() As String
''
'' arnelgp
''
'' NOTE:
''
'' Change the TABLE_NAME and FIELD_NAME
'' to the correct fieldname and table
'' name that you want to Auto generate
'' the reference number
''
    Const TABLE_NAME As String = "table2"
    Const FIELD_NAME As String = "Reference Number"
    Dim Result As Variant
    fncNewReference = "RN-" & Format(Date, "mmyy") & "-0001"
    Result = DMax("[" & FIELD_NAME & "]", _
                  "[" & TABLE_NAME & "]", _
                  "MID([" & FIELD_NAME & "],4,4)=" & Format(Date, "mmyy"))
    If Not IsNull(Result) Then
        Result = Split(Result, "-")
        Result(UBound(Result)) = Format(Val(Result(UBound(Result))) + 1, "0000")
        fncNewReference = Join(Result, "-")
    End If
End Function

On the Form's BeforeInsert Event, add this code:

Private Sub Form_BeforeInsert(Cancel As Integer)
    Me.theReferenceNumberTextBoxName = fncNewReference()
End Sub

 

[Cronk]

Back in the old days before computers, reference were given to items to make it easier to retrieve something. That is, recording the year/month in the reference number made it easier to locate in which folder the copy was held, or a rack/bin number to locate a stock item, or whatever.


With computers, this is not necessary because all relevant information is retrieved as soon as a digital reference is inputted.