Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2004
    Posts
    29

    Unanswered: Help writting a iif statment

    I have three tables of information named OESALESORDER, ARCUSTOMER AND OESHIPTO. I have these tables links through Access and need to write a iif statement but don't know how.

    In the OESALESORDER I have 4 columns I need called orderNo, FKARCustomer, BillTO_FKOEShipTo and ShipTO_FKOEShipto.

    In the ARCustomer I need SKARCustomer, Shiptoname,shiptoaddress,shiptoaddress2,shiptocity ,shiptostate and shiptozipcode

    In the OEShipto I need SKOEShioTO,Name,address1,address2,city,state.zip

    Based on the table design if the bill to address are the same as the ship to address then I need to use the OESHIPTO table if the SHipTo_FKOESHIPTO = 0 in the OESALESORDER table then I have to view the data in the ARCustomer table.

    A little confusing. Thank you in advance!

  2. #2
    Join Date
    Sep 2003
    Location
    T.O.
    Posts
    326
    I'm pretty sure you'll need to focus your question a little better.

    After reading you post a couple of times (well, a few actually), I'm not sure you're looking for one single IIf statement.

    If your true question is how to write an IIf statement:
    need to write a iif statement but don't know how
    the format is

    • IIf(expression, ValueIfTrue, ValueIfFalse)


    But it looks to me that you actually want to dynamically create SQL for your view, and this needs to be done by code. You can prob use the IIf in your code to help build it, though.

    Example of a point where I'm confused:
    Based on the table design if the bill to address are the same as the ship to address then I need to use the OESHIPTO table
    How do you need to use the table? What would be your WHERE clause, there's two address fields in OESHIPTO, etc.
    All code ADO/ADOX unless otherwise specified.
    Mike.

  3. #3
    Join Date
    Mar 2004
    Posts
    29

    Reply

    What I am tring to do is create a view with ship to information so that I can use the information with my shipping machine.


    From the OESALESORDER table I will be using a key called OrderNo this is going to be used to pull up the ship to information.

    In this table the ShipTO_FKOEShipto will determine if I should view the ARCustomer table when it is a 0 or if it is anything else I will need to view the OEShipTo table.

    Attached I have 2 .PDF files atteched
    The BWG4_OE.PDF has the OESALESORDER table structure and the OEShipTo table structure.
    The BWG_AR.PDF will have the ARCUstomer table structure.

    Thank you for any help!
    Attached Files Attached Files

  4. #4
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    And a followup to Mike's point on the addresses: Most Bill To/Ship To comparisons are in actuality a simple flag that says they are the same ... No real testing of the actual addresses themselves ...

  5. #5
    Join Date
    Sep 2003
    Location
    T.O.
    Posts
    326
    In this table the ShipTO_FKOEShipto will determine if I should view the ARCustomer table when it is a 0 or if it is anything else I will need to view the OEShipTo table.
    If there is a common id field from ShipTo_FKOEShipto (a) to both ARCustomer (b) and OEShipTo (c), I would consider making two separate queries, joining a to b and a to c by the common field and using your 0 determinant. Design the queries to pull out the relevant data from both b and c using matching columns (they don't need to be named the same, just contain the same type of data, ex. address). Then create a union query based on the output of b & c, this should get you what you're after.
    All code ADO/ADOX unless otherwise specified.
    Mike.

  6. #6
    Join Date
    Mar 2004
    Posts
    29
    Thank you for your help I wrote the program in Visual Basic instead of Access.

Posting Permissions

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