HELP WITH DATE EXPRESSION

[Kay]

I HAVE A BENEFITS DATABASE THAT INCLUDES A FIELD [BENEFITS ELIGIBILTY]. i
WANT TO SET THE DEFAULT VALUE. THE ELIGIBILTY DATE SHOULD BE 7 MONTHS
FOLLOWING THE [START DATE]

FOR EXAMPLE.

[FIRST NAME],[LAST NAME],[START DATE],[BENEFITS ELIGIBILTY]
JOHN DOE 07/02/2008 01/2008

I CANT EVEN GET PAST THE FIRST PART, MONTH([STARTDATE])+7 BECAUSE IF THE
MONTH IS 12, IT RETURNS WITH 19????

I ALSO NEED IT IN MM/YYYY FORMAT SO THAT I CAN QUERY IT LATER WITH DATE
EXPRESSIONS AND CRITERIA.

 

[Douglas J. Steele]

You can use Format(DateAdd("m", 7, [Start Date]), "m/yyyy"), but if you're
storing that in the table (you shouldn't, unless not all will be exactly 7
months), you'll have to store it in a text field: date fields can only
contain complete dates, and m/yyyy is not a complete date. Of course, you
won't be able to query it with date expressions later, since it won't be a
date.

Incidentally, when posting to the newsgroup, please toggle your Caps Lock
off. Posting in all upper case is the visual equivalent of shouting, and is
considered to be rude.

 

[Kay]

Thank you so much for your help.

I sincerely apologize for the caps as I utilize this site alot and
appreciate everyone’s help even if I am just reading other responses. I hope
there was no offense taken.

You can use Format(DateAdd("m", 7, [Start Date]), "m/yyyy"), but if you're
storing that in the table (you shouldn't, unless not all will be exactly 7
months), you'll have to store it in a text field: date fields can only
contain complete dates, and m/yyyy is not a complete date. Of course, you
won't be able to query it with date expressions later, since it won't be a
date.

Incidentally, when posting to the newsgroup, please toggle your Caps Lock
off. Posting in all upper case is the visual equivalent of shouting, and is
considered to be rude.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

I HAVE A BENEFITS DATABASE THAT INCLUDES A FIELD [BENEFITS ELIGIBILTY]. i
WANT TO SET THE DEFAULT VALUE. THE ELIGIBILTY DATE SHOULD BE 7 MONTHS
FOLLOWING THE [START DATE]

FOR EXAMPLE.

[FIRST NAME],[LAST NAME],[START DATE],[BENEFITS ELIGIBILTY]
JOHN DOE 07/02/2008 01/2008

I CANT EVEN GET PAST THE FIRST PART, MONTH([STARTDATE])+7 BECAUSE IF THE
MONTH IS 12, IT RETURNS WITH 19????

I ALSO NEED IT IN MM/YYYY FORMAT SO THAT I CAN QUERY IT LATER WITH DATE
EXPRESSIONS AND CRITERIA.