Primary key date and year in vba

[Balmora via AccessMonster.com]

im trying to find a way to create a primary key that will auto increment in
vba and it needs to be the month and year only so you would have January 2008
then February 2008 and so on till the new year and change the year to 2009 it
would be great because i have a database that we create a new record only
once a month and im tierd of having to pick the date in a date picker.

thank in advance

 

[Daniel Pineault]

Tired of entering one date a month, wow! lol Sorry I couldn't help myself.

using some simple vba you can easily determine the current day month
year.... assuming you wish to base it on today's date?!

date() = today's date
Year(date()) = the year of the entered date
Month(date()) = month of the entered date
Day(Date()) = day of the entered date
Format() permits you to output an input variable as you need it.

Using the Format() we can tranform any date into the format we want. so
Format(Date(),"mmm yyyy") would return

May 2008 (for today's date)

So you can use Format(Date(),"mmm yyyy") and pass it's value to the conrol
on your form where you have to enter you date.
--
Hope this helps,

Daniel Pineault
http://www.cardaconsultants.com/
For Access Tips and Examples: http://www.devhut.com/index.php
Please rate this post using the vote buttons if it was helpful.

 

[Balmora via AccessMonster.com]

what if i dont want to bass my self on the curent date because i can
create the record a few days in advance.
thanks for the quik responce

Tired of entering one date a month, wow! lol Sorry I couldn't help myself.

using some simple vba you can easily determine the current day month
year.... assuming you wish to base it on today's date?!

date() = today's date
Year(date()) = the year of the entered date
Month(date()) = month of the entered date
Day(Date()) = day of the entered date
Format() permits you to output an input variable as you need it.

Using the Format() we can tranform any date into the format we want. so
Format(Date(),"mmm yyyy") would return

May 2008 (for today's date)

So you can use Format(Date(),"mmm yyyy") and pass it's value to the conrol
on your form where you have to enter you date.

im trying to find a way to create a primary key that will auto increment in
vba and it needs to be the month and year only so you would have January 2008

[quoted text clipped - 3 lines]

thank in advance

 

[Daniel Pineault]

Well then you can use the dateadd() to add a redefined number of day to the
current date. Basically, you need to come up with some rule to base the
automate date on and from the the Format function, as mentioned earlier, will
do the rest.

Try something like
format(dateadd("d",7,date()),"mmmm yyyy")

the 7 is the number of day to add to the current date, change it to whatever
suits your needs.
--
Hope this helps,

Daniel Pineault
http://www.cardaconsultants.com/
For Access Tips and Examples: http://www.devhut.com/index.php
Please rate this post using the vote buttons if it was helpful.

what if i dont want to bass my self on the curent date because i can
create the record a few days in advance.
thanks for the quik responce

Tired of entering one date a month, wow! lol Sorry I couldn't help myself.

using some simple vba you can easily determine the current day month
year.... assuming you wish to base it on today's date?!

date() = today's date
Year(date()) = the year of the entered date
Month(date()) = month of the entered date
Day(Date()) = day of the entered date
Format() permits you to output an input variable as you need it.

Using the Format() we can tranform any date into the format we want. so
Format(Date(),"mmm yyyy") would return

May 2008 (for today's date)

So you can use Format(Date(),"mmm yyyy") and pass it's value to the conrol
on your form where you have to enter you date.

im trying to find a way to create a primary key that will auto increment in
vba and it needs to be the month and year only so you would have January 2008

[quoted text clipped - 3 lines]

thank in advance

 

[Balmora via AccessMonster.com]

oh! ok thanks i will try it

Well then you can use the dateadd() to add a redefined number of day to the
current date. Basically, you need to come up with some rule to base the
automate date on and from the the Format function, as mentioned earlier, will
do the rest.

Try something like
format(dateadd("d",7,date()),"mmmm yyyy")

the 7 is the number of day to add to the current date, change it to whatever
suits your needs.

what if i dont want to bass my self on the curent date because i can
create the record a few days in advance.

[quoted text clipped - 23 lines]

 

[Balmora via AccessMonster.com]

Well, nothing works. i will try too explan it ai simple as i can lets say i
have one table named MyTable and in my table i have only mmmmyyyyID i want to
call it that way because i want monthe in full and year in 4 digits so it
looks like May 2008 lets say i guess i can always right the first id by hand
so it can start by their and i was thinking that maybe i can put the vba code
in the next record command button that i coded this way

Private Sub cmdNewRec_Click()
Me.AllowAdditions = True
DoCmd.GoToRecord , , acNewRec
End Sub

as you can see i put Me.AllowAdditions = True because this is the only button
that can add a new record so to start off from their i was wondering if i
should create a table called month and have like Id and Month and insert all
12 month of the year and then some how have the code in the cmdNewRec button
go thow all the 12 records to display the month and when it gets to the end
it would increment the year by one and then start over. maby use some format()
and some Dlookup in the code i realy dont know but this is how i see it.

do you think its possible. to be used as a primary key i know its not the
best way to go but this is what i need. i see this as a realy good learning
experience. thank you in advance for all your help.

Tired of entering one date a month, wow! lol Sorry I couldn't help myself.

using some simple vba you can easily determine the current day month
year.... assuming you wish to base it on today's date?!

date() = today's date
Year(date()) = the year of the entered date
Month(date()) = month of the entered date
Day(Date()) = day of the entered date
Format() permits you to output an input variable as you need it.

Using the Format() we can tranform any date into the format we want. so
Format(Date(),"mmm yyyy") would return

May 2008 (for today's date)

So you can use Format(Date(),"mmm yyyy") and pass it's value to the conrol
on your form where you have to enter you date.

im trying to find a way to create a primary key that will auto increment in
vba and it needs to be the month and year only so you would have January 2008

[quoted text clipped - 3 lines]

thank in advance

 

[Balmora via AccessMonster.com]

oh!
i thought this over and i guess i can keep a autonumber for the primery key i
realy just need the month and year to increment automaticaly so a month is
never skiped because i then make a time stamp of it for some filtering. i
know im making some happy by not using it for a primery key hope to here
from you all soon

Well, nothing works. i will try too explan it ai simple as i can lets say i
have one table named MyTable and in my table i have only mmmmyyyyID i want to
call it that way because i want monthe in full and year in 4 digits so it
looks like May 2008 lets say i guess i can always right the first id by hand
so it can start by their and i was thinking that maybe i can put the vba code
in the next record command button that i coded this way

Private Sub cmdNewRec_Click()
Me.AllowAdditions = True
DoCmd.GoToRecord , , acNewRec
End Sub

as you can see i put Me.AllowAdditions = True because this is the only button
that can add a new record so to start off from their i was wondering if i
should create a table called month and have like Id and Month and insert all
12 month of the year and then some how have the code in the cmdNewRec button
go thow all the 12 records to display the month and when it gets to the end
it would increment the year by one and then start over. maby use some format()
and some Dlookup in the code i realy dont know but this is how i see it.

do you think its possible. to be used as a primary key i know its not the
best way to go but this is what i need. i see this as a realy good learning
experience. thank you in advance for all your help.

Tired of entering one date a month, wow! lol Sorry I couldn't help myself.

[quoted text clipped - 19 lines]