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

Convert Access Expression to VBA

P: 6
Expand|Select|Wrap|Line Numbers
  1. GainRecDistr: IIf([BegTaxBasis]=0 And [Contribution]+[Distribution]=0,0,IIf([BegTaxBasis]=0 And [TaxIncSubTotal]=0,-[Distribution],IIf([Distribution]=0,0,IIf([TBBLL]>0,0,IIf([TBBLL]+[Recourse]+[QualifiedNonrecourse]+[NonRecourse]<[Distribution],-[Distribution],IIf([TBBLL]+[Recourse]+[QualifiedNonrecourse]+[NonRecourse]>[Distribution] And [TBBLL]+[Recourse]+[QualifiedNonrecourse]+[NonRecourse]<0 And [TaxIncSubTotal]<0,[TBBLL]+[Recourse]+[QualifiedNonrecourse]+[NonRecourse]-[TaxIncSubTotal],[TBBLL]+[Recourse]+[QualifiedNonrecourse]+[NonRecourse]))))))
when I try to calculate the above in access expression it gives me an error message saying query is too complex specifically the problem is with this section:
Expand|Select|Wrap|Line Numbers
  1. IIf([TBBLL]+[Recourse]+[QualifiedNonrecourse]+[NonRecourse]>[Distribution] And [TBBLL]+[Recourse]+[QualifiedNonrecourse]+[NonRecourse]<0 And [TaxIncSubTotal]<0,[TBBLL]+[Recourse]+[QualifiedNonrecourse]+[NonRecourse]-[TaxIncSubTotal],[TBBLL]+[Recourse]+[QualifiedNonrecourse]+[NonRecourse]))))))
How can I convert this into a VBA expression? Any ideas much appreciated?
May 23 '17 #1
Share this Question
Share on Google+
4 Replies


jforbes
Expert 100+
P: 1,107
The following is the basic structure I use for a Function that you want it callable from a Query:

Expand|Select|Wrap|Line Numbers
  1.     'getSaveState
  2. Public Function getSaveState(sUser As Variant, sObject As Variant) As Variant
  3.     On Error GoTo ErrorOut
  4.  
  5.     getSaveState = Nz(dlookup("State", "SaveState", "UserID='" & NZ(sUser, "") & "' AND Object='" & NZ(sObject, "") & "'"), "Default")
  6.  
  7. ExitOut:
  8.     Exit Function
  9.  
  10. ErrorOut:
  11.     Msg
  12.     Resume ExitOut
  13. End Function
This basic structure has Variants for Inputs and Outputs as they work best to get past data type conversion problems. There is a NZ() around any of the parameters that are used. If you are going to use the parameter in more than one place, it's better to setup a variable for it and use the variable.

This is a completely made up function to show how to return a result based on values supplied from a Query. It may have syntax errors:
Expand|Select|Wrap|Line Numbers
  1.     'getSaveState
  2. Public Function getSaveState(Parm1 As Variant, Parm3 As Variant, Parm3 As Variant, Parm4 As Variant) As Variant
  3.     On Error GoTo ErrorOut
  4.  
  5.     Dim sParm1 AS String
  6.     Dim sParm2 AS String
  7.     Dim sParm3 AS String
  8.     Dim sParm4 AS String
  9.  
  10.     sParm1 = NZ(sParm1, "")
  11.     sParm2 = NZ(sParm2, "")
  12.     sParm3 = NZ(sParm3, "")
  13.     sParm3 = NZ(sParm3, "")
  14.  
  15.     IF sParm1 = sParm3 THEN
  16.         getSaveState = 1
  17.     ELSEIF sParm1 = "" and sParm2 = "TOP" THEN
  18.         getSaveState = 2
  19.     ELSEIF sParm3 = sParm4 THEN
  20.         getSaveState = 3
  21.     END IF
  22.  
  23. ExitOut:
  24.     Exit Function
  25.  
  26. ErrorOut:
  27.     Msg
  28.     Resume ExitOut
  29. End Function
May 24 '17 #2

NeoPa
Expert Mod 15k+
P: 31,418
Hi Jane.

It doesn't make sense to ask that question. Before you think of asking for help converting SQL to VBA you'd have to make sure you had the SQL working adequately. You haven't, so it's not appropriate or fair to ask for help to convert it. The GIGO law would apply.

Nor would it make sense for you to. It only complicates further your already complicated position. What you really want is for some logic to be implemented from scratch in VBA. That's far easier, but it does require you first to express your logic clearly and precisely as part of the request.

Also, please note that whenever posting code of any form you are obliged to use the [CODE] markers so that it can be read more easily.
May 25 '17 #3

P: 6
Well it is an access expression formula and the formula is working properly. However Access returns a query is too complex error due to the multiple IIfs in the expression. So I was trying to figure out how I can convert the expression into VBA perhaps using a case statement?
May 25 '17 #4

NeoPa
Expert Mod 15k+
P: 31,418
Hi Jane.

I don't know how best to understand your post. You seem to be saying that the formula works, yet returns an error message when run. I can't see how those two statements aren't mutually contradictory.

Regardless of where that all ends up, requesting help to convert a complex formula, especially one where the results can't be confirmed, is not where you should be going if you'll take my advice. If you make the effort to explain the logic in clear English and request help converting that to VBA then you won't regret it. Obviously, that relies heavily on the clear English part.
May 26 '17 #5

Post your reply

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