By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,315 Members | 2,150 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,315 IT Pros & Developers. It's quick & easy.

I Need A Help With Auto Generating String (Access 2003)

P: n/a

I need some constructive input to be able to finish my program.

I have a field called (1) DateRec, another (2) Municipality and another
(3) Type. These three fields invariably will automatically generate a
string of number for another field that is called FileNumber. This
FileNumber is already the Primary Key in the program. The (1) is the
date received, that is the date the application of the client was
received. (2) The Municipality field will be programmatically structured
to produce one or two letter characters that will represent each
Municipality in the FileNumber for every application within the County
where the applicant is from. Right now in the program, all the 23
Municipalities are listed in a combo box with their full names. And (3)
which is the Type is the state of the application, it will only be
involved in the FileNumber, when it is an "EXEMPT", then, the
nomenclature will be; an 'E'.

Here comes the drill! When the user enters the date received, it will be
a complete date, with day, month and year. The only thing that will go
to the FileNumber will be the year, like 2006. When a Municipality is
chosen by the user, for instance Atlantic City, it will generate a
nomenclature like 'AC', if it is for Brigantine it will be a 'B'. Each
Municipality will have a unique identifier of one or two characters
only. And there are twenty-three Municipalities in all. Then the
program will run a query that will check for the last FileNumber under
that Municipality the user has chosen and update the middle number with
one number increment. So the FileNumber will be something like AC-2-2006
or B-1-2006, for another entry on this same Municipality it will be
AC-3-2006 or B-2-2006, AC-4-2006 or B-3-2006 etc. Hence, the middle
number will be like a counter that will help the user to know the number
of applications for each municipality within a certain year. Then, when
there is an Exemption in the application, the user will select EXEMPT
from Type, it will emerge the 'E' with the middle number; like
AC-5E-2006. For this to work the program will enforce the user to enter
Date received, and the Municipality and check for Type before he exit,
the program. This is all that is needed.

I definitely, need a help to get this working. The purpose for this is
to debar the user from entering wrong File numbers.

Thanks in anticipation for your help.

Anthony


*** Sent via Developersdex http://www.developersdex.com ***
Feb 14 '06 #1
Share this Question
Share on Google+
1 Reply


P: n/a
To make it easier for yourself, create fields for each of the parts
of String

Year, n, Code

You can get year from date like this Year(date)

You can get n from the table like this:
1 + Dlookup("n","table","Year = " & year)

I don't know where you will get municpility codes from.

You can combine them like this:

Year & n & Code

(david)

"Anthony Esochaghi" <ze****@hotmail.com> wrote in message
news:R1*************@news.uswest.net...

I need some constructive input to be able to finish my program.

I have a field called (1) DateRec, another (2) Municipality and another
(3) Type. These three fields invariably will automatically generate a
string of number for another field that is called FileNumber. This
FileNumber is already the Primary Key in the program. The (1) is the
date received, that is the date the application of the client was
received. (2) The Municipality field will be programmatically structured
to produce one or two letter characters that will represent each
Municipality in the FileNumber for every application within the County
where the applicant is from. Right now in the program, all the 23
Municipalities are listed in a combo box with their full names. And (3)
which is the Type is the state of the application, it will only be
involved in the FileNumber, when it is an "EXEMPT", then, the
nomenclature will be; an 'E'.

Here comes the drill! When the user enters the date received, it will be
a complete date, with day, month and year. The only thing that will go
to the FileNumber will be the year, like 2006. When a Municipality is
chosen by the user, for instance Atlantic City, it will generate a
nomenclature like 'AC', if it is for Brigantine it will be a 'B'. Each
Municipality will have a unique identifier of one or two characters
only. And there are twenty-three Municipalities in all. Then the
program will run a query that will check for the last FileNumber under
that Municipality the user has chosen and update the middle number with
one number increment. So the FileNumber will be something like AC-2-2006
or B-1-2006, for another entry on this same Municipality it will be
AC-3-2006 or B-2-2006, AC-4-2006 or B-3-2006 etc. Hence, the middle
number will be like a counter that will help the user to know the number
of applications for each municipality within a certain year. Then, when
there is an Exemption in the application, the user will select EXEMPT
from Type, it will emerge the 'E' with the middle number; like
AC-5E-2006. For this to work the program will enforce the user to enter
Date received, and the Municipality and check for Type before he exit,
the program. This is all that is needed.

I definitely, need a help to get this working. The purpose for this is
to debar the user from entering wrong File numbers.

Thanks in anticipation for your help.

Anthony


*** Sent via Developersdex http://www.developersdex.com ***

Feb 15 '06 #2

This discussion thread is closed

Replies have been disabled for this discussion.