Union query asking to Enter Parameter Values

[FueledbyAccess]

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]

Do they perhaps have spaces in the actual names rather than underscores?

 

[plog]

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]

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]

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]

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]

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]

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