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

    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
    Posts
    5
    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
    Location
    MI
    Posts
    3,713
    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
  •