Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    4 Star Lounger
    Join Date
    Dec 2002
    Location
    Kitchener, Ontario, Canada
    Posts
    421
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Union Query question (2000 (SR1a))

    I am trying to track down a problem I am having with an unbound form that generates reports based on various saved queries. The specific problem is that reports filtered on CommitteeName are producing the Message that "There are no rows to display" even though there are records. The report gets its records from the union query below which generates them just fine. Obviously there is a breakdown filtering the stuff from the union query. This is happening in VBA.

    Here is the current question in my troubleshooting:
    I have noticed that the following union query produces the column name Exp1003 instead of CommitteeName for the fourth column. Does it matter/do I care? Or should I keep looking for another solution to the problem? I have seen this sort of thing appear in regular queries when a column gets added in twice. I cannot for the life of me figure out why Access is doing it in this instance.

    SELECT IndividualID, IndName, EnvelopeNumber, null, Age, Address1, Address2, HomePhone, HomeEmail, BusinessPhone, BusinessEmail, FamilySortName as ParentOrDescription
    FROM qryBasicIndInformation
    UNION SELECT IndividualID, IndName, EnvelopeNumber, CommitteeName, null, Address1, Address2, HomePhone, HomeEmail, BusinessPhone, BusinessEmail, Position as ParentOrDescription
    FROM [qryCommittee Lists]
    ORDER BY IndName;

  2. #2
    3 Star Lounger
    Join Date
    Jun 2001
    Location
    Salem, Oregon, USA
    Posts
    219
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Union Query question (2000 (SR1a))

    > I have noticed that the following union query produces the column name Exp1003 instead of CommitteeName for the fourth column. Does it matter/do I care?

    It _could_ matter, so you might care. If I understand you correctly: you have a report based on this union query, but you are opening the report with a filter from a form. If you're not getting the name "CommitteeName," then, of course, your report won't work. Have you looked at the output of the query to confirm that there are, really, committee names there? Are you getting a separate column called, "null?" I'm not familiar with just using the word "null," although if it works for you ... if I were doing it, I think I'd put "...CommitteeName, null as TheNullField, Address1.." etc. Not that I know that your way won't work. -- thx
    Pat

  3. #3
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Union Query question (2000 (SR1a))

    Your comments are on target - if you don't have a field name showing up in a query that is the control source for a form or report, you get error messages. Also, the use of the word null as a field name is almost certain to cause problems if you attempt to do anything with it - Null is a reserved word as far as Jet is concerned, and the fact that it didn't get capitalized suggests it is being returned as text or some other thing.
    Wendell

  4. #4
    4 Star Lounger
    Join Date
    Dec 2002
    Location
    Kitchener, Ontario, Canada
    Posts
    421
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Union Query question (2000 (SR1a))

    The use of null in the union query was at the suggestion of Hans last fall when I was originally working on the problem (which I thought I had solved until today!). This was because "Age" was an integer that needed to sort as a number and "CommitteeName" was text that needed to sort alphabetically. His suggestion was to use two separate columns in the union query and that the union query wouldn't care.

    For example
    Column 1, Column 2, Null, Column 4
    Union
    Column 1, Column 2, Column 3, Null

    I will check my caps and see if that makes a difference and continue to play with this. The underlying query for [qryCommittee Lists] had three tables in it all joined with one to one joins. Is this possibly wat is causing CommitteeName to be wonky.

    I will keep you posted as I will continue to pursue this tonight. (Sort of) snowed in in Southern Ontario...

  5. #5
    4 Star Lounger
    Join Date
    Dec 2002
    Location
    Kitchener, Ontario, Canada
    Posts
    421
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Union Query question (2000 (SR1a))

    I got it working with the following syntax.
    SELECT IndividualID, IndName, EnvelopeNumber,CommitteeName, Null as Age, Address1, Address2, HomePhone, HomeEmail, BusinessPhone, BusinessEmail, Position as ParentOrDescription
    FROM [qryCommittee Lists]
    UNION SELECT IndividualID, IndName, EnvelopeNumber, Null, Age, Address1, Address2, HomePhone, HomeEmail, BusinessPhone, BusinessEmail, FamilySortName as ParentOrDescription
    FROM qryBasicIndInformation
    ORDER BY IndName;

    Your suggestions pointed me in the right direction and it only took about 10 minutes after my last reply to solve it. I started by switching the two queries around and sure enough, when ComitteeName came first, it appeared as a column name and Age cakced out. It was pretty quick to use Pat's suggestion of "Null as Age" and Bob's your uncle it all worked. Now if only I can get the report working properly again... (Actually, I won't bother as I have a copy somewhere of one I didn't @#$!% up trying to troubleshoot this thing).

    As always, the Lounge gets the juices flowing. Many thanks

  6. #6
    4 Star Lounger
    Join Date
    Dec 2002
    Location
    Kitchener, Ontario, Canada
    Posts
    421
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Union Query question (2000 (SR1a))

    Curioser and curioser. I was checking this further today and it seems to be a conflict between Access (actually SP3 not SR1a as stated above) and Windows 98.

    This database is set up for multi-user. Back end is on the server and everyone has front ends on their own computers. 2 of the machines have Windows 2000 and this multiple select form which runs the Union Query which feeds the report works properly on both of them. On the Windows 98SE machines, I get the error message that started this whole thing. (It turns out that the Union Query was right all along, I suspect I may have changed it without remembering when I was trying to figure out what was going on.)

    First I get a prgrammed message box that says "There are no records to display" (and there are records) then I get error 2501 The OpenReport Action was cancelled, Click OK and it closes my form and doesn't redraw the screen properly and it reduces the size of the Switchboard window (which is supposed to stay maximized.)

    Any ideas what is going on here?

  7. #7
    4 Star Lounger
    Join Date
    Dec 2002
    Location
    Kitchener, Ontario, Canada
    Posts
    421
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Union Query question (2000 (SR1a))

    A further bit of information. I put a breakpoint in the code at the point where it runs DoCmd.OpenReport. This uses the strWhere argument.

    StrWhere has data. When I hover the cursor over it I get for example: StrWhere = "CommitteeName = 'Adult Forum'" . It seems the hangup is in strWhere getting fed to the report because as you step through the report opening events it will end up at the Report_NoData Sub which is where the message box is triggered.

  8. #8
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Union Query question (2000 (SR1a))

    Peter,

    One thing to note. Union queries I have used in the past get a little "hokey" if the fields in the queries being unioned are not in exactly the same order and with the same names. Just my 2 cents worth.

    Hokey is that technical term for "It dont work right"
    Regards,

    Gary
    (It's been a while!)

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

    Re: Union Query question (2000 (SR1a))

    In your union query, if you're still using Null values for fields missing in one table or the other, the datatype of the returned value in the query is going to be based on the Null. If you have a Null and the matching field in the other table returns a number, the entire column will be handled as a string. That could cause you to wind up with nodata if the value is supposed to be a number.

    If you run the query with that where condition, do you get records? You definitely need to separate possible data problems from possible report problems.
    Charlotte

  10. #10
    4 Star Lounger
    Join Date
    Dec 2002
    Location
    Kitchener, Ontario, Canada
    Posts
    421
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Union Query question (2000 (SR1a))

    I ran several tests this afternoon after my previous posts.
    1) If I print the report from the query that feeds the union query then it prints fine.
    2) If I use ages in my strWhere from the other column in the union query that also makes use of null values, they print fine.
    3) I only have this problem trying to use text strings from CommitteeName in strWhere

    4) And this seems most important to me: I can make this whole thing work flawlessly on the 2 computers on the network that are running Windows 2000. If I make a copy of the frontend from one of these computers and put it on any Windows 98 machines it fails in passing the value from strWhere into the DoCmd.OpenReport method.

    One other thing that may well have a bearing: strWhere is of Variant data type because it needs to be able to handle ages as integers and CommitteeNames as text. <img src=/S/confused.gif border=0 alt=confused width=15 height=20>

  11. #11
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Union Query question (2000 (SR1a))

    It may be that you have different versions of the MDAC on the Win98 platform than you do on Win2000 - I presume your backend is Jet based and not an ODBC driver. In any event, you might have different versions of the Jet engine which could account for the difference. You might want to look at <!mskb=239114>Microsoft Knowledge Base Article 239114<!/mskb> which describes how to determine what version you have, and how to get the latest version.
    Wendell

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

    Re: Union Query question (2000 (SR1a))

    Peter,

    The WhereCondition argument is *always* a string. You pass it as if you were building a where clause in SQL but without the word WHERE. If you're passing text, you have to surround the text value itself in delimiters and the entire expression has to be a string. Something like this:

    <pre>strWhere = "[Age]=" & intAge & " AND [CommitteName] = '" & strCommitteName & "'"</pre>

    Charlotte

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

    Re: Union Query question (2000 (SR1a))

    Gary,

    The fields definitely have to be in the same order in a union query, but they don't have to have the same names. The field names used are the ones in the first select query. However, the fields must have the same or a compatible datatype (i.e., AutoNumber and Long, Currency and Double, etc.).
    Charlotte

  14. #14
    4 Star Lounger
    Join Date
    Dec 2002
    Location
    Kitchener, Ontario, Canada
    Posts
    421
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Union Query question (2000 (SR1a))

    Wendell:

    God Bless your cotton socks! I downloaded the lastest service pack and all the problems went away! If only life were so easy.

    Would these different versions of Jet have been the result of updating Windows via Windows Update? That seems to me to be the only way this could have happened as I believe all of the Win 98 machines have run Windows update since I completed these forms. I *know* it was working in the fall.

    Anyways. Many thanks.

  15. #15
    3 Star Lounger
    Join Date
    Jun 2001
    Location
    Salem, Oregon, USA
    Posts
    219
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Union Query question (2000 (SR1a))

    Thanks, Wendell! And I just went out to check your "Tip of the Day" -- selecting with the ruler, pretty cool! Ah allus lassoo them .... the ruler way could be useful, at times!
    thx,
    Pat

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
  •