Autonumber problem

[nishant.dhruve]

i have attached a picture and a code that i m using for access.


so when i press a command button its generates a number having a format
EU17/0000

But i m having problem, instead of increment of the number in EU17/0001, EU17/0002,
It being increasing like EU17/0000, EU17/EU17/0000, EU17/EU17/EU17/0000......

Can someone helo me on this to correct the increment please
[Code]
Private Sub btnmakeID_Click()


vID = Nz(DMax("[ID]", "SapRequest"))


ID = "EU" & Right(Date, 2) & "/" & Format(vID, "0000")




End Sub
[Code]

[Forbes]

Does it work when you put it in like so "0001/EU17"

[CJ_London]

try running your code in steps. You have a text ID which cannot be formatted (formatting only applies to numbers) so you are getting

vid=max....="EU17/0000"

formatting vid (EU17/0000) returns vid - EU17/0000

ID = "EU" & Right(Date, 2) & "/" & Format(vID, "0000") =EU17/EU17/0000

so you need to extract the last 4 characters from vid, convert it to a number and then format it again

You would be much better keeping this as 3 separate fields - but I guess you've probably been told that in the past

[nishant.dhruve]

no its doesn't work in that way too

[nishant.dhruve]

i didnt get you quite well.
Would be able to help by modifying the code the way you are telling to me.
it would be more easy to understand

[CJ_London]

If you are referring to me then no, not really. I don't believe in providing solutions that are not the right way to do things. You know how to use the right string function and the format function. The other one you will need is the val function

[rpeare]

This is the wrong way to approach this as ajax has said. You should use an autonumber as your primary key. You then have two options:

1. set the display format of the autonumber field to be "EU17/" & AutonumberfieldName any time you need to display it. This may give you non sequential numbers
2. Keep your autonumber primary key field but make this other field a non primary key index field.

The syntax you want is

dim sMaxID as string
dim sNewMaxID as string

sMaxID = dmax("[ID]", "SAPRequests")
snewmaxid = left(smaxid, 5) & right("0000" & cint(right(smaxid, len(smaxid) - 5))

just bear in mind this will bomb out after you hit record 9999

[CJ_London]

the other problem you will have with the dmax is if you have other prefixes to EU

and as an autonumber - if this is indexed and/or is your primary key, the indexes are over larger and so slower to search - a long number takes 4 bytes - your text takes 13 - so over 3 times slower.

Your technique is confusing presentation with data storage - an Excel 'style' which combines presentation and storage into one view - which is consequently not normalised and inefficient from a data storage and retrieval perspective.