Increasing a number function problem

Posted on 2012-03-09
Last Modified: 2012-03-13

I am trying to increase a numbe by 1 each time it's used this is the type of number
DEMO00001. It will always have 5 digits sample of other numbers are
I am using the following function

 rsjob.Fields("ProjectNumber") = left(Tdemo, 4) & Format(Val(right(JobcardNumber, 5)) + 1, "00000")
 in this example Tdeme = "DEMO" for the other characters I use Left(Ttype,2)
Type can be CI,RD or ST. before running the function I check to see it it's DEMO or CI,RD or ST
 If for example the number is DEMO00001 it is saved as DEMO00001 not DEMO00002

Any help appreciated

Question by:chestera
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
  • 7
  • 4
LVL 11

Accepted Solution

techhealth earned 500 total points
ID: 37703447
so when JobcardNumber=DEMO00001, you get ProjectNumber=DEMO00001?  That's unlikely. Or are you trying to increase ProjectNumber for each run?  Then it really should be

rsjob.Fields("ProjectNumber") = left(Tdemo, 4) & Format(Val(right(rsjob.Fields("ProjectNumber"), 5)) + 1, "00000")

Open in new window

LVL 57
ID: 37703503

  It's generally a bad idea to build any type of meaning into a key. For example, is Tdeme another field in the table?  If so, then it could be changed in the future.

 Then you might have a CI in the record for Tdeme, and a ID of DEMO00001.

 I would avoid doing this.


Author Comment

ID: 37703631

The field Project number contains the completenumber example DEMO00001. Is there a better way of doing this

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!


Author Comment

ID: 37703639

Of course. I missed that


Author Closing Comment

ID: 37703663

Yep it worked. Thank you


Author Comment

ID: 37703667

Hi Jim

I have accepted techhealth solution because he did answer my question. But I will take your suggestion on board

LVL 57
ID: 37704945

  I don't know the full detals of what it is your attempting to do, but in general, a key will be one of two things:

1. A "natural" key - meaning it's dervied from the data itself.

2. A meaningless key - has no bearing to the data.

  I gave you an example above of the problem that can occur when you build meaning into a key and treat it as a value.

  I'll give you another:  

  At the end of your year, the last Demo key reads DEMO0103.    Does that mean you have had 103 demo projects?  Maybe, maybe not.  What if someone deleted one?   Put a project in the wrong category?

  The proper way to set this up would be to have a field Project Type and actually count the records that exist within a time frame.  You don't want to rely on the key for any kind of data.  It should only be a way of finding data, not data in of itself. Many people like to use this setup, but it always leads to problems down the road.

  Another example, someone decides that AR customers will be assigned the first two letters of the company name followed by a numeric sequence.   So Online Computers becomes:


  Everything in the system is setup to support sorting on the key (reports, lookups, etc).   Now five years latter, the company name changes to Always Online Computers.  Oops.

  The problem is that the key contains data which is duplicated because it exists else where in the record (company name) and it should not.

 Do yourself a favor, add field(s) as required to capture all the data and either use a natural key or a meaningless key (autonumber) and forget about the ID field as you have it.  


Author Comment

ID: 37705653

Hi Jim

Your feed back much appreciated. I haver a table called tblProjectnumbers
with two fields
ID            Projectnumber
   1             CI00001
  2              RD00001
  3             DEMO00001
4               ST00001

A Project is allowcated one of the above once used it needs to be updated ready for the next project

Project 1 could have used  DEMO00001 the next Project2 could have used CI00001
so now the table will show

1          CI00002
2          RD00001
3          DEMO00002
4          ST00001

All my tables have a key field. I think I had a mental block when I saw your first responce

ID is usually autogenterated

LVL 57
ID: 37709495

  Not sure what you have both.


Author Comment

ID: 37711843

Hi Jim

I have a table tblProjectnumbers containing CI00001, Demo00001, RD00001 and ST00001 which gets updated with each new Project ie CI00002 etc
and another table called tblProjects
tblProjects structure
ProjectID        Key field autogenerated
Projectnumber   which can contain one of the numbers from tblProjectnumbers

Eact time we add a new project it is allocated a number from the tblProjectnumbers table

LVL 57
ID: 37714137

<<Eact time we add a new project it is allocated a number from the tblProjectnumbers table>>

  Understand that, but your missing the point a bit.

<<CI00001, Demo00001, RD00001 and ST00001 >>

  You've built meaning into the project ID.  "CI", "Demo", "RD", and "ST" are a fact about the project (type of) and should not be part of the ID# itself.    Their all projects, just different types and there's no need to have both a project ID and a ID in the table.

  Maybe this will make it a bit clearer...

 If the project types were substantially different (a different list of facts (fields) about each), then they would need to be in different tables. Each table would represent the different project types and have it's own series of numbers.

 However since you have them all in a single table, what you've said by doing that is that they are all basically the same and yet you are generating ID's that are not.


Author Comment

ID: 37717222

Thanks Jim now get your meaning. many thanks


Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA.…

742 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