[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 404
  • Last Modified:

Increasing a number function problem

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
chestera
Asked:
chestera
  • 7
  • 4
1 Solution
 
techhealthCommented:
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
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
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
 
chesteraAuthor Commented:
jdetman

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

Alan
0
Independent Software Vendors: 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!

 
chesteraAuthor Commented:
techhealth

Of course. I missed that

Alan
0
 
chesteraAuthor Commented:
techhealth

Yep it worked. Thank you

Alan
0
 
chesteraAuthor Commented:
jdetman

Hi Jim

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

Alan
0
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
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
 
chesteraAuthor Commented:
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
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
Alan

  Not sure what you have both.

Jim.
0
 
chesteraAuthor Commented:
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
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
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
 
chesteraAuthor Commented:
jDetman

Thanks Jim now get your meaning. many thanks

Alan
0

Featured Post

Technology Partners: 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!

  • 7
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now