Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Jerusalem, Israel
    Posts
    708
    Thanks
    0
    Thanked 1 Time in 1 Post

    link empty text fields (XP/2K)

    I am running a query which has 2 linked tables linked by 3 text fields. Is there a problem if one is null on both sides, that they do not link and the information from that record is not in there? IF yes, is there a way around this?
    Thanks

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

    Re: link empty text fields (XP/2K)

    Are the nulls in only one table? If so, change the links to outer joins, displaying all records from the table without nulls in those fields. If there are nulls in both tables, you will have to create two such queries, one for each side, then create a union query to combine the results.

  3. #3
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Jerusalem, Israel
    Posts
    708
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: link empty text fields (XP/2K)

    maybe this will help clarify:
    tblinventory has an autonumberfield, then a numeric field and 2 text fields that identify the object. Then there is a number field as to how many places are in that object.
    qer1 is a query on a table that has a numeric field and 2 text fields that identify the object, and it is a totals query as to how many places in that object are actually used.
    I want to join the 2 sets of information, to see what portion of the object is in use. However, one of the 2 text fields can be null. Obviously, if it null, it is null on both sides. The query that joins qer1 and tblinventory shows the correct information for those objects which have information in the numeric field and both text fields , but if one of the text fields is null, the informartion does not appear at all.
    i.e.
    tblinventory
    001,1,a,b,25
    002,3,b,b,34
    003,3,d,,33
    qer1
    001,1,a,b,20
    002,3,b,b,32
    003,3,d,,13

    however when I join them on the 3 fields I only get the first two lines

    Is this what your solution will fix?
    Thanks

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: link empty text fields (XP/2K)

    Sorry, but you can't use a null value in a unique identifier. Either you have to use a place holder in that "null" text field, or you need to change the way you're trying to join the values.
    Charlotte

  5. #5
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Jerusalem, Israel
    Posts
    708
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: link empty text fields (XP/2K)

    I assume I could do it with brute strength- open both recordsets in code and step through them matching them one to one. Is there a better suggestion?
    Thanks

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

    Re: link empty text fields (XP/2K)

    You could create a query that joins the tables (which will only return records for which both sides are filled in) and - depending on the situation - one or more queries that have Is Null in the criteria on both sides. Finally, a union query that combines the lot.

  7. #7
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: link empty text fields (XP/2K)

    How could you match them even one by one? You don't actually know what the null would be if it were populated.
    Charlotte

  8. #8
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Jerusalem, Israel
    Posts
    708
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: link empty text fields (XP/2K)

    I am not sure I am being clear.
    What I am trying to reach is based on the info in my previous post that I should get the following result:
    tblinventory - inventory of objects
    001,1,a,b,25
    002,3,b,b,34
    003,3,d,null,33
    qer1-count of used objects
    001,1,a,b,20
    002,3,b,b,32
    003,3,d,null,13
    result:
    001,1,a,b,20,25
    002,3,b,b,32,34
    003,3,d,null,13,33

    the null is not supposed to be populated. The records are not consistent and sometimes the 4rth field can be null. If it is null it is null in both the table and the query.
    Therefore I think I could brute force it to match them up.
    Please tell me if I am wrong.
    Of course I could always put a space anywhere there is a null, and then the whole thing would work more easily. That would mean messing around with the client's data.
    Thanks to everyone for their help

  9. #9
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: link empty text fields (XP/2K)

    Create a query with all the fields of the inventory table.
    For the field that can contain the null use in the query the expression :
    Nz([YourNullField],"A")
    "A" can be replaced by any other character you want.
    In the existing query, use the same Nz function for the null field with the same character.
    Now you can create a third query based on the new query and the existing query and link on the 3 fields.
    Francois

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

    Re: link empty text fields (XP/2K)

    I have attached a database with a query to retrieve the non-null matches, another for the null "matches", and a union query to combine them.

    For practical reasons, qer1 is a table in this demo, not a query, but the principle is the same.

  11. #11
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Jerusalem, Israel
    Posts
    708
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: link empty text fields (XP/2K)

    brilliant.
    Now in the report based on this query- can I suppress the A, since it is fictitous?
    Thanks

  12. #12
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Jerusalem, Israel
    Posts
    708
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: link empty text fields (XP/2K)

    Thanks Hans. Now I see what you were doing.
    Thanks for 2 good solutions everyone.

  13. #13
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: link empty text fields (XP/2K)

    In the Nz function use a character or combination of characters that won't appear in the normal data in the Null field.
    In the on format event of the detail section of the report, make the text box invisible if it contain the character that you use in the Nz function.
    Francois

  14. #14
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Jerusalem, Israel
    Posts
    708
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: link empty text fields (XP/2K)

    thanks

  15. #15
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Jerusalem, Israel
    Posts
    708
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: link empty text fields (XP/2K)

    I used the following code, but that makes all the Chelkah textboxes in all the details disappear. I put it in the on format of the detail

    If Me.Chelkah = "A" Then Me.Chelkah.Visible = False

    Thanks

Page 1 of 2 12 LastLast

Posting Permissions

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