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? - UPDATE AwardTbl SET AwardTbl.VestingDate = Date(),
-
AwardTbl.ForfeitedUnits = IIf(CalcExactDiff([AwardTbl]![AwardDate])<1,[AwardTbl]![AwardUnits],
-
IIf(CalcExactDiff([AwardTbl]![AwardDate])<2,0.8*[AwardTbl]![AwardUnits],
-
IIf(CalcExactDiff([AwardTbl]![AwardDate])<3,0.6*[AwardTbl]![AwardUnits],
-
IIf(CalcExactDiff([AwardTbl]![AwardDate])<4,0.4*[AwardTbl]![AwardUnits],
-
IIf(CalcExactDiff([AwardTbl]![AwardDate])<5,0.2*[AwardTbl]![AwardUnits],0))))),
-
AwardTbl.SpecialVestedUnits = IIf(CalcExactDiff([AwardTbl]![AwardDate],[AssociateTbl]![SeparationDate])<1,0,
-
IIf(CalcExactDiff([AwardTbl]![AwardDate],[AssociateTbl]![SeparationDate])<2,0.2*[AwardTbl]![AwardUnits],
-
IIf(CalcExactDiff([AwardTbl]![AwardDate],[AssociateTbl]![SeparationDate])<3,0.4*[AwardTbl]![AwardUnits],
-
IIf(CalcExactDiff([AwardTbl]![AwardDate],[AssociateTbl]![SeparationDate])<4,0.6*[AwardTbl]![AwardUnits],
-
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.
1  1238 
I figured it out. There were missing fields that I simply overlooked. - IIf(CalcExactDiff([AwardTbl]![AwardDate],[Forms]![AstProfileAwardFrm]![SepDateTxt])<1,0,
-
IIf(CalcExactDiff([AwardTbl]![AwardDate],[Forms]![AstProfileAwardFrm]![SepDateTxt])<2,0.2*([AwardTbl]![AwardUnits]-[AwardTbl]![ForfeitedUnits]),
-
IIf(CalcExactDiff([AwardTbl]![AwardDate],[Forms]![AstProfileAwardFrm]![SepDateTxt])<3,0.4*([AwardTbl]![AwardUnits]-[AwardTbl]![ForfeitedUnits]),
-
IIf(CalcExactDiff([AwardTbl]![AwardDate],[Forms]![AstProfileAwardFrm]![SepDateTxt])<4,0.6*([AwardTbl]![AwardUnits]-[AwardTbl]![ForfeitedUnits]),
-
IIf(CalcExactDiff([AwardTbl]![AwardDate],[Forms]![AstProfileAwardFrm]![SepDateTxt])<5,0.8*([AwardTbl]![AwardUnits]-[AwardTbl]![ForfeitedUnits]),
-
[AwardTbl]![AwardUnits]-)))))
And here's the other calculation: - IIf(CalcExactDiff([AwardTbl]![AwardDate])<1,[AwardTbl]![AwardUnits],
-
IIf(CalcExactDiff([AwardTbl]![AwardDate])<2,(0.8*([AwardTbl]![AwardUnits]-[AwardTbl]![ForfeitedUnits]-[AwardTbl]![PaidOutUnits]))+[AwardTbl]![ForfeitedUnits],
-
IIf(CalcExactDiff([AwardTbl]![AwardDate])<3,(0.6*([AwardTbl]![AwardUnits]-[AwardTbl]![ForfeitedUnits]-[AwardTbl]![PaidOutUnits]))+[AwardTbl]![ForfeitedUnits],
-
IIf(CalcExactDiff([AwardTbl]![AwardDate])<4,(0.4*([AwardTbl]![AwardUnits]-[AwardTbl]![ForfeitedUnits]-[AwardTbl]![PaidOutUnits]))+[AwardTbl]![ForfeitedUnits],
-
IIf(CalcExactDiff([AwardTbl]![AwardDate])<5,(0.2*([AwardTbl]![AwardUnits]-[AwardTbl]![ForfeitedUnits]-[AwardTbl]![PaidOutUnits]))+[AwardTbl]![ForfeitedUnits],
-
[AwardTbl]![ForfeitedUnits])))))
Sign in to post your reply or Sign up for a free account.
Similar topics
by: JMCN |
last post by:
hi
i need some advice on whether if it would be better to use an append
query or an update query. here is the situation, i have linked another
database table to my current database. then i...
|
by: jlkass |
last post by:
Hello -
I am trying to execute an update query, updating 4 fields in one table.
Is there a way that I can enter the first value for each field, and
then serialize the first field for a given...
|
by: G Gerard |
last post by:
Hello
I am trying to update a table (TableB) using records
from a second table (TableA)
|
by: hharriel |
last post by:
Hi All,
I have created an update query related to high school course
information (name of course; credit hour; course description, etc.) I
am updating a master course information table. I am...
|
by: Dixie |
last post by:
I have asked this question before, but I could not get the suggested
solution work. So I will give more details this time.
I have an append query that adds several hundred records to a table...
|
by: sbowman |
last post by:
I ran the following update query in Sql Server 8.0:
update _SMDBA_._CUSTOMER_
set _SMDBA_._CUSTOMER_.client = dbo.results.adid
from _SMDBA_._CUSTOMER_, dbo.results
where...
|
by: M3lancholy |
last post by:
I've created a database in Microsoft Access 2003, Windows XP.
It updates based on: a date range, and various other field matches.
It takes this data from a form.
What I'm confused about is that...
|
by: najimou |
last post by:
Hi
I have a subform where the user make changes to several filtered records in view.
when clicking the updating button, I run the update query to perform some calculations and update the table...
|
by: gnortenjones |
last post by:
I have a linked table (to an oracle db), and I am trying to run a simple update query against it to change some data, but I am getting the following error:
"...didn't update 0 fields due to a type...
|
by: JKing |
last post by:
I'm using an update query to update a single field in a table. Table1 is part of my normalized table structure. Table2 is used solely as an import table for raw data which I sort and summarize...
|
by: taylorcarr |
last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: aa123db |
last post by:
Variable and constants
Use var or let for variables and const fror constants.
Var foo ='bar';
Let foo ='bar';const baz ='bar';
Functions
function $name$ ($parameters$) {
}
...
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
| |
