HELP WITH DATE EXPRESSION

  • Thread starter Thread starter Kay
  • Start date Start date
K

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.
 
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.
 
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.




Douglas J. Steele said:
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)


Kay said:
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.
Click to expand...
Click to expand...
 
Back
Top