[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 296
  • Last Modified:

Union query producing parameter dialog with unnamed query in parameter

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
ErinRad
Asked:
ErinRad
  • 6
  • 6
  • 4
  • +2
1 Solution
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
I wonder if this

ParentFNCombo 4 UNION

is an issue because UNION is a reserved word ?

mx
0
 
Dale FyeCommented:
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
 
ErinRadAuthor Commented:
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
ErinRadAuthor Commented:
To FYED:
All fields are populated as expected without entering a value at the prompt.
0
 
HainKurtSr. System AnalystCommented:
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
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
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
 
ErinRadAuthor Commented:
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
 
HainKurtSr. System AnalystCommented:
are these tables or queries

[Class List by Teacher]
[ParentFNCombo 4]

if queries post the sql...
0
 
ErinRadAuthor Commented:
To DatabaseMX:
the "4" is part of the query name (it's 4th in a sequence of queries)
0
 
HainKurtSr. System AnalystCommented:
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
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
Get this tool
http://www.rickworld.com/products.html#Find%20and%20Replace%209.0
... best ever ... and Search for Query1

mx
0
 
ErinRadAuthor Commented:
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
 
Dale FyeCommented:
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
 
BusyMamaCommented:
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
 
ErinRadAuthor Commented:
Very fast and direct resolution. THANKS
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
"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
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
@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
 
HainKurtSr. System AnalystCommented:
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
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
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

Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

  • 6
  • 6
  • 4
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now