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

Is it possible to call a module in QBE?

100+
P: 294
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:

Expand|Select|Wrap|Line Numbers
  1. IIf(CalcExactDiff([WhatIfAwdPayoutTbl]![WhatIfAwardDate],[Forms]![AstProfileFrm]![AstProfileWhatIfSbfrm].[Form]![SepDateInputTxt])<1,[AwardTbl]![AwardUnits],
  2. IIf(CalcExactDiff([WhatIfAwdPayoutTbl]![WhatIfAwardDate],[Forms]![AstProfileFrm]![AstProfileWhatIfSbfrm].[Form]![SepDateInputTxt])<2,0.8*[AwardTbl]![AwardUnits],
  3. IIf(CalcExactDiff([WhatIfAwdPayoutTbl]![WhatIfAwardDate],[Forms]![AstProfileFrm]![AstProfileWhatIfSbfrm].[Form]![SepDateInputTxt])<3,0.6*[AwardTbl]![AwardUnits],
  4. IIf(CalcExactDiff([WhatIfAwdPayoutTbl]![WhatIfAwardDate],[Forms]![AstProfileFrm]![AstProfileWhatIfSbfrm].[Form]![SepDateInputTxt])<4,0.4*[AwardTbl]![AwardUnits],
  5. IIf(CalcExactDiff([WhatIfAwdPayoutTbl]![WhatIfAwardDate],[Forms]![AstProfileFrm]![AstProfileWhatIfSbfrm].[Form]![SepDateInputTxt])<5,0.2*[AwardTbl]![AwardUnits],0)))))
Jan 6 '14 #1

✓ answered by Rabbit

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?

Share this Question
Share on Google+
11 Replies


Rabbit
Expert Mod 10K+
P: 12,316
What happens when you try it?
Jan 6 '14 #2

ADezii
Expert 5K+
P: 8,601
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).
Jan 6 '14 #3

100+
P: 294
It asks for AwardUnits when I try it
Jan 6 '14 #4

Rabbit
Expert Mod 10K+
P: 12,316
What is the full SQL? It sounds like it can't find this field you're referencing.
Jan 6 '14 #5

100+
P: 294
There's a lot to it..

Expand|Select|Wrap|Line Numbers
  1. 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])))))
  2. WHERE (((WhatIfAwdPayoutTbl.WhatIfVestingDate)>[Forms]![AstProfileFrm]![AstProfileWhatIfSbfrm].[Form]![SepDateInputTxt]) AND ((WhatIfAwdPayoutTbl.WhatIfEmployeeID)=[Forms]![AstProfileFrm]![EmployeeID]));
  3.  
Jan 6 '14 #6

Rabbit
Expert Mod 10K+
P: 12,316
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?
Jan 6 '14 #7

100+
P: 294
So you're basically saying I was missing the Award Table in my query?
Jan 6 '14 #8

Rabbit
Expert Mod 10K+
P: 12,316
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.
Jan 6 '14 #9

100+
P: 294
It worked! Thanks for your help.
Jan 6 '14 #10

Rabbit
Expert Mod 10K+
P: 12,316
No problem, good luck with the rest of your project.
Jan 6 '14 #11

100+
P: 294
My next one will be in C#, so I'm sure I'll be back, haha. Thanks for your help
Jan 7 '14 #12

Post your reply

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