Re: Tables/Form

I have all 3 fields as "text" fields. Should the year field be a "date"
field?

"Douglas J. Steele" <NOSPAM_djsteele@xxxxxxxxxxxxxxxxx> wrote in message
news:OaGIl0URHHA.4404@xxxxxxxxxxxxxxxxxxxxxxx
In a blank column in the "Field" row of the grid.

Note that if you type exactly that, Access will assign a field name of
something like Expr1 to the new field. To give it a more meaningful name,
you can put a name (followed by a colon) in front of the concatenation:

MyField: "ITCC" & Format(Date(),yy) & "-" & [YourSequenceNumberField]

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Jenn Civello" <jenn.civello@xxxxxxxxx> wrote in message
news:uUxhpqURHHA.920@xxxxxxxxxxxxxxxxxxxxxxx
Jeff,

Where would I type "ITCC" & Format(Date(),yy) & "-" &
[YourSequenceNumberField] in the query? I have set up a field for each
fact.

E.g. ITCC is one field, 07 (the year) is another field and 001 is the
third field.


"Jeff Boyce" <nonsense@xxxxxxxxxxxx> wrote in message
news:%23Fzt91KRHHA.2124@xxxxxxxxxxxxxxxxxxxxxxx
Jenn

<scratch, scratch!> I could have sworn there was information at
mvps.org, but I don't find it there either.

The basic concept is that you will use DMax() to find the maximum value
currently used in your [SequenceNumber] field, add one to it, and use
THAT for your new sequence number in your new record.

If you want to have a "number" that looks like "ITCC07-001", concatenate
"ITCC" & Format(Date(),yy) & "-" & [YourSequenceNumberField] in a query
(note that this is untested aircode). Use that value from the query in
your forms and reports.

Regards

Jeff Boyce
Microsoft Office/Access MVP

"Jenn Civello" <jenn.civello@xxxxxxxxx> wrote in message
news:eMenzEKRHHA.3440@xxxxxxxxxxxxxxxxxxxxxxx
I can't seem to find any articles on "custom auto numbers", can you
please tell me where I should be looking?


"Jeff Boyce" <nonsense@xxxxxxxxxxxx> wrote in message
news:O7p7Il%23QHHA.4276@xxxxxxxxxxxxxxxxxxxxxxx
Jean

Take a look at www.mvps.org/access for "Custom Autonumbers" for an
example of how to do that.

By the way, I hope you aren't saying you have that entire string of
characters in one field. From your description, it consists of at
least two separate values (year, sequence #), and I suspect at least
one or two more. You need one field for each fact, and can easily use
a query to concatenate the field values together for display in a
form. If you try putting them all together in one field, you (and
Access) will have to work a lot harder.

Regards

Jeff Boyce
Microsoft Office/Access MVP

"Jenn Civello" <jenn.civello@xxxxxxxxx> wrote in message
news:u2HlQU%23QHHA.4384@xxxxxxxxxxxxxxxxxxxxxxx
I have a table that I have consecutive numbers in and I would like to
setup a form that will automatically enter the next number each time.
E.G. ITCC07-001, once this number is used I would like it to
automatically go to ITCC07-002. But I need to be able to stop this at
the end of the year. Can you please let me know if this can be done.

Thanks













.

Relevant Pages

  • Re: Multiple requests for parameter value
    ... Can you give me an example of a "flattening" query? ... "Jeff Boyce" wrote: ... Microsoft Office/Access MVP ...
    (microsoft.public.access.reports)
  • Re: Query not returning results for a text field
    ... I guess my next question is: Is this an accurate way to query for the ... "Jeff Boyce" wrote: ... Microsoft Office/Access MVP ... I have typed manually and copy/pasted the number into the criteria ...
    (microsoft.public.access.queries)
  • Re: Query not returning results for a text field
    ... I guess my next question is: Is this an accurate way to query for the ... "Jeff Boyce" wrote: ... Microsoft Office/Access MVP ... I have typed manually and copy/pasted the number into the criteria ...
    (microsoft.public.access.queries)
  • Re: Update top n records
    ... Microsoft Office/Access MVP ... to the query design window to see the code. ... "Jeff Boyce" wrote: ... All widgets have a number and are in numerical order in inventory. ...
    (microsoft.public.access.formscoding)
  • Re: Tables/Form
    ... THAT for your new sequence number in your new record. ... Use that value from the query in ... Jeff Boyce ... Microsoft Office/Access MVP ...
    (microsoft.public.access.gettingstarted)
Loading