Re: Another numbering system question

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

Hi,
Thanks again for hanging in there with me.

Do you have a date field in the record? Originally no. I'll add one. It
appears best to do so.

Is there a primary key field or combination of fields? No primary key field.

My concern was how to add numbers to a field that had a mishmash of numbers
and text rather than a pure number.

For example...

Field 1 (text)
1
2a
3
3c

I believe from what you given me will work. I'll give it a try anyway

Thanks
--
AusTexRich


"BruceM" wrote:

It will start over at 1 next year. Note this part of the code for DMax,
which is the optional criteria:
"Year([DateField]) = " & Year([DateField])
If the DMax statement just included the following:
DMax("NumberField", "YourTable") + 1
then Access would find the largest value for NumberField in YourTable and
add 1 to it.
However, with the optional criteria added:
DMax("NumberField", "YourTable","Year([DateField]) = " & Year([DateField]) +
1
Access will find the largest value for NumberField in YourTable for which
the Year portion of DateField is the same as the Year portion of DateField
for the current record, and add 1 to it.
The Nz in the original code allows for the fact that for the year's first
record there will be no records in which Year from DateField equals Year
from the current record. In that case DMax will produce a null value. Nz
replaces the null with a 0, to which 1 is added, resulting in a value of 1
in NumberField for the first record of any year.
Do you have a date field in the record?
Is there a primary key field or combination of fields?
My original post includes the reasons why I ask these questions. The year
can be added to the number and stored, but first I'm trying to find out if
it's necessary. If it isn't you can just concatenate the two-digit year
with the number as I described.

"AusTexRich" <AusTexRich@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:BFAC1A93-CC76-46EE-A9FA-2C1C2BD80F01@xxxxxxxxxxxxxxxx
Hi,
Great stuff. I very appreciative of your help. You guys always have some
great stuff.
My problem is adding new records to a table of preexisting data that is
grouped by year (kind of) and each prior year has its own count beginning
at
001 to the last record for the year.
If I use DMax I get the next record 539. Great for this year, but, what
about next year? I'd like the "counter" to reset to "001".

I was hoping that if I add a text field to capture the year "08" I can
base
a numbering system on that and start at "001".
--
AusTexRich


"BruceM" wrote:

Assuming the year is stored somewhere in the record (as part of a date
field), you could do something like this in the After Update event for
the
control bound to the date field:
If Me.NewRecord Then
Me.NumberField = Nz(DMax("NumberField", "YourTable", _
"Year([DateField]) = " & Year([DateField])), 0) + 1
End If

Use your own field and table names, of course.

To display the number, you could do something like this as the Control
Source of an unbound text box:
=Format([DateField],"yy") & "-" & Format([NumberField],"000")

Keep in mind that NumberField cannot be used as the primary key, since it
will start over from 0 each year. If you already have a primary key that
is
not a problem. If this number is to be the primary key you could add the
year to the beginning of the number, but that involves a few more steps,
so
I will wait until I hear more about the details. Some would argue that
adding the year to the number is storing the year redundantly, but
without
that you would need either a compound primary key or a separate primary
key
field.

The general point here is that you can build an automatically
incrementing
number into your database. As always, the details dictate the specifics.

"AusTexRich" <AusTexRich@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:E386988D-A1C4-4426-A57A-2DCD504FFE44@xxxxxxxxxxxxxxxx
I want to create a numbering system, in a three character format, in a
form
starting at "001" and so on. Now, because the of the formatting of the
prior
data from an Excel file, I've had to distinguish one year's records
from
the
next with a text field "08" (year). So when a new record is added, the
text
"08" is added and then the count begins at "001". How do I do that?

table fields are: [I_period] - "08", [row_num] - "001", "002, etc,.
Thanks all.
--
AusTexRich






.

Relevant Pages

  • Re: Another numbering system question
    ... Note this part of the code for DMax, ... with the optional criteria added: ... Access will find the largest value for NumberField in YourTable for which ... Keep in mind that NumberField cannot be used as the primary key, ...
    (microsoft.public.access.forms)
  • Re: Another numbering system question
    ... Is there a primary key field or combination of fields? ... Access will find the largest value for NumberField in YourTable for which ... the Year portion of DateField is the same as the Year portion of DateField ... In that case DMax will produce a null value. ...
    (microsoft.public.access.forms)
  • Re: Another numbering system question
    ... "BruceM" wrote: ... Is there a primary key field or combination of fields? ... then Access would find the largest value for NumberField in YourTable ... DateField ...
    (microsoft.public.access.forms)
  • Re: Another numbering system question
    ... incrementing number or combination of text and numbers, ... Is there a primary key field or combination of fields? ... then Access would find the largest value for NumberField in YourTable ... DateField ...
    (microsoft.public.access.forms)
  • Re: Recordset.addnew and Linked Oracle table
    ... Record the date/time at the top of your procedure in a variable and then pull the record with a value>= StartDateTime after you make the record and get its key value? ... if the primary key values are assigned sequentially, you could use dMax before the operation to determine the max PK value, then use dMax after the operation. ... Domain aggregate functions are slow, so, depending on how big the table is, you may want to try using a recordset to pull the max. ...
    (microsoft.public.access.modulesdaovba)