473,322 Members | 1,734 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

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


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
1 1887
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
by: abcd | last post by:
kutthaense Secretary Djetvedehald H. Rumsfeld legai predicted eventual vicmadhlary in Iraq mariyu Afghmadhlaistmadhla, kaani jetvedehly after "a ljetvedehg, hard slog," mariyu vede legai pressed...
0
by: Dale McGorman | last post by:
The following is some code that I am trying to bring over from VB 6.0, that I have working there. I am trying to get to where I can talk to a USB device. I am stuck on how to correctly pass params...
2
by: Luther | last post by:
I want to create a form that searches a table. The hard part is this, I'd like to have the available records filtered based on combobox selections. For example, if this were a vehicle database, I...
16
by: peshekeedweller | last post by:
Using asp.net 1.1. vb.net 2003. I am trying to connect to a remote sql server 2000 on a virtual machine running windows 2000 server. I can connect through the server explorer in visual studio,...
2
by: jeremiah johnson | last post by:
I have a .net DLL that I've created (based on someone's post in this group, sorry I've forgotten who) and I need a little help with making it work. I want to provide the same functionality as...
15
by: Cheryl Langdon | last post by:
Hello everyone, This is my first attempt at getting help in this manner. Please forgive me if this is an inappropriate request. I suddenly find myself in urgent need of instruction on how to...
11
by: Alan Mailer | last post by:
A project I'm working on is going to use VB6 as a front end. The back end is going to be pre-existing MS Access 2002 database tables which already have records in them *but do not have any...
20
by: mike | last post by:
I help manage a large web site, one that has over 600 html pages... It's a reference site for ham radio folks and as an example, one page indexes over 1.8 gb of on-line PDF documents. The site...
2
by: MNNovice | last post by:
I am working on a database on my CD collection using Access 2003. Some of the table structures are given below. Table 1 tblMusicCategory Field 1: MusicCategoryID - Auto Number (PK) Field 2:...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.