Numerically incrementing a string

[SDL20]

How do I incement a numeric portion in Access 2003? The field I want to
numerically
increment looks like this:

Emp-1
Emp-2
....
....
Emp-n

What would be the code to do this?
--SDL20

[MGFoster]

SDL20 wrote:

How do I incement a numeric portion in Access 2003? The field I want to
numerically
increment looks like this:

Emp-1
Emp-2
...
...
Emp-n

What would be the code to do this?

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

for i = 1 to n
debug.print "Emp-" & i
next i

You might think about arrays:

const n = 25
dim strEnum(1 to n)

for i = 1 to n
strEnum(i) = "Emp-" & i
next i

--
MGFoster:::mgf0 0 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBRAOgloechKq OuFEgEQLLKQCg+y gyKB7joqS8Oxmho Vw8jGg5RkYAnRFV
iYlotTDUnr2CR4Q a1cVZ6lUB
=/lLH
-----END PGP SIGNATURE-----

[Bob Quintal]

"SDL20" <dy*******@gmai l.com> wrote in
news:11******** **************@ p10g2000cwp.goo glegroups.com:

How do I incement a numeric portion in Access 2003? The field
I want to numerically
increment looks like this:

Emp-1
Emp-2
...
...
Emp-n

What would be the code to do this?
--SDL20

It depends on where you want to use it, Is it in a query, in a
textbox , in the default value of a form?

First you need to separate the alphatic portion from the number.
Then you need to convert the numeric portion to an integer, then
you need to add one to that number. Then you need to format the
numeric part back to a string, and concatenate it to the
previously saved string portion.

For your simple example left(EMP_ID,4) & cint(mid(EMP_ID ,5)+1
This has no formatting nor error checking.
However, this will change the existing Employee ID, You didn't
specify, but I'll assume you want to
a) find the last created value of emp_id,
b) create a new record, and use the incremented emp_id for the
new record.

Code for that is more than you asked for.

[SDL20]

Right. It is a text box in a form. Each time I create a new record I
want the Employee ID ("Emp-n") to increase by one. I can't figure out
how to seperate the string, increase the integer, and then concatenate
it. Sorry, but I'm new to this.

Thanks for your help.

[MGFoster]

SDL20 wrote:

Right. It is a text box in a form. Each time I create a new record I
want the Employee ID ("Emp-n") to increase by one. I can't figure out
how to seperate the string, increase the integer, and then concatenate
it. Sorry, but I'm new to this.

What you're doing is complicated though it looks easy. You really
should be using an AutoNumber for the Employee ID if you just want to
have consecutive numbers. But, that has it pitfalls also - the numbers
can skip when a user decides to cancel a new record.

Ideally, you'd have a function that safely increments the employee ID
w/o interference from other users trying to add new employees. Search
Google groups (this group) for examples of how to do that (this subject
has been hashed over many times in this NG). The Access Developer's
Handbook has an example of how to do this.
--
MGFoster:::mgf0 0 <at> earthlink <decimal-point> net
Oakland, CA (USA)

[Bob Quintal]

"SDL20" <dy*******@gmai l.com> wrote in
news:11******** *************@i 40g2000cwc.goog legroups.com:

Right. It is a text box in a form. Each time I create a new
record I want the Employee ID ("Emp-n") to increase by one. I
can't figure out how to seperate the string, increase the
integer, and then concatenate it. Sorry, but I'm new to this.

Thanks for your help.

It's obvious you are new. If "Emp-" is a constant and will be
identical for each employee ID, you do not need (and a good
database designer would not want) to store that in the table,
because it wastes disk space, it complicates many operations,
and is absolutely easy to generate at the time you prepare a
report.

empid = "Emp-"+[employeeID] where [employeeID] is the fieldname
in the table for the number.

You would only need to store the "Emp-" part if it's subject to
change, like "XYZBoxesEm p-" "Firm2-" for different records.
Even then most database programmers would store that part as
text separately from the numeric portion and concatenate them
when needed [EMployeePrefix]+[EMployeeID]....

Now you can just store the number part, which makes the whole
thing a lot easier. as M.G. Foster said, you can define the
numeric part as an autonumber, and Access will always put a
unique, ascending but not necessarily consecutive number when
you add a new line

You could also write some visual basic code that looks up that
highest number already assigned and adds one to that, but that's
probably more complex than you need.

Anyways, to split a string you use the mid() function, to add
one you use the add operator +, and to concatenate two strings
you can use the + operator, or the & operator. the two
concatenation operators behave differently in the presence of
null values, but you don't need to worry about that for now.

As to returning the existing the maximum existing value, there
are several ways to go about this, the simplest is the dmax()
function, but as M.G. said that still doesn't guarantee
consecutive numbers.

Use the Help file F1 key to get info about the syntax for the
functions.

--
Bob Quintal

PA is y I've altered my email address.