Access Access 97 How do I get a value of 4 to appear as 0004

[Steve Ferry]

I'm relatively new to access, reckon I'm at about novice level, but I'm trying to get an example Job Database together for my work. As I'm doing this of my own back you can imagine I have little or no resources.

What I m trying to do is get a sequencial numering system based on job types, mechanical & eletrical, as follows using a form.

ME0001, EL0001, EL0002, EL003, ME002

So essentially inputting mechanical & electrical jobs whilst maintaing sequencial numering for each job type.

I have a partial success of the database doing the following

ME1, EL1, EL2, EL3, ME2 - but as you can see the 000's are missing.

I'm really struggling with this having tried setting input masks and Format types without little success, mainly due to my lack of experience.

Any assistance would be grateful

Thanks

Steve Ferry.

 

[Me__2001]

do you have an imput mask ? and what format type are you using at the moment ?

 

[cvo-aalst]

I'm relatively new to access, reckon I'm at about novice level, but I'm trying to get an example Job Database together for my work. As I'm doing this of my own back you can imagine I have little or no resources.

What I m trying to do is get a sequencial numering system based on job types, mechanical & eletrical, as follows using a form.

ME0001, EL0001, EL0002, EL003, ME002

So essentially inputting mechanical & electrical jobs whilst maintaing sequencial numering for each job type.

I have a partial success of the database doing the following

ME1, EL1, EL2, EL3, ME2 - but as you can see the 000's are missing.

I'm really struggling with this having tried setting input masks and Format types without little success, mainly due to my lack of experience.

Any assistance would be grateful

Thanks

Steve Ferry.

Dear Steve

If I understand it well the numbers must raise automatically for every unique character code.
When the last code ME0005 is then must the next code automatically finish with a 6 (ME0006).

A solution could be to wirte a Function with Acces basic or VBA:

• place a secound textbox on the form an gif it the following name: txtLetterCode
• then write a function which get the last number automatically by code (SQL)
• with te function numCode = right$(string,len(sting)-2) you get the the numbers
• then convert it to a numbervalue with val function nextNumber = Val(numCode)
• add 1 to the number -> nextNumber = nextNumber +1
• build the exact 4 position code
  select case nextNumber
  case 1 to 9
  REM - This code add's 3 zero's in front of the number
  numCode = string("0",3) & Cstr(nextNumber)
  case 10 to 99
  numCode = string("0",2) & Cstr(nextNumber)
  case 100 to 999
  numCode = string("0",1) & Cstr(nextNumber)
  end select

The function is called when the user leave the textbox txtLetterCode.

I have no Access 97 any more installed on my PC, but if you want I can give you an example for access 2000 or higher in VBA.

----------------------------------
Greetings from Belgium
Groetjes vanuit België

ps: Sorry, for possible bad English, I'm Dutch-speaking

 

[Steve Ferry]

CVO, thanks.

some VB code would be useful I will be able to view it on a friends machine and see how it translates into Access97.

I'm not big on VB even less so with SQL but I'll try what you have said.

Sorry for the delay getting back I had a long weekend & with the great weather there was no way I was sitting indoors.

PS your english is fine.

Thanks again

ME2001 I've tried all format types and and input masks without little success, if you can suggest anything great.

Steve Ferry

 

[abaxt]

Don't know if this is what your looking for

I did something similar in a database at work under the same kind of constraints as I'm not a programmer etc, but in a table I set up a field with autonumber as the datatype and then in the box at the bottom if you type in where it says format for that field,

"ME"000

then the autonumber adds one digit on as each record is entered and appears as,
ME001
ME002
ME003

Forgive me if this is not what you are looking for or if you have already solved this.
Regards
Angie

 

[cvo-aalst]

CVO, thanks.

some VB code would be useful I will be able to view it on a friends machine and see how it translates into Access97.

I'm not big on VB even less so with SQL but I'll try what you have said.

Sorry for the delay getting back I had a long weekend & with the great weather there was no way I was sitting indoors.

PS your english is fine.

Thanks again

ME2001 I've tried all format types and and input masks without little success, if you can suggest anything great.

Steve Ferry

Steve,

I have make a example for you and add it as a attachment.

It's convert from Access 2002 to Access 97, I hope it's works.

 

[charlyw]

You can use the Access built-in function Format as follows:

"M" & Format([CounterField],"0000") or
"E" & Format([CounterField],"0000")

Hope this helps
Charlyw