Solved

Increasing a number function problem

Posted on 2012-03-09
12
396 Views
Last Modified: 2012-03-13
Hi EE

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
CI00001
RD00001
ST00001
I am using the following function

rsjob.Edit
 rsjob.Fields("ProjectNumber") = left(Tdemo, 4) & Format(Val(right(JobcardNumber, 5)) + 1, "00000")
 rsjob.Update
 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

chestera
0
Comment
Question by:chestera
  • 7
  • 4
12 Comments
 
LVL 11

Accepted Solution

by:
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

0
 
LVL 57
ID: 37703503
Chestera,

  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.

Jim.
0
 

Author Comment

by:chestera
ID: 37703631
jdetman

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

Alan
0
 

Author Comment

by:chestera
ID: 37703639
techhealth

Of course. I missed that

Alan
0
 

Author Closing Comment

by:chestera
ID: 37703663
techhealth

Yep it worked. Thank you

Alan
0
 

Author Comment

by:chestera
ID: 37703667
jdetman

Hi Jim

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

Alan
0
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 57
ID: 37704945
Alan,

  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:

 ON0001

  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.  

Jim.
0
 

Author Comment

by:chestera
ID: 37705653
jdetman

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

Alan
0
 
LVL 57
ID: 37709495
Alan

  Not sure what you have both.

Jim.
0
 

Author Comment

by:chestera
ID: 37711843
jDetman

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
Field1
Field2
Field3
etc

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

Alan
0
 
LVL 57
ID: 37714137
Alan,

<<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.

Jim.
0
 

Author Comment

by:chestera
ID: 37717222
jDetman

Thanks Jim now get your meaning. many thanks

Alan
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Join & Write a Comment

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

760 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now