Solved

Union query producing parameter dialog with unnamed query in parameter

Posted on 2011-09-16
19
271 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
  • 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 47

Expert Comment

by:Dale Fye (Access MVP)
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
 

Author Comment

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

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 51

Accepted Solution

by:
HainKurt earned 500 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
Backup Your Microsoft Windows Server®

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

 
LVL 51

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 47

Expert Comment

by:Dale Fye (Access MVP)
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 51

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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

Suggested Solutions

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

920 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now