Re: Combining Data from multiple fields

Tech-Archive recommends: Fix windows errors by optimizing your registry

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


"Steve Schapel" wrote:

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.


.

Relevant Pages

  • Re: Combining Data from multiple fields
    ... You have a number of different "pieces" of information here, as represented by your "breakdown". ... 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. ... 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. ...
    (microsoft.public.access.forms)
  • Re: Combining Data from multiple fields
    ... Steve, why does Access occasionally skip numbers [AutoNumber]? ... there may be occasional gaps in the sequence of the numbers. ... the form or report is based on, or in the Control Source of an unbound ...
    (microsoft.public.access.forms)
  • Re: Crosstab query with irregularly-spaced dates
    ... I based my crosstab query on the ... sequence number instead of the date, allowing me to create the relative ... Then, as you suggest, I handle the report labels with VBA ... > Steve Clark, Access MVP ...
    (microsoft.public.access.queries)
  • Re: Combining Data from multiple fields
    ... Given that the only purpose of an Autonumber ... I can certainly understand you want the LSR to be *displayed* in the ... there may be occasional gaps in the sequence of the numbers. ... the form or report is based on, or in the Control Source of an unbound ...
    (microsoft.public.access.forms)
  • Re: Sort of OT - Tracking clicked links in an email?
    ... Steve, this is all pretty confusing to me. ... > If the newsletter is server based and going to be viewed on your server by ... > is for the client machine to send a report back to your server in some ... >> alerts to go off? ...
    (microsoft.public.frontpage.client)