Results 1 to 13 of 13
  1. #1
    Join Date
    Mar 2012
    Posts
    22

    Unanswered: IIF query problems

    Hey guys I am having problems trying to get my IIF to work! I have been working it awhile now and I think I am truely over thinking it! I first tried:

    Expr4: IIf([State]="La","Parish","County")

    I want the county text box to show county if it is any other state besides La, and if it has La then have it marked as Parish in the same text box. A friend of mine sent me a CO-PA line but I have never used it before:

    CO-PA: IIf([Lease Counties]![STATE]="LA"," PARISH, "," COUNTY, ")

    What am I missing???

  2. #2
    Join Date
    Mar 2009
    Posts
    5,441
    Provided Answers: 14
    Where do you use the expression, and how?
    Have a nice day!

  3. #3
    Join Date
    Mar 2012
    Posts
    22
    I am trying to use it in a set of reports. So if I am creating legal information for a company that owns land in numerous states, Louisiana will not accept anything unless the Parish is marked as a Parish not county. It makes it a pain when most companies have a few properties within La!

  4. #4
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    Are you using this statement in a query or in VBA? The construct you're using is used only in a query. In VBA, it becomes
    Code:
    Expr4 = IIf([State] = "La","Parish","County")
    Sam

  5. #5
    Join Date
    Mar 2009
    Posts
    5,441
    Provided Answers: 14
    This does not answer the previous question. Do you try to use the expression in a query, in a VBA function, in the property of an object?

    - If it's in a query or in a vba proceduren please post the whole code of the query (SQL) or of the (VBA) procedure.

    - If it's in the property of an object (Textbox...) please post more information about this object and, possibly, it's parent, as well as the whole expression as it appears in the Property window.

    Explaining what does not work (error message and code, or unexpected results, or plain nothing) would help too.
    Have a nice day!

  6. #6
    Join Date
    Mar 2012
    Posts
    22
    Yes I was using this expression in the query, I am new to the IIF. If you have any quick reference to make a better sql statement for the IIF I would glady look at it!

    And the code I have currently does nothing but leave the blank textbox. No matter what state is selected.

  7. #7
    Join Date
    Mar 2009
    Posts
    5,441
    Provided Answers: 14
    You still do not post the SQL statement for the query!
    Have a nice day!

  8. #8
    Join Date
    Mar 2012
    Posts
    22
    IIf([Lease Counties]![STATE]="LA"," PARISH, "," COUNTY, ") AS [CO-PA]

    IIf([State]="La","Parish","County") AS Expr4

  9. #9
    Join Date
    Mar 2009
    Posts
    5,441
    Provided Answers: 14
    These are not valid SQL statements and can only be part of one: no SELECT, no FROM, no nothing, except what you posted in your original message.
    Have a nice day!

  10. #10
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    this sounds more like a user interface issue to me than a query...

    where are you intending using this
    why do you believe you need a query rather than some VBA inside a form or report

    if it is in a query then we do need to see the full query... switch the view to SQL videw and then copy and paste the whole statement including the select
    I'd rather be riding on the Tiger 800 or the Norton

  11. #11
    Join Date
    Jul 2012
    Posts
    8
    Is it possible that your [State] field does not write *exactly* "La" ? For example: "La " or " LA "? Try [State] LIKE "*La*" and let us know how it goes.

    BTW to post the full SQL statement: in the Design View, you'll see you can switch to a datasheet view, but one of the other options is to go to SQL view. That's where you can copy-paste the full SQL statement and let us have a look-see to help you out.

  12. #12
    Join Date
    Mar 2012
    Posts
    22
    The posted is what I got from the SQL view, and yes I had entered into the query as part of the expression. The reason for VBA is I am new and have not had the best of luck with VBA, but I am learning SQL now and trying to figure out how the IIF command can be brought over as an sql command when it seems it was more meant for the query interface.

  13. #13
    Join Date
    Jul 2012
    Posts
    8
    Yeah but you posted only part of your SQL statement, we need the whole SQL statement to make sense of it. At first glance, your IIF was correct, but it does not work. Therefore, maybe the problem is elsewhere in your SQL statement.

Posting Permissions

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