435,294 Members | 2,681 Online
Need help? Post your question and get tips & solutions from a community of 435,294 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 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))))) Jan 6 '14 #1

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 Replies

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

 Expert 5K+ P: 8,638 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

 Expert Mod 10K+ P: 12,366 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 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]));   Jan 6 '14 #6

 Expert Mod 10K+ P: 12,366 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

 Expert Mod 10K+ P: 12,366 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

 Expert Mod 10K+ P: 12,366 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