?
Solved

Union query producing parameter dialog with unnamed query in parameter

Posted on 2011-09-16
19
Medium Priority
?
285 Views
Last Modified: 2012-05-12
I have the following union query producing the error:

SELECT [Class List by Teacher].TeacherID, [Class List by Teacher].FamilyID, [ParentFNCombo 4 UNION].FamilyID, [Class List by Teacher].ParentID, [Class List by Teacher].FamilyName, [Class List by Teacher].StudentFirstName, [Class List by Teacher].FirstName, [Class List by Teacher].Address1, [Class List by Teacher].Phone1, [Class List by Teacher].PrimaryInd, [ParentFNCombo 4 UNION].FNCombo
FROM [Class List by Teacher] LEFT JOIN [ParentFNCombo 4 UNION] ON [Class List by Teacher].FamilyID = [ParentFNCombo 4 UNION].FamilyID;

ERROR dialog pops up:
"Enter Parameter Value"
Query1.FamilyID

Where is Query1 coming from?  Why is it asking?  This is the last of several queries, all run successfully until this one.  If I click Cancel, it returns no records.  If I click OK, even if entering no value in the parameter dialog, it returns all the records.
0
Comment
Question by:ErinRad
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 6
  • 4
  • +2
19 Comments
 
LVL 75
ID: 36550432
I wonder if this

ParentFNCombo 4 UNION

is an issue because UNION is a reserved word ?

mx
0
 
LVL 48

Expert Comment

by:Dale Fye
ID: 36550448
If you don't enter a value at the prompt, are any of the fields showing up blank?  If so, chances are that the query that is providing that value, doesn't really have a [FamilyID] field.

I would check on your queries: [ClassList by Teacher], [ParentFNCombo 4 UNION] to see if either of them uses a Query1.

You might also want to check to see if either of these queries, or this one contains a defined parameter [Query1].[FamilyID]

0
 

Author Comment

by:ErinRad
ID: 36550455
Good point... I changed the name of the UNION query used in this ordinary query but still received the same error.  New SQL as follows:

SELECT [Class List by Teacher].TeacherID, [Class List by Teacher].FamilyID, [Class List by Teacher].ParentID, [Class List by Teacher].FamilyName, [Class List by Teacher].StudentFirstName, [Class List by Teacher].FirstName, [Class List by Teacher].Address1, [Class List by Teacher].Phone1, [Class List by Teacher].PrimaryInd, [ParentFNCombo 4].FNCombo
FROM [Class List by Teacher] LEFT JOIN [ParentFNCombo 4] ON [Class List by Teacher].FamilyID = [ParentFNCombo 4].FamilyID;
0
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 

Author Comment

by:ErinRad
ID: 36550471
To FYED:
All fields are populated as expected without entering a value at the prompt.
0
 
LVL 58

Expert Comment

by:HainKurt
ID: 36550480
try this

SELECT c.TeacherID, c.FamilyID, c.ParentID, c.FamilyName, c.StudentFirstName, c.FirstName, c.Address1, c.Phone1, c.PrimaryInd, p.FNCombo
  FROM [Class List by Teacher] c
       LEFT JOIN [ParentFNCombo 4] p ON c.FamilyID = p.FamilyID
0
 
LVL 75
ID: 36550486
oK ... what is this:

[ParentFNCombo 4].FNCombo

The 4 ... also in 2 other places ....?

SELECT [Class List by Teacher].TeacherID,
    [Class List by Teacher].FamilyID,
    [Class List by Teacher].ParentID,
    [Class List by Teacher].FamilyName,
    [Class List by Teacher].StudentFirstName,
    [Class List by Teacher].FirstName,
    [Class List by Teacher].Address1,
    [Class List by Teacher].Phone1,
    [Class List by Teacher].PrimaryInd,
    [ParentFNCombo 4].FNCombo


FROM [Class List by Teacher]
    LEFT JOIN [ParentFNCombo 4]
        ON [Class List by Teacher].FamilyID
                = [ParentFNCombo 4].FamilyID;
0
 

Author Comment

by:ErinRad
ID: 36550490
SQL for Class List by Teacher:
SELECT Students.TeacherID, Families.FamilyID, Parents.ParentID, Families.FamilyName, Students.StudentFirstName, Parents.FirstName, Addresses.Address1, [Parent Contact Information].Phone1, Addresses.PrimaryInd
FROM (((Families LEFT JOIN Parents ON Families.FamilyID = Parents.FamilyID) LEFT JOIN Students ON Families.FamilyID = Students.FamilyID) LEFT JOIN Addresses ON Parents.ParentID = Addresses.ParentID) LEFT JOIN [Parent Contact Information] ON Parents.ParentID = [Parent Contact Information].ParentID
WHERE (((Addresses.PrimaryInd)=True))
ORDER BY Families.FamilyID, Addresses.Address1 DESC;


SQL for ParentFNCombo 4:
SELECT [ParentFNCombo 2].FamilyID, [ParentFNCombo 2].ParentID, [ParentFNCombo 2].FNCombo FROM [ParentFNCombo 2]
UNION ALL SELECT [ParentFNCombo 3 Single Parents w FN].FamilyID, [ParentFNCombo 3 Single Parents w FN].ParentID, [ParentFNCombo 3 Single Parents w FN].FirstName AS FNCombo
FROM [ParentFNCombo 3 Single Parents w FN];
0
 
LVL 58

Accepted Solution

by:
HainKurt earned 2000 total points
ID: 36550495
are these tables or queries

[Class List by Teacher]
[ParentFNCombo 4]

if queries post the sql...
0
 

Author Comment

by:ErinRad
ID: 36550500
To DatabaseMX:
the "4" is part of the query name (it's 4th in a sequence of queries)
0
 
LVL 58

Expert Comment

by:HainKurt
ID: 36550520
I don't trust Access with so much uninon/union alls/ left joins... it may give unpredicted results...
if possible write your new query based on tables only...
0
 
LVL 75
ID: 36550522
Get this tool
http://www.rickworld.com/products.html#Find%20and%20Replace%209.0
... best ever ... and Search for Query1

mx
0
 

Author Comment

by:ErinRad
ID: 36550538
to HainKurt:
That worked... can you explain for me why?  

Also, quick story for all you experts...
I was at my kids' school roller skating party, and there was this guy who was a pretty great skater and he was wearing a black experts-exchange.com t-shirt!  I rolled over and said "excuse me, I'm sure people don't usually tell you this but I LOVE your shirt!"  He was pretty shocked (woman approaches techie??), but I told him I thought this was the best tech help forum EVER.  

Thanks always to all of you for being there when we get stuck :)
0
 
LVL 48

Expert Comment

by:Dale Fye
ID: 36550550
I think you are going to have to go back and look at the other 3 queries that look like:

[ParentFNCombo 2]
[ParentFNCombo 3 Single Parents w FN]

and whatever the name is for the [Combo 1] version of this.
0
 
LVL 7

Expert Comment

by:BusyMama
ID: 36550553
In your query, you are selecting 2 columns named FamilyID.  Maybe renaming one of them would work?

For example,

 [ParentFNCombo 4 UNION].FamilyID AS FamilyID2

0
 

Author Closing Comment

by:ErinRad
ID: 36550572
Very fast and direct resolution. THANKS
0
 
LVL 75
ID: 36550582
"woman approaches techie?"
I'm waiting for that to happen at LA Fitness, where I where EE shirts every night!

btw ... sounds like you are female.  You might want to change your EE icon to the female version :-)

mx
0
 
LVL 75
ID: 36550605
@HainKurt:

''I don't trust Access with so much uninon/union alls/ left joins... it may give unpredicted results...
if possible write your new query based on tables only..."
Do you have an example where this fails ... as I've never had an issue ...?
0
 
LVL 58

Expert Comment

by:HainKurt
ID: 36552464
I have 63 TShirts and started giving them to my friends as gift :) EE should give us some other choices.

I DO NOT WANT BLACK T-SHIRT ANYMORE!

Can you hear me EE!
0
 
LVL 75
ID: 36552477
I want black, but different artwork ... something new and different.  And MUST include the phrase  

Experts-Exchange.com

I got one recently - must have been an short lived interim - with different artwork, but no clue that it was an EE shirt.  These shirts are ADVERTISING ... hello !!!  I wear them to LA Fitness every night.

mx
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Suggested Courses

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question