UNION with WHERE from a Form

[sxschech]

Having a problem refering to a string from a form in a union query. It works fine as a non union.

This union query works

SELECT * 
FROM qryAPPTotals in '\\imageserver\Application\AppStats.mdb' 
UNION SELECT Left(Sort_Status,1), Year, Stu_Pop, Status, iif(left(Sort_Status,1) in ('3','5'),Total/100,Total), iif(left(Sort_Status,1) in ('3','5'),Dom/100,Dom), iif(left(Sort_Status,1) in ('3','5'),Intl/100,Intl) 
From GDomIntl 
ORDER BY 3, 2, 1;

If I do add a where clause

SELECT * 
FROM qryAPPTotals in '\\imageserver\Application\AppStats.mdb' 
UNION SELECT Left(Sort_Status,1), Year, Stu_Pop, Status, iif(left(Sort_Status,1) in ('3','5'),Total/100,Total), iif(left(Sort_Status,1) in ('3','5'),Dom/100,Dom), iif(left(Sort_Status,1) in ('3','5'),Intl/100,Intl) 
From GDomIntl 
[red]WHERE YEAR < [forms]![frmMain].[txtBannerYear][/red]
ORDER BY 3, 2, 1;

It doesn't work - The MS Jet Database does not recognize [forms]![frmMain].[txtBannerYear] as a valid field name or expression.

However, if I run the same query and remove the part before the union, it works fine.

SELECT Left(Sort_Status,1), Year, Stu_Pop, Status, iif(left(Sort_Status,1) in ('3','5'),Total/100,Total), iif(left(Sort_Status,1) in ('3','5'),Dom/100,Dom), iif(left(Sort_Status,1) in ('3','5'),Intl/100,Intl) 
From GDomIntl 
[red]WHERE YEAR < [forms]![frmMain].[txtBannerYear][/red]
ORDER BY 3, 2, 1;

The union query works with hard coded value WHERE YEAR < "2009"

I also tried UNION ALL, didn't work either. Any thoughts how I can rewrite to allow query to refer to a value from the form?

 

[AlexCuse]

you might try something like this:

select a.* from
(
SELECT * 
FROM qryAPPTotals in '\\imageserver\Application\AppStats.mdb' 
UNION SELECT Left(Sort_Status,1), Year, Stu_Pop, Status, iif(left(Sort_Status,1) in ('3','5'),Total/100,Total), iif(left(Sort_Status,1) in ('3','5'),Dom/100,Dom), iif(left(Sort_Status,1) in ('3','5'),Intl/100,Intl) 
From GDomIntl 
) a
WHERE YEAR < [forms]![frmMain].[txtBannerYear]
ORDER BY 3, 2, 1;

Access is funny sometimes. But I think what this would do is apply your where clause after combining the two parts of your union, so that you don't have to be worried about your form parameter being needed in your external mdb (where it is not available).

Hope it helps,

Alex

[small]----signature below----[/small]
Majority rule don't work in mental institutions

My Crummy Web Page

 

[Golom]

You might also try a different syntax

FROM [;DATABASE=\\imageserver\Application\AppStats.mdb].qryAPPTotals

Also note that "Year" is a reserved word in Access so enclose it in brackets (i.e. [Year])

 

[sxschech]

I tried both suggestions and I still receive the error message. I even tried doing a separate query Select * from qry where... and same error. I put the data in a table and then the where clause worked. I would prefer not to have to do a bunch of make tables as there are many queries that will require the criteria, (different slices and dices and many queries behind them to build up to the final product) but if that is the only option, guess that's what I'll do.

 

[PWise]

this query works for me

SELECT tblStudents.Studentid, tblStudents.FamilyLastName, tblStudents.StudentFirstName
FROM tblStudents IN '\\Server\DATA\pwdocs\bse.mdb'
union 
SELECT Providers.ProviderID, Providers.StaffLastName, Providers.StaffFirstName
FROM Providers
WHERE (((Providers.ProviderID)<[forms]![form2].[providerid]))
order by 3,2,1

What error do you get?

Do you also want to limit qryAPPTotals to year
=[forms]![frmMain].[txtBannerYear]
In what database in formmain? The current database or in \\imageserver\Application\AppStats.mdb' .

Is the form open?

You say that when you hard code the pramameter it works
What does this return

SELECT * ,[forms]![frmMain].[txtBannerYear] as frmyear
FROM qryAPPTotals in '\\imageserver\Application\AppStats.mdb' 
UNION SELECT Left(Sort_Status,1), Year, Stu_Pop, Status, iif(left(Sort_Status,1) in ('3','5'),Total/100,Total), iif(left(Sort_Status,1) in ('3','5'),Dom/100,Dom), iif(left(Sort_Status,1) in ('3','5'),Intl/100,Intl) ,,[forms]![frmMain].[txtBannerYear] as frmyear
From GDomIntl 
WHERE YEAR < [forms]![frmMain].[txtBannerYear]
ORDER BY 3, 2, 1;

 

[sxschech]

pwise,

Thanks for responding...
The error I get is "The Microsoft Jet database engine does not recognize '[forms]![frmMain].[txtBannerYear]' as a valid field name or expression."

The form is in the local (current?) database. From what I can tell, the problem seems to be when combining the query that points to a query in the remote (external?) mdb file. (both are in the same network directory.) I tried the query you suggested and that still returned the same error message.

The first query in the union does not necessarily need criteria applied to it as it already has been summaraized for the period requested. The second query of the union is coming from another source and thus needs to be filtered to exclude future years.

 

[randy700]

The error I get is "The Microsoft Jet database engine does not recognize '[forms]![frmMain].[txtBannerYear]' as a valid field name or expression."

Is frmMain open?


Randy

 

[sxschech]

Yes, of course. It is opened on startup.

 

[bubba100]

Have you tried [forms]![frmMain]![txtBannerYear]?

 

[sxschech]

I tried your suggestion, no luck. I even tried qualifying
in brackets

WHERE GDomIntl.[YEAR] < [forms]![frmMain]![txtBannerYear]

In the meantime, to get the data out, I have hard coded the year and hope I remember to change it when next year rolls around.