Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Union query producing parameter dialog with unnamed query in parameter

Posted on 2011-09-16
19
Medium Priority
?
291 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
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 

Author Comment

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

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 60

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 60

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 60

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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

636 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