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

Update Query not updating records

100+
P: 294
I am really clueless as to why my query isn't working. (Don't take that as a confession of not doing research and Googling) Its' purpose is to calculate remaining units someone is allowed to keep - depending on when they were given and when they are separating. (The conditions are attempted to be replicated in the SQL)


Does anyone see something that would be an immediate red flag?

Expand|Select|Wrap|Line Numbers
  1.         UPDATE AwardTbl SET AwardTbl.VestingDate = Date(), 
  2.         AwardTbl.ForfeitedUnits = IIf(CalcExactDiff([AwardTbl]![AwardDate])<1,[AwardTbl]![AwardUnits],
  3.         IIf(CalcExactDiff([AwardTbl]![AwardDate])<2,0.8*[AwardTbl]![AwardUnits],
  4.         IIf(CalcExactDiff([AwardTbl]![AwardDate])<3,0.6*[AwardTbl]![AwardUnits],
  5.         IIf(CalcExactDiff([AwardTbl]![AwardDate])<4,0.4*[AwardTbl]![AwardUnits],
  6.         IIf(CalcExactDiff([AwardTbl]![AwardDate])<5,0.2*[AwardTbl]![AwardUnits],0))))), 
  7.         AwardTbl.SpecialVestedUnits = IIf(CalcExactDiff([AwardTbl]![AwardDate],[AssociateTbl]![SeparationDate])<1,0,
  8.         IIf(CalcExactDiff([AwardTbl]![AwardDate],[AssociateTbl]![SeparationDate])<2,0.2*[AwardTbl]![AwardUnits],
  9.         IIf(CalcExactDiff([AwardTbl]![AwardDate],[AssociateTbl]![SeparationDate])<3,0.4*[AwardTbl]![AwardUnits],
  10.         IIf(CalcExactDiff([AwardTbl]![AwardDate],[AssociateTbl]![SeparationDate])<4,0.6*[AwardTbl]![AwardUnits],
  11.         IIf(CalcExactDiff([AwardTbl]![AwardDate],[AssociateTbl]![SeparationDate])<5,0.8*[AwardTbl]![AwardUnits],[AwardTbl]![AwardUnits]))))), AwardTbl.ForfeitDate = Date();
The 'CalcExactDiff' is simply finding the exact difference of 2 dates and is a function in the VBA code.
Jan 16 '14 #1
Share this Question
Share on Google+
1 Reply


100+
P: 294
I figured it out. There were missing fields that I simply overlooked.

Expand|Select|Wrap|Line Numbers
  1.         IIf(CalcExactDiff([AwardTbl]![AwardDate],[Forms]![AstProfileAwardFrm]![SepDateTxt])<1,0,
  2.         IIf(CalcExactDiff([AwardTbl]![AwardDate],[Forms]![AstProfileAwardFrm]![SepDateTxt])<2,0.2*([AwardTbl]![AwardUnits]-[AwardTbl]![ForfeitedUnits]),
  3.         IIf(CalcExactDiff([AwardTbl]![AwardDate],[Forms]![AstProfileAwardFrm]![SepDateTxt])<3,0.4*([AwardTbl]![AwardUnits]-[AwardTbl]![ForfeitedUnits]),
  4.         IIf(CalcExactDiff([AwardTbl]![AwardDate],[Forms]![AstProfileAwardFrm]![SepDateTxt])<4,0.6*([AwardTbl]![AwardUnits]-[AwardTbl]![ForfeitedUnits]),
  5.         IIf(CalcExactDiff([AwardTbl]![AwardDate],[Forms]![AstProfileAwardFrm]![SepDateTxt])<5,0.8*([AwardTbl]![AwardUnits]-[AwardTbl]![ForfeitedUnits]),
  6.         [AwardTbl]![AwardUnits]-)))))
And here's the other calculation:
Expand|Select|Wrap|Line Numbers
  1.         IIf(CalcExactDiff([AwardTbl]![AwardDate])<1,[AwardTbl]![AwardUnits],
  2.         IIf(CalcExactDiff([AwardTbl]![AwardDate])<2,(0.8*([AwardTbl]![AwardUnits]-[AwardTbl]![ForfeitedUnits]-[AwardTbl]![PaidOutUnits]))+[AwardTbl]![ForfeitedUnits],
  3.         IIf(CalcExactDiff([AwardTbl]![AwardDate])<3,(0.6*([AwardTbl]![AwardUnits]-[AwardTbl]![ForfeitedUnits]-[AwardTbl]![PaidOutUnits]))+[AwardTbl]![ForfeitedUnits],
  4.         IIf(CalcExactDiff([AwardTbl]![AwardDate])<4,(0.4*([AwardTbl]![AwardUnits]-[AwardTbl]![ForfeitedUnits]-[AwardTbl]![PaidOutUnits]))+[AwardTbl]![ForfeitedUnits],
  5.         IIf(CalcExactDiff([AwardTbl]![AwardDate])<5,(0.2*([AwardTbl]![AwardUnits]-[AwardTbl]![ForfeitedUnits]-[AwardTbl]![PaidOutUnits]))+[AwardTbl]![ForfeitedUnits],
  6.         [AwardTbl]![ForfeitedUnits])))))
Jan 17 '14 #2

Post your reply

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