Combining Data from multiple fields

[Gil]

Is there a way to get access 2007 to automatically input the ordinal date for
a given field and combine it with other data?
Here is my situation, I created a Dbase where the “Local Service Request
Number†[LSR] has to be inputed manually because I couldn’t figure out how to
make Access do this automatically, but users are making a lot of mistakes.
The LSR number looks like this; 13/8258-E1115.
Here is the breakdown;
13/ has to be at the beginning of every LSR number, so those characters
nerve change.
8 Fiscal year, so that will change to a “9†on 1 October 2008.
258 the ordinal date, which 258= 15 Sep, 259= 16 Sep… etc.
-E never change, dash “E†must come after the ordinal date.
1115 is the sequence number, which began with 0001, which boils down to the
number of service tickets.
Here is what I would like to do, have access generate the ordinal date based
on the PC date, and put an 8 in front of the ordinal date. I was able to
figured out how to automatically input the sequence number by making it the
primary key, data type = AutoNumber, Field size = Long Integer, New Values =
Increment, Format = 0000, Indexed = Yes (No Duplicates), which generates
numbers beginning with 0001 and increments for every new service ticket. I
would like the user to be able to see the combine data in one cell on the
input form and I would like to generate reports with the combine data.
Any help would be greatly appreciated.

 

[Steve Schapel]

Gil,

You have a number of different "pieces" of information here, as
represented by your "breakdown". These should not be combined into a
single field.

I can certainly understand you want the LSR to be *displayed* in the
prescribed format, according to your business rules. But how it is
displayed, and how it is stored, and how it is entered, are 3 different
things.

The "13/" and the "E-" do not need to be recorded in the database at
all, if they will never change.

Using an Autonumber for the "1115" is ok, so long as you don't care if
there may be occasional gaps in the sequence of the numbers. Otherwise
I would recommend another approach (see below).

And the rest of it i.e. "8258" is derived from or calculated from the
date. So the only data that needs entering is the date. And if I
understand you correctly, even this does not need entering as you will
be using the current date when the record is entered.

So I would do it like this:
1. Have a field for the RecordDate. On the form, set the Default Value
of the applicable textbox to:
Date()
2. Have a Number field for the SequenceNumber. On the form, set the
Default Value of the applicable textbox to:
DMax("[SequenceNumber]","NameOfYourTable")+1
3. Whenever you need to see the LSR on a form or report, use an
expression to display it, either in a calculated field in the query that
the form or report is based on, or in the Control Source of an unbound
textbox on the form or report itself. Something like this:
="13/" & Val(Format([RecordDate],"yy"))-(Month([RecordDate])>9) &
DatePart("y",[RecordDate]) & "-E" & Format([SequenceNumber],"0000")

So the users don't have to enter anything, so therefore no more
mistakes!

If that expression seems a bit cumbersome, remember that you would just
need to copy/paste it a few times probably. You could make a
user-defined function to put it together if you wanted, so your work in
queries and reports would be neater.

--
Steve Schapel, Microsoft Access MVP

Is there a way to get access 2007 to automatically input the ordinal date for
a given field and combine it with other data?
Here is my situation, I created a Dbase where the “Local Service Request
Number” [LSR] has to be inputed manually because I couldn’t figure out how to
make Access do this automatically, but users are making a lot of mistakes.
The LSR number looks like this; 13/8258-E1115.
Here is the breakdown;
13/ has to be at the beginning of every LSR number, so those characters
nerve change.
8 Fiscal year, so that will change to a “9” on 1 October 2008.
258 the ordinal date, which 258= 15 Sep, 259= 16 Sep… etc.
-E never change, dash “E” must come after the ordinal date.
1115 is the sequence number, which began with 0001, which boils down to the
number of service tickets.
Here is what I would like to do, have access generate the ordinal date based
on the PC date, and put an 8 in front of the ordinal date. I was able to
figured out how to automatically input the sequence number by making it the
primary key, data type = AutoNumber, Field size = Long Integer, New Values =
Increment, Format = 0000, Indexed = Yes (No Duplicates), which generates
numbers beginning with 0001 and increments for every new service ticket. I
would like the user to be able to see the combine data in one cell on the
input form and I would like to generate reports with the combine data.
Any help would be greatly appreciated.

 

[Gil]

Steve, your example got the ball rolling and I was able to successfully
combine the fields. I didn't do it exactly how you suggested, but your
suggestions gave me a better understanding of what I was trying to do.
Thanks.

Here is what I entered into the Control Source of an unbound textbox: ="13/"
& ([OrdinalDate]) & "-E" & ([SequenceNumber]), which returns 13/8011-E0001.

The uses only has to enter the ordinal date, in this case "8011", and
everything else auto fills. I'll eventually figure out a way to get Access
to automatically input the ordinal date, but for now, it's working fine.
Thanks for your help.

--

Gil

Gil,

You have a number of different "pieces" of information here, as
represented by your "breakdown". These should not be combined into a
single field.

I can certainly understand you want the LSR to be *displayed* in the
prescribed format, according to your business rules. But how it is
displayed, and how it is stored, and how it is entered, are 3 different
things.

The "13/" and the "E-" do not need to be recorded in the database at
all, if they will never change.

Using an Autonumber for the "1115" is ok, so long as you don't care if
there may be occasional gaps in the sequence of the numbers. Otherwise
I would recommend another approach (see below).

And the rest of it i.e. "8258" is derived from or calculated from the
date. So the only data that needs entering is the date. And if I
understand you correctly, even this does not need entering as you will
be using the current date when the record is entered.

So I would do it like this:
1. Have a field for the RecordDate. On the form, set the Default Value
of the applicable textbox to:
Date()
2. Have a Number field for the SequenceNumber. On the form, set the
Default Value of the applicable textbox to:
DMax("[SequenceNumber]","NameOfYourTable")+1
3. Whenever you need to see the LSR on a form or report, use an
expression to display it, either in a calculated field in the query that
the form or report is based on, or in the Control Source of an unbound
textbox on the form or report itself. Something like this:
="13/" & Val(Format([RecordDate],"yy"))-(Month([RecordDate])>9) &
DatePart("y",[RecordDate]) & "-E" & Format([SequenceNumber],"0000")

So the users don't have to enter anything, so therefore no more
mistakes!

If that expression seems a bit cumbersome, remember that you would just
need to copy/paste it a few times probably. You could make a
user-defined function to put it together if you wanted, so your work in
queries and reports would be neater.

--
Steve Schapel, Microsoft Access MVP

Is there a way to get access 2007 to automatically input the ordinal date for
a given field and combine it with other data?
Here is my situation, I created a Dbase where the “Local Service Request
Number†[LSR] has to be inputed manually because I couldn’t figure out how to
make Access do this automatically, but users are making a lot of mistakes.
The LSR number looks like this; 13/8258-E1115.
Here is the breakdown;
13/ has to be at the beginning of every LSR number, so those characters
nerve change.
8 Fiscal year, so that will change to a “9†on 1 October 2008.
258 the ordinal date, which 258= 15 Sep, 259= 16 Sep… etc.
-E never change, dash “E†must come after the ordinal date.
1115 is the sequence number, which began with 0001, which boils down to the
number of service tickets.
Here is what I would like to do, have access generate the ordinal date based
on the PC date, and put an 8 in front of the ordinal date. I was able to
figured out how to automatically input the sequence number by making it the
primary key, data type = AutoNumber, Field size = Long Integer, New Values =
Increment, Format = 0000, Indexed = Yes (No Duplicates), which generates
numbers beginning with 0001 and increments for every new service ticket. I
would like the user to be able to see the combine data in one cell on the
input form and I would like to generate reports with the combine data.
Any help would be greatly appreciated.

 

[John W. Vinson]

258 the ordinal date, which 258= 15 Sep, 259= 16 Sep… etc.

DatePart("y", Date())

will return this value.

 

[Gil]

Steve, why does Access occasionally skip numbers [AutoNumber]? Two weeks ago
Friday, my user experienced it for the first time. AutoNumber skipped from
63 to 65. I fix it by making a copy of the table without the data, then
copying the data into the new table [cut/paste]. I also had to break all
relationship from the original table before deleting it, renamed the copy to
the original name and then recreated relationships. The database seems to be
working fine. I did notice the table moved down to a new position on the
navigation menu. All this because of AutoNumber – I wonder how often this
will happen. I couldn’t make your Dmax suggestion work, but I think this is
motivation enough to keep at it.
--

Gil

Gil,

You have a number of different "pieces" of information here, as
represented by your "breakdown". These should not be combined into a
single field.

I can certainly understand you want the LSR to be *displayed* in the
prescribed format, according to your business rules. But how it is
displayed, and how it is stored, and how it is entered, are 3 different
things.

The "13/" and the "E-" do not need to be recorded in the database at
all, if they will never change.

Using an Autonumber for the "1115" is ok, so long as you don't care if
there may be occasional gaps in the sequence of the numbers. Otherwise
I would recommend another approach (see below).

And the rest of it i.e. "8258" is derived from or calculated from the
date. So the only data that needs entering is the date. And if I
understand you correctly, even this does not need entering as you will
be using the current date when the record is entered.

So I would do it like this:
1. Have a field for the RecordDate. On the form, set the Default Value
of the applicable textbox to:
Date()
2. Have a Number field for the SequenceNumber. On the form, set the
Default Value of the applicable textbox to:
DMax("[SequenceNumber]","NameOfYourTable")+1
3. Whenever you need to see the LSR on a form or report, use an
expression to display it, either in a calculated field in the query that
the form or report is based on, or in the Control Source of an unbound
textbox on the form or report itself. Something like this:
="13/" & Val(Format([RecordDate],"yy"))-(Month([RecordDate])>9) &
DatePart("y",[RecordDate]) & "-E" & Format([SequenceNumber],"0000")

So the users don't have to enter anything, so therefore no more
mistakes!

If that expression seems a bit cumbersome, remember that you would just
need to copy/paste it a few times probably. You could make a
user-defined function to put it together if you wanted, so your work in
queries and reports would be neater.

--
Steve Schapel, Microsoft Access MVP

Is there a way to get access 2007 to automatically input the ordinal date for
a given field and combine it with other data?
Here is my situation, I created a Dbase where the “Local Service Request
Number†[LSR] has to be inputed manually because I couldn’t figure out how to
make Access do this automatically, but users are making a lot of mistakes.
The LSR number looks like this; 13/8258-E1115.
Here is the breakdown;
13/ has to be at the beginning of every LSR number, so those characters
nerve change.
8 Fiscal year, so that will change to a “9†on 1 October 2008.
258 the ordinal date, which 258= 15 Sep, 259= 16 Sep… etc.
-E never change, dash “E†must come after the ordinal date.
1115 is the sequence number, which began with 0001, which boils down to the
number of service tickets.
Here is what I would like to do, have access generate the ordinal date based
on the PC date, and put an 8 in front of the ordinal date. I was able to
figured out how to automatically input the sequence number by making it the
primary key, data type = AutoNumber, Field size = Long Integer, New Values =
Increment, Format = 0000, Indexed = Yes (No Duplicates), which generates
numbers beginning with 0001 and increments for every new service ticket. I
would like the user to be able to see the combine data in one cell on the
input form and I would like to generate reports with the combine data.
Any help would be greatly appreciated.

 

[Douglas J. Steele]

Autonumbers are not guaranteed to be continuous. If you start to work on a
record and then change your mind, the value that would have been used for
that record is lost to you. Given that the only purpose of an Autonumber
field is to provide a (practically guaranteed) unique value that can be used
as a primary key, having gaps in the numbering shouldn't matter. (In fact,
if you replicate your database, the Autonumber field will be changed to
Random, so that you're guaranteed the numbering won't be consecutive!)

It's actually usual not to even show the value of the Autonumber field to
the user. If you're trying to assign meaning to the value of the Autonumber
field, you probably shouldn't be using an Autonumber field.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Steve, why does Access occasionally skip numbers [AutoNumber]? Two weeks
ago
Friday, my user experienced it for the first time. AutoNumber skipped
from
63 to 65. I fix it by making a copy of the table without the data, then
copying the data into the new table [cut/paste]. I also had to break all
relationship from the original table before deleting it, renamed the copy
to
the original name and then recreated relationships. The database seems to
be
working fine. I did notice the table moved down to a new position on the
navigation menu. All this because of AutoNumber - I wonder how often this
will happen. I couldn't make your Dmax suggestion work, but I think this
is
motivation enough to keep at it.
--

Gil

Gil,

You have a number of different "pieces" of information here, as
represented by your "breakdown". These should not be combined into a
single field.

I can certainly understand you want the LSR to be *displayed* in the
prescribed format, according to your business rules. But how it is
displayed, and how it is stored, and how it is entered, are 3 different
things.

The "13/" and the "E-" do not need to be recorded in the database at
all, if they will never change.

Using an Autonumber for the "1115" is ok, so long as you don't care if
there may be occasional gaps in the sequence of the numbers. Otherwise
I would recommend another approach (see below).

And the rest of it i.e. "8258" is derived from or calculated from the
date. So the only data that needs entering is the date. And if I
understand you correctly, even this does not need entering as you will
be using the current date when the record is entered.

So I would do it like this:
1. Have a field for the RecordDate. On the form, set the Default Value
of the applicable textbox to:
Date()
2. Have a Number field for the SequenceNumber. On the form, set the
Default Value of the applicable textbox to:
DMax("[SequenceNumber]","NameOfYourTable")+1
3. Whenever you need to see the LSR on a form or report, use an
expression to display it, either in a calculated field in the query that
the form or report is based on, or in the Control Source of an unbound
textbox on the form or report itself. Something like this:
="13/" & Val(Format([RecordDate],"yy"))-(Month([RecordDate])>9) &
DatePart("y",[RecordDate]) & "-E" & Format([SequenceNumber],"0000")

So the users don't have to enter anything, so therefore no more
mistakes!

If that expression seems a bit cumbersome, remember that you would just
need to copy/paste it a few times probably. You could make a
user-defined function to put it together if you wanted, so your work in
queries and reports would be neater.

--
Steve Schapel, Microsoft Access MVP

Is there a way to get access 2007 to automatically input the ordinal
date for
a given field and combine it with other data?
Here is my situation, I created a Dbase where the "Local Service
Request
Number" [LSR] has to be inputed manually because I couldn't figure out
how to
make Access do this automatically, but users are making a lot of
mistakes.
The LSR number looks like this; 13/8258-E1115.
Here is the breakdown;
13/ has to be at the beginning of every LSR number, so those
characters
nerve change.
8 Fiscal year, so that will change to a "9" on 1 October 2008.
258 the ordinal date, which 258= 15 Sep, 259= 16 Sep. etc.
-E never change, dash "E" must come after the ordinal date.
1115 is the sequence number, which began with 0001, which boils down to
the
number of service tickets.
Here is what I would like to do, have access generate the ordinal date
based
on the PC date, and put an 8 in front of the ordinal date. I was able
to
figured out how to automatically input the sequence number by making it
the
primary key, data type = AutoNumber, Field size = Long Integer, New
Values =
Increment, Format = 0000, Indexed = Yes (No Duplicates), which
generates
numbers beginning with 0001 and increments for every new service
ticket. I
would like the user to be able to see the combine data in one cell on
the
input form and I would like to generate reports with the combine data.
Any help would be greatly appreciated.

 

[Gil]

Doug, I wish I knew that before implementing the database. I guess I will
continue using my workaround until I implement something a bit more
permanent. The problem is I am combining the AutoNumber field in addition to
other fields to create a Local Service Request number, as describe in my
first post. I’ll figure something out… Thanks for your comment.
--

Gil

Autonumbers are not guaranteed to be continuous. If you start to work on a
record and then change your mind, the value that would have been used for
that record is lost to you. Given that the only purpose of an Autonumber
field is to provide a (practically guaranteed) unique value that can be used
as a primary key, having gaps in the numbering shouldn't matter. (In fact,
if you replicate your database, the Autonumber field will be changed to
Random, so that you're guaranteed the numbering won't be consecutive!)

It's actually usual not to even show the value of the Autonumber field to
the user. If you're trying to assign meaning to the value of the Autonumber
field, you probably shouldn't be using an Autonumber field.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Steve, why does Access occasionally skip numbers [AutoNumber]? Two weeks
ago
Friday, my user experienced it for the first time. AutoNumber skipped
from
63 to 65. I fix it by making a copy of the table without the data, then
copying the data into the new table [cut/paste]. I also had to break all
relationship from the original table before deleting it, renamed the copy
to
the original name and then recreated relationships. The database seems to
be
working fine. I did notice the table moved down to a new position on the
navigation menu. All this because of AutoNumber - I wonder how often this
will happen. I couldn't make your Dmax suggestion work, but I think this
is
motivation enough to keep at it.
--

Gil

Gil,

You have a number of different "pieces" of information here, as
represented by your "breakdown". These should not be combined into a
single field.

I can certainly understand you want the LSR to be *displayed* in the
prescribed format, according to your business rules. But how it is
displayed, and how it is stored, and how it is entered, are 3 different
things.

The "13/" and the "E-" do not need to be recorded in the database at
all, if they will never change.

Using an Autonumber for the "1115" is ok, so long as you don't care if
there may be occasional gaps in the sequence of the numbers. Otherwise
I would recommend another approach (see below).

And the rest of it i.e. "8258" is derived from or calculated from the
date. So the only data that needs entering is the date. And if I
understand you correctly, even this does not need entering as you will
be using the current date when the record is entered.

So I would do it like this:
1. Have a field for the RecordDate. On the form, set the Default Value
of the applicable textbox to:
Date()
2. Have a Number field for the SequenceNumber. On the form, set the
Default Value of the applicable textbox to:
DMax("[SequenceNumber]","NameOfYourTable")+1
3. Whenever you need to see the LSR on a form or report, use an
expression to display it, either in a calculated field in the query that
the form or report is based on, or in the Control Source of an unbound
textbox on the form or report itself. Something like this:
="13/" & Val(Format([RecordDate],"yy"))-(Month([RecordDate])>9) &
DatePart("y",[RecordDate]) & "-E" & Format([SequenceNumber],"0000")

So the users don't have to enter anything, so therefore no more
mistakes!

If that expression seems a bit cumbersome, remember that you would just
need to copy/paste it a few times probably. You could make a
user-defined function to put it together if you wanted, so your work in
queries and reports would be neater.

--
Steve Schapel, Microsoft Access MVP

Gil wrote:
Is there a way to get access 2007 to automatically input the ordinal
date for
a given field and combine it with other data?
Here is my situation, I created a Dbase where the "Local Service
Request
Number" [LSR] has to be inputed manually because I couldn't figure out
how to
make Access do this automatically, but users are making a lot of
mistakes.
The LSR number looks like this; 13/8258-E1115.
Here is the breakdown;
13/ has to be at the beginning of every LSR number, so those
characters
nerve change.
8 Fiscal year, so that will change to a "9" on 1 October 2008.
258 the ordinal date, which 258= 15 Sep, 259= 16 Sep. etc.
-E never change, dash "E" must come after the ordinal date.
1115 is the sequence number, which began with 0001, which boils down to
the
number of service tickets.
Here is what I would like to do, have access generate the ordinal date
based
on the PC date, and put an 8 in front of the ordinal date. I was able
to
figured out how to automatically input the sequence number by making it
the
primary key, data type = AutoNumber, Field size = Long Integer, New
Values =
Increment, Format = 0000, Indexed = Yes (No Duplicates), which
generates
numbers beginning with 0001 and increments for every new service
ticket. I
would like the user to be able to see the combine data in one cell on
the
input form and I would like to generate reports with the combine data.
Any help would be greatly appreciated.