Union Query question (2000 (SR1a))

Topic

New Reply

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;

Reply | Quote

Replies

> 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

Reply | Quote

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.

Reply | Quote

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…

Reply | Quote

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

Reply | Quote

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?

Reply | Quote

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.

Reply | Quote

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”

Reply | Quote

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.).

Reply | Quote

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”

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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 Microsoft Knowledge Base Article 239114 which describes how to determine what version you have, and how to get the latest version.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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 Microsoft Knowledge Base Article 239114 which describes how to determine what version you have, and how to get the latest version.

Reply | Quote

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:

strWhere = "[Age]=" & intAge & " AND [CommitteName] = '" & strCommitteName & "'"

Reply | Quote

Charlotte:

Thanks for the advice. I was aware of that in a general way and in this instance the Where clause was being created properly. Once I got the problem fixed via the Jet update, I changed the strWhere data type back to String from Variant and it still works properly (as your explanation makes clear) so I will leave it that way.

The Variant data type was a holdover from a previous incarnation of the code where CommitteeName and Age were in the same column and I was hoping Access would treat the ages as integers and the CommitteeNames as text.

Thanks for taking the time to offer your advice.

Reply | Quote

Hi, Peter: Well, you don’t need this anymore, but I _would_ like to add on with something that was triggered by Charlotte’s discussion of the datatypes that you might be getting — I wonder if you had unioned the CommitteeNames from the one query with a _string_ (“”) instead of a NULL, that you might have not faced these problems to begin with. (Er, I hope I’m not repeating a suggestion already made …) Of course, it’s always good to be updated and in synch, and Wendell’s suggestion pointed you in that direction, which also solved the problem! thx, Pat

Reply | Quote

Hi Pat:
Could you expand on your suggestion? How would the syntax look for the Union Query using a string instead of NULL?

The use of the null comes from a discussion from last October when Hans made the suggestion of the nulls in post 307662. Obviously at this point it is academic, but more knowledge is good (particularly with Acess!

Reply | Quote

> How would the syntax look for the Union Query using a string instead of NULL?

Actually, I see for “Age” you would probably want a Null. But for Committee Name, you could replace w/ empty string (“”). I’ve edited bits of the two queries below to show what I mean:

SELECT … EnvelopeNumber, “” as CommitteeName, Age, Address1….)
SELECT … EnvelopeNumber, CommitteeName, NULL as Age, Address1 …

thx
Pat

Reply | Quote

> How would the syntax look for the Union Query using a string instead of NULL?

Actually, I see for “Age” you would probably want a Null. But for Committee Name, you could replace w/ empty string (“”). I’ve edited bits of the two queries below to show what I mean:

SELECT … EnvelopeNumber, “” as CommitteeName, Age, Address1….)
SELECT … EnvelopeNumber, CommitteeName, NULL as Age, Address1 …

thx
Pat

Reply | Quote

Hi Pat:
Could you expand on your suggestion? How would the syntax look for the Union Query using a string instead of NULL?

The use of the null comes from a discussion from last October when Hans made the suggestion of the nulls in post 307662. Obviously at this point it is academic, but more knowledge is good (particularly with Acess!

Reply | Quote

Hi, Peter: Well, you don’t need this anymore, but I _would_ like to add on with something that was triggered by Charlotte’s discussion of the datatypes that you might be getting — I wonder if you had unioned the CommitteeNames from the one query with a _string_ (“”) instead of a NULL, that you might have not faced these problems to begin with. (Er, I hope I’m not repeating a suggestion already made …) Of course, it’s always good to be updated and in synch, and Wendell’s suggestion pointed you in that direction, which also solved the problem! thx, Pat

Reply | Quote

Charlotte:

Thanks for the advice. I was aware of that in a general way and in this instance the Where clause was being created properly. Once I got the problem fixed via the Jet update, I changed the strWhere data type back to String from Variant and it still works properly (as your explanation makes clear) so I will leave it that way.

The Variant data type was a holdover from a previous incarnation of the code where CommitteeName and Age were in the same column and I was hoping Access would treat the ages as integers and the CommitteeNames as text.

Thanks for taking the time to offer your advice.

Reply | Quote

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:

strWhere = "[Age]=" & intAge & " AND [CommitteName] = '" & strCommitteName & "'"

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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?

Reply | Quote

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

Reply | Quote

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…

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

> 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

Reply | Quote