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

Incorrect Expression

100+
P: 166
Hello:

I would like to automatically calculate the time an employees' benefits will begin, which is 90 days after the hire date. I have this expression:
Expand|Select|Wrap|Line Numbers
  1. =IIf(IsNull([HireDt]),Null,IIf(Day([HireDt]+90)=1,[HireDt]+90,DateSerial(Year([HireDt]+90),Month([HireDt]+90)+1,1)))
It does not produce the correct results.

For example: I have a hire date 1/4/2005...it says the benefits start 5/1/05, as opposed to 4/4/05.

What's wrong with my expression?
Feb 13 '09 #1
Share this Question
Share on Google+
13 Replies


NeoPa
Expert Mod 15k+
P: 31,419
As a quick response - I would say consider using DateAdd().
Feb 13 '09 #2

100+
P: 166
Thanks! that worked great
Feb 13 '09 #3

DonRayner
Expert 100+
P: 489
Try it like this.
Expand|Select|Wrap|Line Numbers
  1. =IIf(IsNull([HireDt]),Null,DateAdd("d",90,[HireDt])
Never Mind. NeoPa answerd it.
Feb 13 '09 #4

100+
P: 166
Actually, I ended up using just what you suggested, except I incremented by the month as opposed to the day.

thanks Don!
Feb 13 '09 #5

NeoPa
Expert Mod 15k+
P: 31,419
You say you want to add 90 days, yet your code doesn't do this. Assuming you want to return Null if [HireDt] is null, try :
Expand|Select|Wrap|Line Numbers
  1. =[HireDt]+90
or
Expand|Select|Wrap|Line Numbers
  1. =DateAdd("d",90,[HireDt])
The Null should perpetuate anyway.

PS. Please note the edit comment by your post. This is not optional. As a full member we expect you to follow the site rules.
Feb 13 '09 #6

100+
P: 166
"PS. Please note the edit comment by your post. This is not optional. As a full member we expect you to follow the site rules. "

I note it, but as a full member, I've never known how to do this...it would have been helpful if you had said how to do this.
Feb 13 '09 #7

NeoPa
Expert Mod 15k+
P: 31,419
You've been here 11 months and made 91 posts to date, and have never bothered to check out the Help section? Remarkable. All you ask and more is there available for you to read any time.

OK. There is a link to the Help section on every page of this site. If you go there, you will see a section called Posting Guidelines. We expect members to abide by these guidelines, but will always issue a warning (with direct links) before taking any punitive action. I'm sure you can appreciate that taking every member through them by the hand is not a practical possibility (although I've never seen any of the moderators fail to be very helpful when asked).

Particularly, for ensuring you use the [ CODE ] tags, there is a toolbar type of thing in the posting window (looks like a hash (#)) which will place the relevant tags around any selected text. Tooltips over each button explains what they do for you.

This is not found in the QuickReply window at the bottom of every thread, nor in the Basic Editor window if you have that selected in your profile. If you choose to use either of these options, then you are responsible for formatting the reply yourself. I can help you to select the other (more fully featured) editor if that is a problem for you.

-Administrator.
Feb 13 '09 #8

100+
P: 166
"You've been here 11 months and made 91 posts to date, and have never bothered to check out the Help section?"

--Nope, never needed to...didn't know there was an issue
Expand|Select|Wrap|Line Numbers
  1. Thanks for the link
Feb 16 '09 #9

NeoPa
Expert Mod 15k+
P: 31,419
@csolomon
Yet you don't seem at all embarrassed to post this. You complain something is missing, without even checking to see if it's there. Impressive.
Feb 16 '09 #10

100+
P: 166
Actually, a poster edited my work before and asked me to in the future use the tags but I could never figure out how to do it...I thought I had to select something to do it Who know it was as simple as [code]...[\code] so I appreciate your pointing to it...Am I embarrassed, no, just glad I finally know how to do it so that it doesn't irritate anyone else.
Feb 16 '09 #11

NeoPa
Expert Mod 15k+
P: 31,419
You shouldn't need to have to type them out manually though. here's something I just prepared to clarify things.

Tags are done as matching pairs where the opening one is surrounded by [...] and the closing one by [/...]. A set of buttons is available for ease of use in the Standard Editor (Not the Basic Editor). The one for the [ CODE ] tags has a hash (#) on it. You can choose which editor to use in your profile options (Look near the bottom of the page).
Feb 16 '09 #12

100+
P: 166
Expand|Select|Wrap|Line Numbers
  1. AHHHHHHHHHHH...
  2. So I found the hash and I made some changes to my profile...
  3. which I never paid attention to before...
  4. I just started asking for help!
  5. Thank you for getting me on track.
Feb 16 '09 #13

NeoPa
Expert Mod 15k+
P: 31,419
That's fine. When I realised what you needed to know I was happy to help.
Feb 16 '09 #14

Post your reply

Sign in to post your reply or Sign up for a free account.