3 probs...

Hi Everyone!

I have 3 problems. Please try to help if you can! (I am newbee)

1. I am making a database and of course before I will finish it I am testing it. The problem is that the Customer ID (which is set to primary key and it is autonumber) is beginnig with 0004 (because of testing with records) and I would like the finished database to begin with 0001. So how could I reset this record to begin wiht 0001??

2. I try to make the Customer ID (which is also the primary key and it is set to autonumber) to look like this 0001, 0002, 003,.. 0009, 0010,0011… 0100, 0101.. and so on.
On the format of the Customer ID I wrote this:”>> 0000” (without quotations)
My problem is that this autonumber should work this way: 0008, 0009, 0010, 0011.
But this way unfortunatelly it works this way: 0008, 0009, 00010, 00011. I hope you understand my problems. What’s wrong??

3. I have another database where the Customer Id (also primary key) should be 4digits_year_mont of registration. (Example: 0001-04-01; where 0001 stands for the code/number/id of customer, 04 stands for 2004 and 01 stands for January. What I would like is that the code/number/id section should be increasing automatically and the other part of the ID (year and month) should be changing (manually or automatically) with the dates.
Example: if today is 2004 february of 14th and this is my 214th customer the Customer Id (automatically) should be set: 0214-04-02. Can this be done with ms access or not??

Thanx a lot in advance and have a nice weekend!!

1. By compacting the database (available from Tool menu database compact) you can reset the autonumber.

2. If you want your own format for primary key (like 4 or 5 digits) Use just number (long) format and increment the primary key by coding which is more flexible.

3. You can increment a part of the value by 1 by using substring equivalent command Mid()

LastNo = "2001-05-300"
NextNO = Mid(LastNo,1,8) & (Mid(Lastno,9,3)+1)

I would do number two different from supersuba
I would keep it as auto number and just reformat the number when displaying it with the following code

Format([auto],"0000")

This will be easier for you.


AS far as number three I would just personnal go about it different. I would treat each seperate part of you customer number field as seperate indivdual fields that would be a three part, concatenated Primary Key.

This would give me the flexibility to hadle each field seperately and would simplying the code invlolved in making the system do what I need it to do.

There really isn't right or worng answers as much as what is you preference.

S_

Hi sbaxter!!

Thanx for your reply!

The only problem is that I really dont know where should I paste the: Format([auto],"0000") code!

Please tell me!

Thanx!

How do you want to use it? In query, in code, in your textbox (on a bound or unbound form)?

S-

I guess I would use it in my textbox.... since I cant code!
thanx