I've a module that calculates the exact difference between two dates, from Allen Browne (CalcExactDiff). I am wondering if it's possible to use that in QBE under the "Criteria" for an Update query.
Here's an example of what I am talking about : - IIf(CalcExactDiff([WhatIfAwdPayoutTbl]![WhatIfAwardDate],[Forms]![AstProfileFrm]![AstProfileWhatIfSbfrm].[Form]![SepDateInputTxt])<1,[AwardTbl]![AwardUnits],
-
IIf(CalcExactDiff([WhatIfAwdPayoutTbl]![WhatIfAwardDate],[Forms]![AstProfileFrm]![AstProfileWhatIfSbfrm].[Form]![SepDateInputTxt])<2,0.8*[AwardTbl]![AwardUnits],
-
IIf(CalcExactDiff([WhatIfAwdPayoutTbl]![WhatIfAwardDate],[Forms]![AstProfileFrm]![AstProfileWhatIfSbfrm].[Form]![SepDateInputTxt])<3,0.6*[AwardTbl]![AwardUnits],
-
IIf(CalcExactDiff([WhatIfAwdPayoutTbl]![WhatIfAwardDate],[Forms]![AstProfileFrm]![AstProfileWhatIfSbfrm].[Form]![SepDateInputTxt])<4,0.4*[AwardTbl]![AwardUnits],
-
IIf(CalcExactDiff([WhatIfAwdPayoutTbl]![WhatIfAwardDate],[Forms]![AstProfileFrm]![AstProfileWhatIfSbfrm].[Form]![SepDateInputTxt])<5,0.2*[AwardTbl]![AwardUnits],0)))))
There's the problem, you are trying to pull a value from a table that isn't referenced in the source. How is it supposed to know which row to use from that table?
11 1091
What happens when you try it?
Why not simply set the Update to Row of [AwardUnits] to CalcExactDiff() with all non-relevant Return Values simply returning the Original Value of [AwardUnits]. Mr. Browne's Code may have to be slightly modified in order to achieve this, but I'm sure he will not mind (LOL).
It asks for AwardUnits when I try it
What is the full SQL? It sounds like it can't find this field you're referencing.
There's a lot to it.. - UPDATE WhatIfAwdPayoutTbl SET WhatIfAwdPayoutTbl.WhatIfVestingDate = [Forms]![AstProfileFrm]![AstProfileWhatIfSbfrm].[Form]![SepDateInputTxt], WhatIfAwdPayoutTbl.WhatIfForfeitedUnits = IIf(CalcExactDiff([WhatIfAwdPayoutTbl]![WhatIfAwardDate],[Forms]![AstProfileFrm]![AstProfileWhatIfSbfrm].[Form]![SepDateInputTxt])<1,[AwardTbl]![AwardUnits],IIf(CalcExactDiff([WhatIfAwdPayoutTbl]![WhatIfAwardDate],[Forms]![AstProfileFrm]![AstProfileWhatIfSbfrm].[Form]![SepDateInputTxt])<2,0.8*[AwardTbl]![AwardUnits],IIf(CalcExactDiff([WhatIfAwdPayoutTbl]![WhatIfAwardDate],[Forms]![AstProfileFrm]![AstProfileWhatIfSbfrm].[Form]![SepDateInputTxt])<3,0.6*[AwardTbl]![AwardUnits],IIf(CalcExactDiff([WhatIfAwdPayoutTbl]![WhatIfAwardDate],[Forms]![AstProfileFrm]![AstProfileWhatIfSbfrm].[Form]![SepDateInputTxt])<4,0.4*[AwardTbl]![AwardUnits],IIf(CalcExactDiff([WhatIfAwdPayoutTbl]![WhatIfAwardDate],[Forms]![AstProfileFrm]![AstProfileWhatIfSbfrm].[Form]![SepDateInputTxt])<5,0.2*[AwardTbl]![AwardUnits],0))))), WhatIfAwdPayoutTbl.WhatIfSpecialVestedUnits = IIf(CalcExactDiff([WhatIfAwdPayoutTbl]![WhatIfAwardDate],[Forms]![AstProfileFrm]![AstProfileWhatIfSbfrm].[Form]![SepDateInputTxt])<1,0,IIf(CalcExactDiff([WhatIfAwdPayoutTbl]![WhatIfAwardDate],[Forms]![AstProfileFrm]![AstProfileWhatIfSbfrm].[Form]![SepDateInputTxt])<2,0.2*[AwardTbl]![AwardUnits],IIf(CalcExactDiff([WhatIfAwdPayoutTbl]![WhatIfAwardDate],[Forms]![AstProfileFrm]![AstProfileWhatIfSbfrm].[Form]![SepDateInputTxt])<3,0.4*[AwardTbl]![AwardUnits],IIf(CalcExactDiff([WhatIfAwdPayoutTbl]![WhatIfAwardDate],[Forms]![AstProfileFrm]![AstProfileWhatIfSbfrm].[Form]![SepDateInputTxt])<4,0.6*[AwardTbl]![AwardUnits],IIf(CalcExactDiff([WhatIfAwdPayoutTbl]![WhatIfAwardDate],[Forms]![AstProfileFrm]![AstProfileWhatIfSbfrm].[Form]![SepDateInputTxt])<5,0.8*[AwardTbl]![AwardUnits],[AwardTbl]![AwardUnits])))))
-
WHERE (((WhatIfAwdPayoutTbl.WhatIfVestingDate)>[Forms]![AstProfileFrm]![AstProfileWhatIfSbfrm].[Form]![SepDateInputTxt]) AND ((WhatIfAwdPayoutTbl.WhatIfEmployeeID)=[Forms]![AstProfileFrm]![EmployeeID]));
-
There's the problem, you are trying to pull a value from a table that isn't referenced in the source. How is it supposed to know which row to use from that table?
So you're basically saying I was missing the Award Table in my query?
Basically, yes. You need to bring it into your query in some way. What that way is depends on how the tables are related. That's as specific as I can get since you haven't told us how the tables are related.
It worked! Thanks for your help.
No problem, good luck with the rest of your project.
My next one will be in C#, so I'm sure I'll be back, haha. Thanks for your help
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Glenn Reed |
last post by:
Hi,
This is probably a really easy question and I apologize if it is already
covered in some faq somewhere.
These are my two source files:
------- module1.py -------------
class module2:...
|
by: |
last post by:
Hi,
This does sound like a bit of a weird thing to ask so I
will state my question and then further down explain why
I am trying to do it. So if you have the answer then
reply! Or if you...
|
by: Iyer, Prasad C |
last post by:
Actually I am bit confused between the modules and .py file
How do I differentiate between the 2.
For example
I have a file import1.py, import2.py file
Which has few functions and classes
And...
|
by: Mike |
last post by:
Hi,
This does sound like a bit of a weird thing to ask so I will state my
question and then further down explain why I am trying to do it. So
if you have the answer then reply! Or if you...
|
by: hastha23 |
last post by:
Dear Friends,
My oracle Version is 10g.
I calling a function from sql select same time function body contain DML statement,that time is possible call function from sql?
and again one,
...
|
by: beginner |
last post by:
Hi Everyone,
I have encountered a small problems. How to call module functions
inside class instance functions? For example, calling func1 in func2
resulted in a compiling error.
"my module...
|
by: Mitko Haralanov |
last post by:
I have a Python module that I have written using the C API and I am
having a problem accessing a dictionary from that module.
Here is what I have done:
1. In my init function I call
module =...
|
by: =?Utf-8?B?TWFzc2ltbw==?= |
last post by:
Is possible call the PostBack of an asp page by code? The result I want to
obtain is the itself one as the pressure of a button with "submit"! Thanks
|
by: Guoqi Zheng |
last post by:
Dear Sir,
I have an existing application done in ASP.NET(VB.NET), I need to change one
page of it. Because I am not able to recompile the code and upload the new
dll, I am thinking to use one...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: ryjfgjl |
last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
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: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
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...
| |