Is there a way to reset an auto number in access tables with out changing the
number for existing records? I have an auto number starting at 260001; as of
January 1, 2007 I want the numbering sequence to start at 270001 however I
don't want the numbers for previous records to be modified. Is this possible
using the field format of auto number?

I haven't tested this, but I think when I needed to do somthing similar, I
wrote a query that forced a value into the table that was 1 less than the
next value I required, ie somthing like:

INSERT INTO myTable(myAutoNumberField) VALUES (270000);

Cheers,

ChrisM

"Gretchen" <(E-Mail Removed)> wrote in message
news:8CFC6AFE-7117-461C-99B5-(E-Mail Removed)...
> Is there a way to reset an auto number in access tables with out changing
> the
> number for existing records? I have an auto number starting at 260001; as
> of
> January 1, 2007 I want the numbering sequence to start at 270001 however I
> don't want the numbers for previous records to be modified. Is this
> possible
> using the field format of auto number?

Autonumbers exist for one purpose only: to provide a (practically
guaranteed) unique value that can be used as a primary key. No meaning
should ever be assigned to the value of the autonumber. In fact, usually the
user isn't even aware of what value has been assigned.

Now, you appear to have what's often referred to as an "intelligent key",
which isn't a compliment. It would appear that you're trying to store more
than one piece of information in the same field, which is actually a
violation of database normalization rules.

Having said all that, yes it is possible to do what you want. Insert a row
with a record that has a value of 270000, and the next row to be inserted
will get an autonumber value of 270001. Once that's occurred, you can delete
the bogus 270000 record you inserted. Hopefully, though, you'll rethink what
you're doing and not take that route.

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


"Gretchen" <(E-Mail Removed)> wrote in message
news:8CFC6AFE-7117-461C-99B5-(E-Mail Removed)...
> Is there a way to reset an auto number in access tables with out changing
the
> number for existing records? I have an auto number starting at 260001; as
of
> January 1, 2007 I want the numbering sequence to start at 270001 however I
> don't want the numbers for previous records to be modified. Is this
possible
> using the field format of auto number?

Gretchen wrote:
> Is there a way to reset an auto number in access tables with out
> changing the number for existing records? I have an auto number
> starting at 260001; as of January 1, 2007 I want the numbering
> sequence to start at 270001 however I don't want the numbers for
> previous records to be modified. Is this possible using the field
> format of auto number?

Let me recommend a careful reading of Mr. Steele's response.

I will suggest it even stronger. Using an autonumber anywhere that a
user will see it is an almost sure way to befuddle and confuse the users.

Remember that you can combine fields when displaying them so you can
store a two character or digit (note: they are not the same and you should
be careful about which you chose) and a incremental field (see note above)
and display them as if they were one.

If you already have a data related to the record, then you can pull the
year from there. It is almost always better to use existing data than to
make a second field from that data since someone is bound to change one and
the other will be missed.

--
Joseph Meehan

Dia duit

OK, I'll rethink this but I still need help!
I'll simply join in my reports the prefix "26" or "27" to my job number.
But I still have a problem in getting the number to increase incrementally
for me. I want the number to move from 0001 to 0002 and so on. BUT, I have
to have the ability to reset it back to 0001 on January 1st.

"Joseph Meehan" wrote:

> Gretchen wrote:
> > Is there a way to reset an auto number in access tables with out
> > changing the number for existing records? I have an auto number
> > starting at 260001; as of January 1, 2007 I want the numbering
> > sequence to start at 270001 however I don't want the numbers for
> > previous records to be modified. Is this possible using the field
> > format of auto number?
>
> Let me recommend a careful reading of Mr. Steele's response.
>
> I will suggest it even stronger. Using an autonumber anywhere that a
> user will see it is an almost sure way to befuddle and confuse the users.
>
> Remember that you can combine fields when displaying them so you can
> store a two character or digit (note: they are not the same and you should
> be careful about which you chose) and a incremental field (see note above)
> and display them as if they were one.
>
> If you already have a data related to the record, then you can pull the
> year from there. It is almost always better to use existing data than to
> make a second field from that data since someone is bound to change one and
> the other will be missed.
>
> --
> Joseph Meehan
>
> Dia duit
>
>
>

Gretchen wrote:
> OK, I'll rethink this but I still need help!
> I'll simply join in my reports the prefix "26" or "27" to my job
> number. But I still have a problem in getting the number to increase
> incrementally for me. I want the number to move from 0001 to 0002
> and so on. BUT, I have to have the ability to reset it back to 0001
> on January 1st.

You can do that using the DMAX function

Me! = Nz(DMax("YOURFIELDNAME","TABLENAME"), 0) + 1

Replace the 0 if you want a different starting number

I'll have to think about how to modify that to allow you to start over
at 1 again. Maybe basing it on a query that filters out prior years data?


>
> "Joseph Meehan" wrote:
>
>> Gretchen wrote:
>>> Is there a way to reset an auto number in access tables with out
>>> changing the number for existing records? I have an auto number
>>> starting at 260001; as of January 1, 2007 I want the numbering
>>> sequence to start at 270001 however I don't want the numbers for
>>> previous records to be modified. Is this possible using the field
>>> format of auto number?
>>
>> Let me recommend a careful reading of Mr. Steele's response.
>>
>> I will suggest it even stronger. Using an autonumber anywhere
>> that a user will see it is an almost sure way to befuddle and
>> confuse the users.
>>
>> Remember that you can combine fields when displaying them so you
>> can store a two character or digit (note: they are not the same and
>> you should be careful about which you chose) and a incremental field
>> (see note above) and display them as if they were one.
>>
>> If you already have a data related to the record, then you can
>> pull the year from there. It is almost always better to use
>> existing data than to make a second field from that data since
>> someone is bound to change one and the other will be missed.
>>
>> --
>> Joseph Meehan
>>
>> Dia duit

--
Joseph Meehan

Dia duit

Thanks...maybe this is dumb question but I put that code where in my form? I
was trying to use it on the "on focus" but it was not working.

"Joseph Meehan" wrote:

> Gretchen wrote:
> > OK, I'll rethink this but I still need help!
> > I'll simply join in my reports the prefix "26" or "27" to my job
> > number. But I still have a problem in getting the number to increase
> > incrementally for me. I want the number to move from 0001 to 0002
> > and so on. BUT, I have to have the ability to reset it back to 0001
> > on January 1st.
>
> You can do that using the DMAX function
>
> Me! = Nz(DMax("YOURFIELDNAME","TABLENAME"), 0) + 1
>
> Replace the 0 if you want a different starting number
>
> I'll have to think about how to modify that to allow you to start over
> at 1 again. Maybe basing it on a query that filters out prior years data?
>
>
> >
> > "Joseph Meehan" wrote:
> >
> >> Gretchen wrote:
> >>> Is there a way to reset an auto number in access tables with out
> >>> changing the number for existing records? I have an auto number
> >>> starting at 260001; as of January 1, 2007 I want the numbering
> >>> sequence to start at 270001 however I don't want the numbers for
> >>> previous records to be modified. Is this possible using the field
> >>> format of auto number?
> >>
> >> Let me recommend a careful reading of Mr. Steele's response.
> >>
> >> I will suggest it even stronger. Using an autonumber anywhere
> >> that a user will see it is an almost sure way to befuddle and
> >> confuse the users.
> >>
> >> Remember that you can combine fields when displaying them so you
> >> can store a two character or digit (note: they are not the same and
> >> you should be careful about which you chose) and a incremental field
> >> (see note above) and display them as if they were one.
> >>
> >> If you already have a data related to the record, then you can
> >> pull the year from there. It is almost always better to use
> >> existing data than to make a second field from that data since
> >> someone is bound to change one and the other will be missed.
> >>
> >> --
> >> Joseph Meehan
> >>
> >> Dia duit
>
> --
> Joseph Meehan
>
> Dia duit
>
>
>

When I type in that code the first thing I notice is the code is
automatically removing the ! after Me...furthermore, it is giving me a
perameter error.

"Gretchen" wrote:

> Thanks...maybe this is dumb question but I put that code where in my form? I
> was trying to use it on the "on focus" but it was not working.
>
> "Joseph Meehan" wrote:
>
> > Gretchen wrote:
> > > OK, I'll rethink this but I still need help!
> > > I'll simply join in my reports the prefix "26" or "27" to my job
> > > number. But I still have a problem in getting the number to increase
> > > incrementally for me. I want the number to move from 0001 to 0002
> > > and so on. BUT, I have to have the ability to reset it back to 0001
> > > on January 1st.
> >
> > You can do that using the DMAX function
> >
> > Me! = Nz(DMax("YOURFIELDNAME","TABLENAME"), 0) + 1
> >
> > Replace the 0 if you want a different starting number
> >
> > I'll have to think about how to modify that to allow you to start over
> > at 1 again. Maybe basing it on a query that filters out prior years data?
> >
> >
> > >
> > > "Joseph Meehan" wrote:
> > >
> > >> Gretchen wrote:
> > >>> Is there a way to reset an auto number in access tables with out
> > >>> changing the number for existing records? I have an auto number
> > >>> starting at 260001; as of January 1, 2007 I want the numbering
> > >>> sequence to start at 270001 however I don't want the numbers for
> > >>> previous records to be modified. Is this possible using the field
> > >>> format of auto number?
> > >>
> > >> Let me recommend a careful reading of Mr. Steele's response.
> > >>
> > >> I will suggest it even stronger. Using an autonumber anywhere
> > >> that a user will see it is an almost sure way to befuddle and
> > >> confuse the users.
> > >>
> > >> Remember that you can combine fields when displaying them so you
> > >> can store a two character or digit (note: they are not the same and
> > >> you should be careful about which you chose) and a incremental field
> > >> (see note above) and display them as if they were one.
> > >>
> > >> If you already have a data related to the record, then you can
> > >> pull the year from there. It is almost always better to use
> > >> existing data than to make a second field from that data since
> > >> someone is bound to change one and the other will be missed.
> > >>
> > >> --
> > >> Joseph Meehan
> > >>
> > >> Dia duit
> >
> > --
> > Joseph Meehan
> >
> > Dia duit
> >
> >
> >

Unfortunately, Joseph made a typo.

It should be:

Me!NameOfField = Nz(DMax("YOURFIELDNAME","TABLENAME"), 0) + 1

where NameOfField is the name of the field that's supposed to hold the
value.


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


"Gretchen" <(E-Mail Removed)> wrote in message
news:C5716839-225E-4440-AA5E-(E-Mail Removed)...
> When I type in that code the first thing I notice is the code is
> automatically removing the ! after Me...furthermore, it is giving me a
> perameter error.
>
> "Gretchen" wrote:
>
>> Thanks...maybe this is dumb question but I put that code where in my
>> form? I
>> was trying to use it on the "on focus" but it was not working.
>>
>> "Joseph Meehan" wrote:
>>
>> > Gretchen wrote:
>> > > OK, I'll rethink this but I still need help!
>> > > I'll simply join in my reports the prefix "26" or "27" to my job
>> > > number. But I still have a problem in getting the number to increase
>> > > incrementally for me. I want the number to move from 0001 to 0002
>> > > and so on. BUT, I have to have the ability to reset it back to 0001
>> > > on January 1st.
>> >
>> > You can do that using the DMAX function
>> >
>> > Me! = Nz(DMax("YOURFIELDNAME","TABLENAME"), 0) + 1
>> >
>> > Replace the 0 if you want a different starting number
>> >
>> > I'll have to think about how to modify that to allow you to start
>> > over
>> > at 1 again. Maybe basing it on a query that filters out prior years
>> > data?
>> >
>> >
>> > >
>> > > "Joseph Meehan" wrote:
>> > >
>> > >> Gretchen wrote:
>> > >>> Is there a way to reset an auto number in access tables with out
>> > >>> changing the number for existing records? I have an auto number
>> > >>> starting at 260001; as of January 1, 2007 I want the numbering
>> > >>> sequence to start at 270001 however I don't want the numbers for
>> > >>> previous records to be modified. Is this possible using the field
>> > >>> format of auto number?
>> > >>
>> > >> Let me recommend a careful reading of Mr. Steele's response.
>> > >>
>> > >> I will suggest it even stronger. Using an autonumber anywhere
>> > >> that a user will see it is an almost sure way to befuddle and
>> > >> confuse the users.
>> > >>
>> > >> Remember that you can combine fields when displaying them so you
>> > >> can store a two character or digit (note: they are not the same and
>> > >> you should be careful about which you chose) and a incremental field
>> > >> (see note above) and display them as if they were one.
>> > >>
>> > >> If you already have a data related to the record, then you can
>> > >> pull the year from there. It is almost always better to use
>> > >> existing data than to make a second field from that data since
>> > >> someone is bound to change one and the other will be missed.
>> > >>
>> > >> --
>> > >> Joseph Meehan
>> > >>
>> > >> Dia duit
>> >
>> > --
>> > Joseph Meehan
>> >
>> > Dia duit
>> >
>> >
>> >

Douglas J. Steele wrote:
> Unfortunately, Joseph made a typo.

Who me? Well I never! ...

Well maybe ... :-)

Thanks for the correction, I am sure it would have taken me forever to
notice it.


>
> It should be:
>
> Me!NameOfField = Nz(DMax("YOURFIELDNAME","TABLENAME"), 0) + 1
>
> where NameOfField is the name of the field that's supposed to hold the
> value.
>


--
Joseph Meehan

Dia duit