Results 1 to 7 of 7
  1. #1
    5 Star Lounger
    Join Date
    Jan 2005
    Posts
    614
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Field is blank - how to fill it in? (Access20)

    Hi!
    Ran into another thing.
    I have Report with Location field.
    Once-in-a-while Location is " " (Not Null - blank).

    My Report has this top text box right underneath of the Title
    LOCATION

    Then there is another field LOC that has locations numbers like 34,35,46
    I needed to create a table that says
    34 US
    35 CANADA
    46 France

    Now I neet to do following.
    When Reports is running I need to check IF LOCATION is " " then check field LOC and go pull Description from the table I had just created that has no affiliation with query or anything.

    My table has 5 values only.
    So I thought IF it is possible to have a query to have
    If IsNull LOCATION then look in LOC and
    If LOC = 35 then US
    IF LOC=36 then CANADA
    etc...

    Thanks

  2. #2
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Field is blank - how to fill it in? (Access20)

    Create this LOC table like you said. Then in the query behind the report, use a LEFT OUTER JOIN to connect this new table to the existing table that has that Location field. On the report, itself show the LOC name from your new table rather than the Location.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  3. #3
    5 Star Lounger
    Join Date
    Jan 2005
    Posts
    614
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Field is blank - how to fill it in? (Access20)

    Thanks, I thought of the way not linking that table. But I will try and link it.

  4. #4
    5 Star Lounger
    Join Date
    Jan 2005
    Posts
    614
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Field is blank - how to fill it in? (Access20)

    Sorry I am affraid it is not possible.
    You suggested : On the report, itself show the LOC name from your new table rather than the Location.

    I can not use another table rather then Main because it only has 5 records - those that aren't in a MAIN table.

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Field is blank - how to fill it in? (Access20)

    As Mark wrote, use an outer join. Double click the join line in the query, and select the option to return ALL records from the main table and only related records from the join table.

  6. #6
    5 Star Lounger
    Join Date
    Jan 2005
    Posts
    614
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Field is blank - how to fill it in? (Access20)

    I am affraid I can't explain but is there way to have it done like this?
    If IsNull LOCATION then look in LOC and
    If LOC = 35 then US
    IF LOC=36 then CANADA
    etc...

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Field is blank - how to fill it in? (Access20)

    I'm sure it's possible. Please attach a sample database.

Posting Permissions

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