Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2004

    Unanswered: Query with iif statement to filter results

    I have a query w/ a table for buildings and one with tenants...I have certain requirements for people to qualify for different units and here is how it goes...

    Unit Size: Household Size:
    SRO Unit 0 1
    Studio .5 1,2
    Bedroom 1 1,2,3
    Bedroom 2 1,2,3,4,5
    Bedroom 3 1,2,3,4,5,6,7

    so essentially, the maximum amount of people that can live in a unit is [HouseholdSize]*2 + 1 EXCEPT for the SRO and Studio units.

    I've tried a nested IIf statement like this: IIf([apInfoHouseholdSize] = 1 AND [SROCheck] = True, [UnitSize] = 0, IIf([apInfoHouseholdSize] = 1 AND [StudioCheck] = True, [UnitSize] = [apInfoHouseholdSize]*2, IIf([apInfoHouseholdSize]>1, [UnitSize] <= [apInfoHouseholdSize]*2+1 AND > .5)

    Any help is greatly appreciated, been beating my head over this for a while now...

  2. #2
    Join Date
    Oct 2004
    I also tried going about it in the reverse order...but still doesn't work...

    IIf([apInfoHouseholdSize]>5 And [Forms]![frmApplicants]![SROCheck]=False And [Forms]![frmApplicants]![StudioCheck]=False,=3,IIf([apInfoHouseholdSize]>3 And [Forms]![frmApplicants]![SROCheck]=False And [Forms]![frmApplicants]![StudioCheck]=False,>1,IIf([apInfoHouseholdSize]>2 And [Forms]![frmApplicants]![SROCheck]=False And [Forms]![frmApplicants]![StudioCheck]=False,>0.5,IIf([apInfoHouseholdSize]>1 And [Forms]![frmApplicants]![SROCheck]=False And [Forms]![frmApplicants]![StudioCheck]=False,Between 0.4 And 1.1,IIf([apInfoHouseholdSize]>1 And [Forms]![frmApplicants]![SROCheck]=False And [Forms]![frmApplicants]![StudioCheck]=True,0.5,IIf([apInfoHouseholdSize]=1 And [Forms]![frmApplicants]![SROCheck]=True And [Forms]![frmApplicants]![StudioCheck]=False,0,<=[apInfoHouseholdSize]*2+1))))))

  3. #3
    Join Date
    Sep 2003
    And now what is the question you are asking????
    Back to Access ... ADO is not the way to go for speed ...

Posting Permissions

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