By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,504 Members | 2,606 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,504 IT Pros & Developers. It's quick & easy.

incremental ID resetting each month

P: n/a
i am trying to get "ID" an incremental number that simply resets to 1 each
month in my "table1" thanks,

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200808/1

Aug 15 '08 #1
Share this Question
Share on Google+
10 Replies


P: n/a
The day() function gives you the day of the month. Use it to get your number.
Bonnie
http://www.dataplus-svc.com

drrajnishpatel wrote:
>i am trying to get "ID" an incremental number that simply resets to 1 each
month in my "table1" thanks,
--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200808/1

Aug 15 '08 #2

P: n/a
Sorry - use day(now())

bhicks11 wrote:
>The day() function gives you the day of the month. Use it to get your number.

Bonnie
http://www.dataplus-svc.com
>>i am trying to get "ID" an incremental number that simply resets to 1 each
month in my "table1" thanks,
--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200808/1

Aug 15 '08 #3

P: n/a
thanks for your response,......but i think i am misunderstood....the
incremental number takes entry daily.... goes from 1 to 99 over the month and
again resets to 1 on the 1st of next month.....thanks

bhicks11 wrote:
>Sorry - use day(now())
>>The day() function gives you the day of the month. Use it to get your number.
--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200808/1

Aug 15 '08 #4

P: n/a
Assuming you have a date field in your table. The following will give you
the next value for your counter.

nz(dmax("[CounterFieldName]","TableName","[DateField]>=dateserial(year(now),
month(now),1)"),0)+1

"drrajnishpatel via AccessMonster.com" <u37542@uwewrote in message
news:88b5dbea96bd2@uwe...
thanks for your response,......but i think i am misunderstood....the
incremental number takes entry daily.... goes from 1 to 99 over the month
and
again resets to 1 on the 1st of next month.....thanks

bhicks11 wrote:
Sorry - use day(now())
>The day() function gives you the day of the month. Use it to get your
number.
>

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200808/1

Aug 15 '08 #5

P: n/a
Thanks Ron.... i will run the code and come back to you.....yes i have a date
field....

paii, Ron wrote:
>Assuming you have a date field in your table. The following will give you
the next value for your counter.

nz(dmax("[CounterFieldName]","TableName","[DateField]>=dateserial(year(now),
month(now),1)"),0)+1
>thanks for your response,......but i think i am misunderstood....the
incremental number takes entry daily.... goes from 1 to 99 over the month and
[quoted text clipped - 3 lines]
>
The day() function gives you the day of the month. Use it to get your number.
--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200808/1

Aug 15 '08 #6

P: n/a
HATS OFF to you.... you helped me to solve a very very long problem....i am a
surgeon....a professor of surgery but a child in Access... but i have
deVeloped a passion for access....i am creating a database for my own clinic..
and i wanted to creat a incremental unique id for each patient....so that
the unque id would look like 20080910, for the tenth patient in the august
month of the 2008 yeaR.... but copuld not do it...like all the veterans
advised could not put it togather, but your help put the final nail into the
problem and it has just clicked excellent.....THANKS RON...

paii, Ron wrote:
>Assuming you have a date field in your table. The following will give you
the next value for your counter.

nz(dmax("[CounterFieldName]","TableName","[DateField]>=dateserial(year(now),
month(now),1)"),0)+1
>thanks for your response,......but i think i am misunderstood....the
incremental number takes entry daily.... goes from 1 to 99 over the month and
[quoted text clipped - 3 lines]
>
The day() function gives you the day of the month. Use it to get your number.
--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200808/1

Aug 15 '08 #7

P: n/a
Congratulations! Nice little piece of code Ron.

Bonnie
http://www.dataplus-svc.com

drrajnishpatel wrote:
>HATS OFF to you.... you helped me to solve a very very long problem....i am a
surgeon....a professor of surgery but a child in Access... but i have
deVeloped a passion for access....i am creating a database for my own clinic..
.and i wanted to creat a incremental unique id for each patient....so that
the unque id would look like 20080910, for the tenth patient in the august
month of the 2008 yeaR.... but copuld not do it...like all the veterans
advised could not put it togather, but your help put the final nail into the
problem and it has just clicked excellent.....THANKS RON...
>>Assuming you have a date field in your table. The following will give you
the next value for your counter.
[quoted text clipped - 7 lines]
>>
The day() function gives you the day of the month. Use it to get your number.
--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200808/1

Aug 15 '08 #8

P: n/a
dear ron.... a little trouble the series does go beyond '10' any reason here

drrajnishpatel wrote:
>HATS OFF to you.... you helped me to solve a very very long problem....i am a
surgeon....a professor of surgery but a child in Access... but i have
deVeloped a passion for access....i am creating a database for my own clinic..
.and i wanted to creat a incremental unique id for each patient....so that
the unque id would look like 20080910, for the tenth patient in the august
month of the 2008 yeaR.... but copuld not do it...like all the veterans
advised could not put it togather, but your help put the final nail into the
problem and it has just clicked excellent.....THANKS RON...
>>Assuming you have a date field in your table. The following will give you
the next value for your counter.
[quoted text clipped - 7 lines]
>>
The day() function gives you the day of the month. Use it to get your number.
--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200808/1

Aug 15 '08 #9

P: n/a
Are you having a problem or is it just a concern?
The "dMax" function returns the largest value in the selection set; in this
case all records in the current month. The code will continue counting up
until the month changes.

"drrajnishpatel via AccessMonster.com" <u37542@uwewrote in message
news:88b84ba4a24cf@uwe...
dear ron.... a little trouble the series does go beyond '10' any reason
here
>
drrajnishpatel wrote:
HATS OFF to you.... you helped me to solve a very very long problem....i
am a
surgeon....a professor of surgery but a child in Access... but i have
deVeloped a passion for access....i am creating a database for my own
clinic..
.and i wanted to creat a incremental unique id for each patient....so
that
the unque id would look like 20080910, for the tenth patient in the
august
month of the 2008 yeaR.... but copuld not do it...like all the veterans
advised could not put it togather, but your help put the final nail into
the
problem and it has just clicked excellent.....THANKS RON...
>Assuming you have a date field in your table. The following will give
you
>the next value for your counter.
[quoted text clipped - 7 lines]
>
The day() function gives you the day of the month. Use it to get
your number.
>
--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200808/1

Aug 18 '08 #10

P: n/a
once again .....THANKS RON ... for your concern ... i had that problem... on
my form but then i found there was some clash of code... i was mixing up to
different codes on one event and pulling results to different columns.... i
could identify that and so sepratyed the events into before and after update..
.. your little piece of code is marvellous..... thanks for doing follow up
also.....
drrajnishpatel

paii, Ron wrote:
>Are you having a problem or is it just a concern?
The "dMax" function returns the largest value in the selection set; in this
case all records in the current month. The code will continue counting up
until the month changes.
>dear ron.... a little trouble the series does go beyond '10' any reason here
[quoted text clipped - 12 lines]
>>
The day() function gives you the day of the month. Use it to get your number.
--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200808/1

Aug 18 '08 #11

This discussion thread is closed

Replies have been disabled for this discussion.