Results 1 to 5 of 5
  1. #1
    Star Lounger
    Join Date
    May 2006
    Posts
    51
    Thanks
    0
    Thanked 0 Times in 0 Posts
    This should be a simple query. I have been away from Access for a while, but this should not be stumping me.

    Here is the query:
    BestAddress: IIf([POBox]<>"","PO Box" & [POBox],[Address])
    All records come back with #Error on the records with a PO Box. The PO Box field is a number field.

    So I have tried the following with similar results:
    BestAddress: IIf([POBox]="",[Address],"PO Box ")
    BestAddress: IIf([POBox]="","Address","PO Box")
    BestAddress: IIf([POBox]="","Address","PO BOX ")


    So I tried this one:
    BestAddress: IIf([POBox]<>"","PO Box","Address")
    This results in all records with no PO Box containing "Address", and all records with a PO Box containing #Error.

    And this one:
    BestAddress: IIf([POBox]<>"","PO Box",[Address])
    This results in all records with no PO Box containing the real Address value, and all records with a PO Box containing #Error.

    And this one:
    BestAddress: IIf([POBox]<>"",[POBox],[Address])
    This results in all records with no PO Box containing the real Address value, and all records with a PO Box containing #Error.

    It seems that whenever the result of IIF is True, I get an #Error. The action for FALSE works OK.

    What basic logic/syntax error have I been making?

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,435
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    What is the Data Type of the POBox field? If it is numeric the test for "" won't work.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    Star Lounger
    Join Date
    May 2006
    Posts
    51
    Thanks
    0
    Thanked 0 Times in 0 Posts
    AAArgh!

    RetiredGeek, you got it. At the top of my posting I noted that the field was a number.

    Changed the query to this:
    BestAddress: IIf([POBox]>0,"PO BOX " & [POBox],[Address])

    Works perfectly. Thanks.....
    I will probably be be asking some more basics until I get back up to speed again.

  4. #4
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    You should only use numbers for fields if you want to capacity to perform arithmetic with them. Text fields provides much greater flexibility. In my country some PO Boxes have letters in them (for example) PO BOX 1234A.
    So the other solution to your original problem is just to convert the field to text.
    Then run an update query to set any PO box values of 0 to Null.
    Regards
    John



  5. #5
    Star Lounger
    Join Date
    May 2006
    Posts
    51
    Thanks
    0
    Thanked 0 Times in 0 Posts
    You said: "You should only use numbers for fields if you want to capacity to perform arithmetic with them"

    I agree completely. I just inherited this database from a company who has worked for years without "a guy who used to work here" that designed the application. They contacted me initially because they needed to change some reports. I had nothing to do with the design, and am looking forward to many more unexpected issues. It looks like I will have a lot of homework reviewing all field specs in all tables, etc.

    Thanks again for your support and advice.

Posting Permissions

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