Go Back   Access World Forums > Microsoft Access Discussion > Queries

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 09-06-2019, 03:23 AM   #1
FueledbyAccess
Newly Registered User
 
Join Date: Sep 2019
Posts: 2
Thanks: 1
Thanked 0 Times in 0 Posts
FueledbyAccess is on a distinguished road
Union query asking to Enter Parameter Values

Hello

I've seen various threads on similar problems as mine but none have been able to resolve the issue.

I have 3 tables I want to append using a union query, as I don't want all the columns I'm not using the

Select * From

but instead entering the column headings I want, below is my SQL code in full

Select all
[Nominal Code], Date, Period, Origin, Type, [Journal Type], Batched, [Header Line No#], [Header Ref#], Debit, Credit, Reconciled, [Internal Ref#], [Batch Ref#], [Account Code], [Analysis Code], [Price Code], Year, [Project Code2], [Cost Header Name], [Cost Centre Code2], [Audit No#], [Account Name], SL_ACC_USER1, SL_ACC_USER2, SL_ACC_USER3, [Home to 2nd Base Rate], [Currency Rate], [Journal Detail Ref#], [Nominal Account Name], Quantity, [Sub Ledger Audit No#]

FROM [Nominal Transactions open]

UNION ALL select
[Nominal Code], Date, Period, Origin, Type, [Journal Type], Batched, [Header Line No#], [Header Ref#], Debit, Credit, Reconciled, [Internal Ref#], [Batch Ref#], [Account Code], [Analysis Code], [Price Code], Year, [Project Code2], [Cost Header Name], [Cost Centre Code2], [Audit No#], [Account Name], SL_ACC_USER1, SL_ACC_USER2, SL_ACC_USER3, [Home to 2nd Base Rate], [Currency Rate], [Journal Detail Ref#], [Nominal Account Name], Quantity, [Sub Ledger Audit No#]

from
[Nominal Transactions closed]

UNION ALL select
[Nominal Code], Date, Period, Origin, Type, [Journal Type], Batched, [Header Line No#], [Header Ref#], Debit, Credit, Reconciled, [Internal Ref#], [Batch Ref#], [Account Code], [Analysis Code], [Price Code], Year, [Project Code2], [Cost Header Name], [Cost Centre Code2], [Audit No#], [Account Name], SL_ACC_USER1, SL_ACC_USER2, SL_ACC_USER3, [Home to 2nd Base Rate], [Currency Rate], [Journal Detail Ref#], [Nominal Account Name], Quantity, [Sub Ledger Audit No#]

From
tbl_Accruals;


The issue I'm getting is that for SL_ACC_USER1, SL_ACC_USER2, SL_ACC_USER3 I'm being asked to 'Enter Parameter Values'

I've checked and the column headings are valid in the tables and spelled correctly so I've no idea what's causing the problem.

FueledbyAccess is offline   Reply With Quote
Old 09-06-2019, 05:58 AM   #2
pbaldy
Wino Moderator
 
pbaldy's Avatar
 
Join Date: Aug 2003
Location: Nevada, USA
Posts: 33,247
Thanks: 13
Thanked 4,102 Times in 4,034 Posts
pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold
Re: Union query asking to Enter Parameter Values

Do they perhaps have spaces in the actual names rather than underscores?
__________________
Paul
Microsoft Access MVP 2007-2019

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
pbaldy is offline   Reply With Quote
Old 09-06-2019, 08:49 AM   #3
plog
Banishment Pending
 
Join Date: May 2011
Posts: 9,379
Thanks: 11
Thanked 2,277 Times in 2,229 Posts
plog is a jewel in the rough plog is a jewel in the rough plog is a jewel in the rough
Re: Union query asking to Enter Parameter Values

The short answer is, you don't have SL_ACC_USER1, SL_ACC_USER2, SL_ACC_USER3 in one or more of your queries. Perhaps you mistyped it, perhaps it doesn't exist at all.

To fix this you need to divide and conquer. You essentially have 3 unique queries, take each query and run it by itself--paste the SQL for one query into a new query object and run it. If that individual query produces the prompt, it needs to be fixed.

Either you've mistyped the field name or it doesn't exist. If it doesn't exist you can force it to exist by making a calculated field for it like so:

SL_ACC_USER1: ""

That will make it exist in the query and then you can UNION that query to the others.

plog is offline   Reply With Quote
The Following User Says Thank You to plog For This Useful Post:
FueledbyAccess (09-20-2019)
Old 09-06-2019, 09:20 AM   #4
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 5,571
Thanks: 55
Thanked 1,228 Times in 1,209 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: Union query asking to Enter Parameter Values

Hi Fueled. Welcome to AWF! Are you able to post a sample copy of your db? It would be easier if we could examine the objects to check a few things.
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is online now   Reply With Quote
Old 09-06-2019, 10:23 AM   #5
The_Doc_Man
Happy Retired Curmudgeon
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 14,504
Thanks: 92
Thanked 1,669 Times in 1,550 Posts
The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold
Re: Union query asking to Enter Parameter Values

Almost invariably, when you get an "Enter Parameter Value" box it names the parameter it wants entered. You need to compare whatever name it gave you against the query parts to compare spelling of that object vs. the place where you thought it was stored. It is not a 100% case, but it is extremely common to find that a request for a parameter is ACTUALLY a field name that was spelled incorrectly vs. its original definition. Another source of this error is that the item does not originate from where you thought it did.
__________________
I'm a certified grandpa (3 times now) and proud of it.
Retired over one year and survived being home all day with the wife. She must really love me.
If I have helped you, please either click the thanks or click the scales.
The_Doc_Man is offline   Reply With Quote
Old 09-06-2019, 10:50 AM   #6
June7
AWF VIP
 
June7's Avatar
 
Join Date: Mar 2014
Location: The Great Land
Posts: 2,392
Thanks: 0
Thanked 558 Times in 554 Posts
June7 will become famous soon enough June7 will become famous soon enough
Re: Union query asking to Enter Parameter Values

Date and Type are reserved words - enclose in [ ] to ensure Access interprets as field name (although I have seen situation involving VBA where even [] failed). Advise not to use reserved words as names for anything. http://allenbrowne.com/AppIssueBadWord.html

Also should not use spaces nor punctuation/special characters in naming convention.

If you want to provide db for analysis, follow instructions at bottom of my post.
__________________
Attach File Manager is below Advanced editor window, click Go Advanced below Quick Reply window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG!
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
June7 is offline   Reply With Quote
Old 09-20-2019, 12:03 AM   #7
FueledbyAccess
Newly Registered User
 
Join Date: Sep 2019
Posts: 2
Thanks: 1
Thanked 0 Times in 0 Posts
FueledbyAccess is on a distinguished road
Re: Union query asking to Enter Parameter Values

Thanks all, I have managed to resolve this now. It turned out that the download from the online database was changing the names of the headings depending on which year the report was run for

FueledbyAccess is offline   Reply With Quote
Old 09-20-2019, 07:57 AM   #8
pbaldy
Wino Moderator
 
pbaldy's Avatar
 
Join Date: Aug 2003
Location: Nevada, USA
Posts: 33,247
Thanks: 13
Thanked 4,102 Times in 4,034 Posts
pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold
Re: Union query asking to Enter Parameter Values

Glad you got it sorted and welcome to the site by the way!

__________________
Paul
Microsoft Access MVP 2007-2019

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
pbaldy is offline   Reply With Quote
Reply

Tags
access , parameters , query , union select

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
3 Combobox filtering report -help on enter parameter values annika05 Modules & VBA 0 03-04-2013 06:37 AM
Parameter help in a union query Jmsteph Queries 3 11-07-2011 02:07 PM
Enter Parameter Value despite no Parameter in query reklaw Queries 5 09-15-2011 10:34 AM
Enter/Change field data into a table in Union Query Pelerin13 Queries 15 03-29-2011 01:17 PM
Union Query - enter data to search mshoems Queries 1 04-13-2010 05:42 AM




All times are GMT -8. The time now is 08:33 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World