Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Incremental Numbering for clients & Year

Posted on 2013-12-29
6
278 Views
Last Modified: 2013-12-29
I need to create an incremental numbering system based on a client ID and the year. Right now I have a numbering system that works well and increments based on the client that is logged in. It numbers the client records sequentially all starting from 0001. But when the year changes I need that numbering system to start back at 0001. Here's what I currently have that is working fine but now I need to somehow change it to start over when the new year starts

Dim i, a
 i = Me.cboClinicName
Me.txtCRecordID = [txtClinicCode] & Format(Date(),"yy") & Nz(DMax("CRecordID", "tblCogginsDetail", "ClinicName = " & i), 0) + 1

Open in new window


So here's how it looks now for each record

When the clinic name is NEHL or KOS for example:

NEHL130001
NEHL130002
KOS130001
KOS130002
KOS130003

So now what I need to do is restart the last 4 digits of the above sequence  when the year changes. For example:

NEHL130001
NEHL130002
NEHL140001

etc...

How can I rewrite my code to restart this when the year changes?
0
Comment
Question by:Lawrence Salvucci
  • 3
  • 3
6 Comments
 
LVL 40

Accepted Solution

by:
als315 earned 500 total points
ID: 39744927
Try this code:
Dim a As String, b As String
a = Me.[txtClinicCode] & Format(Date, "yy")
b = Nz(DMax("CRecordID", "tblCogginsDetail", "instr(1,CRecordID," & Chr(34) & a & Chr(34) & ") = 1"))
If b = "" Then
    b = a & "0001"
Else
    b = a & Format((Mid(b, Len(a) + 1) + 1), "0000")
End If
Me.txtCRecordID = b

Open in new window

0
 
LVL 1

Author Comment

by:Lawrence Salvucci
ID: 39744935
It's not incrementing the last 4 digits of the sequence. It's just putting the same values in the field "NEHL130001".
0
 
LVL 40

Expert Comment

by:als315
ID: 39744952
Can you upload sample DB with this table only and form?
0
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 
LVL 1

Author Comment

by:Lawrence Salvucci
ID: 39744958
That's gonna take a little time. Give me a few and I'll upload a copy of it.
0
 
LVL 40

Expert Comment

by:als315
ID: 39744961
Look at my sample
DBincrement.accdb
0
 
LVL 1

Author Closing Comment

by:Lawrence Salvucci
ID: 39744970
Got it working. I had the wrong field name in your code. Thank you for your help. I appreciate it!
0

Featured Post

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

791 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question