Union query asking to Enter Parameter Values (1 Viewer)

FueledbyAccess

New member
Local time
Today, 09:28
Joined
Sep 6, 2019
Messages
2
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. :banghead:
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 02:28
Joined
Aug 30, 2003
Messages
33,760
Do they perhaps have spaces in the actual names rather than underscores?
 

plog

Banishment Pending
Local time
Today, 04:28
Joined
May 11, 2011
Messages
9,623
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.
 

theDBguy

I’m here to help
Local time
Today, 02:28
Joined
Oct 29, 2018
Messages
8,536
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.
 

The_Doc_Man

Happy Retired Curmudgeon
Local time
Today, 04:28
Joined
Feb 28, 2001
Messages
16,142
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.
 

June7

AWF VIP
Local time
Today, 01:28
Joined
Mar 9, 2014
Messages
2,744
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.
 

FueledbyAccess

New member
Local time
Today, 09:28
Joined
Sep 6, 2019
Messages
2
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
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 02:28
Joined
Aug 30, 2003
Messages
33,760
Glad you got it sorted and welcome to the site by the way!
 

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

Top Bottom