+ Reply to Thread
Results 1 to 6 of 6

Convert Access Expression into VBA

  1. 05-23-2017, 04:43 PM #1
    carissa80
    carissa80 is offline
    Registered User
    Join Date
    08-07-2016
    Location
    ny
    MS-Off Ver
    2013
    Posts
    1

    Wink Convert Access Expression into VBA

    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:

    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?
    Reply With QuoteRegister To Reply
  2. 05-24-2017, 01:24 PM #2
    dflak
    dflak is offline
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,920

    Re: Convert Access Expression into VBA

    First of all, there is no iif in VBA. It’s just plain if. Secondly you have to work with a range. You are using Access Column Headers. Now if they are actually excel table headers then you can work with them

    So this:
    Please Login or Register  to view this content.
    Becomes this:
    Please Login or Register  to view this content.
    One spreadsheet to rule them all. One spreadsheet to find them. One spreadsheet to bring them all and at corporate, bind them.

    A picture is worth a thousand words, but a sample spreadsheet is more likely to be worked on.
    Reply With QuoteRegister To Reply
  3. 05-24-2017, 01:27 PM #3
    Bernie Deitrick
    Bernie Deitrick is offline
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 365 (Windows)
    Posts
    5,937

    Re: Convert Access Expression into VBA

    Quote Originally Posted by dflak View Post
    First of all, there is no iif in VBA.
    That'll be news to everybody who uses it:

    b = False
    MsgBox IIf(b, "b is True", "b is false")
    Bernie Deitrick
    Excel MVP 2000-2010
    Reply With QuoteRegister To Reply
  4. 05-24-2017, 01:40 PM #4
    Bernie Deitrick
    Bernie Deitrick is offline
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 365 (Windows)
    Posts
    5,937

    Re: Convert Access Expression into VBA

    You might be able to shorten it, or figure out what specific part is causing the issue by using code like
    Please Login or Register  to view this content.
    Last edited by alansidman; 05-26-2017 at 10:52 AM. Reason: code tags added. Please don''t forget in the future.
    Reply With QuoteRegister To Reply
  5. 05-25-2017, 07:24 PM #5
    carissa7
    carissa7 is offline
    Registered User
    Join Date
    03-07-2016
    Location
    USA
    MS-Off Ver
    ms office 2013
    Posts
    25

    Re: Convert Access Expression into VBA

    oh wow that is really helpful Bernie! Can you help me do a similar thing for the first part, I want to convert it into VBA using If
    statements:

    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]))))))
    Reply With QuoteRegister To Reply
  6. 05-26-2017, 09:49 AM #6
    Bernie Deitrick
    Bernie Deitrick is offline
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 365 (Windows)
    Posts
    5,937

    Re: Convert Access Expression into VBA

    Well, the first thing is your [NameOfRange] - are those single cells, or multiple cells, like a column of cells that you want to process row by row, where each named range has a corresponding value on that row? In Excel VBA, if the named range is a single cell, then it can be used just like that [NameOfRange] - which would be the same as using Range("NameOfRange").Value But if the named range is multiple-cell, then the code needs to be changed to 
    Please Login or Register  to view this content.
    where i is used to index through the values.
    Last edited by alansidman; 05-26-2017 at 10:53 AM.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Access Expression Error
    By jeepjenn in forum Access Tables & Databases
    Replies: 2
    Last Post: 04-23-2015, 01:48 PM
  2. Convert to Access
    By SuddenImpact in forum Excel General
    Replies: 1
    Last Post: 07-11-2014, 06:41 AM
  3. Need help trying to create Sumif expression in Access
    By delpiero5464 in forum Access Tables & Databases
    Replies: 1
    Last Post: 05-22-2013, 03:12 PM
  4. Expression for Sequential Numbering in An Access Query
    By mcranda in forum Access Tables & Databases
    Replies: 1
    Last Post: 07-05-2012, 06:11 PM
  5. Excel 2007 : Convert Numerical Expression to Number
    By suresh968 in forum Excel General
    Replies: 1
    Last Post: 03-15-2012, 09:49 AM
  6. How to Convert Access 1.x to access 2003 or 2010
    By jambai in forum Access Programming / VBA / Macros
    Replies: 1
    Last Post: 03-02-2011, 07:02 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1