Results 1 to 5 of 5
  1. #1
    ham355 is offline Novice
    Windows XP Access 2007
    Join Date
    Feb 2012
    Posts
    11

    Exclamation Help please! iif not working in query!

    Hi all, thanks for taking the time to read this:



    I am currently trying to run:

    BudgetMonth1: IIf(([Front Loaded?]="Yes") And ([MAIN BUDGET FOR YEAR]>" "),Round ([MAIN BUDGET FOR YEAR]/6),0,"")

    However, it's not working, please could someone spare me 5 mins to see what i'm doing wrong?

    I am learning access as I go, and prefer to use the expression builder at this stage!

    Thanks very much

    Ian

  2. #2
    alansidman's Avatar
    alansidman is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Apr 2010
    Location
    Steamboat Springs
    Posts
    2,529
    ([MAIN BUDGET FOR YEAR]>" ")
    I think your issue might be here. You are asking Access to determine that a value is greater than an empty string. It can't do that. It is a mismatch of formats. Values and Strings.

    Tell us in simple English what you are trying to do here and perhaps we can give you a solution.

    It also appears that the syntax for your IIF statement is wrong. Two many arguments.

    Syntax is =IIF(Criteria, Answer if True, Answer if False)

    You have an extra argument in your statement.

    Alan
    Last edited by alansidman; 02-09-2012 at 07:15 AM. Reason: IIF statement arguments.

  3. #3
    ham355 is offline Novice
    Windows XP Access 2007
    Join Date
    Feb 2012
    Posts
    11
    Hi Alan, thanks....

    Basically, I am trying to say that if front loaded equals yes (which is controlled by a combo box on a form) and the main budget is greater than zero, then divide the main budget by 6, otherwise show nothing.

    Regards, Ian

  4. #4
    alansidman's Avatar
    alansidman is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Apr 2010
    Location
    Steamboat Springs
    Posts
    2,529
    Try this:

    Code:
    IIF([Front Loaded?]="Yes" And [MAIN BUDGET FOR YEAR]> 0,Round([MAIN BUDGET FOR YEAR]/6,0),"")
    I just noticed also, that the parens at the of the round function was in the wrong place, and that is what confused Access and Me.

  5. #5
    ham355 is offline Novice
    Windows XP Access 2007
    Join Date
    Feb 2012
    Posts
    11
    Thanks very much Alan!

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Parameter Query not working
    By cphelps48 in forum Queries
    Replies: 3
    Last Post: 10-06-2011, 04:42 PM
  2. Run Query Button Not Working
    By SusanHRO in forum Forms
    Replies: 8
    Last Post: 08-15-2011, 10:33 AM
  3. parameter query not working
    By denny in forum Access
    Replies: 3
    Last Post: 02-20-2011, 01:07 PM
  4. IIf Update Query working OK but a better way?
    By dreamweaver547 in forum Queries
    Replies: 7
    Last Post: 04-19-2010, 09:47 AM
  5. Query is not working
    By pushpm in forum Programming
    Replies: 3
    Last Post: 04-14-2009, 07:16 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums