Append Query Error --- Enter Parameter Value when None exist

AlexB123

Board Regular
Joined
Dec 19, 2014
Messages
194
Hi all,

I have a large database and I am attempting to build a separate archive database. For now, while testing and building, I am just trying to start by writing an append query to a new table in my database. This table, tblArchiveItems, was created using the structure only of tblItems. I want to archive any Items that were loaded in to the database more than three years ago.

There is a field in tblItems, "Date_Loaded" that is filled by a query when new items are loaded to the table --- it merely calls the "Now()" function ... so the data type of "Date_Loaded" is Date/Time.

In my query below, every time I run it, it asks me to enter the parameter value of "Date_Loaded". I have checked for typos, I have checked to make sure the two tables are of the same design and data types. What could cause Access to ask me for a parameter value? How can I get this append query to work?

Code:
SELECT a.ItemDBID, a.Item_Name, a.Item_Number, a.Item_Date, a.Item_Source, a.Item_Type, a.Date_Loaded 
INTO tblArchiveItems
FROM tblItems a
WHERE (((a.Date_Loaded)>DateAdd("m",-36,Date())));
 

Micron

Well-known Member
Joined
Jun 3, 2015
Messages
1,782
shouldn't this

FROM tblItems a be FROM tblItems As a

Not sure if that would cause your problem as that doesn't look like a typical sql statement for an Access append query.
I would expect more like
Code:
INSERT INTO [tblArchiveItems] ([FIELD1], [FIELD2], FIELDn...)
SELECT a.ItemDBID, a.Item_Name, a.Item_Number, a.Item_Date, a.Item_Source, a.Item_Type, a.Date_Loaded
FROM tblItems As a
WHERE (((a.Date_Loaded)>DateAdd("m",-36,Date())));
where [FIELD1] etc are the target field names, whose count has to be the same as the SELECT portion field count.
 
Last edited:

xenou

MrExcel MVP, Moderator
Joined
Mar 2, 2007
Messages
16,587
Office Version
2013
Platform
Windows
It shouldn't be a make table query if (as you say) the table already exists.

I know you said you checked for typos etc. but that's all you can do is keep checking for typos really.

Also you can try these debugging attempts:
Make sure that Date_Loaded isn't a calculated field or something weird like that.
Try it with the problem field and again without the problem field.
Turn your query into a select statement to check what it is getting from the source table, then turn it back into an insert into query.
Rebuild the target table.

Note that I almost never use "As" in MSAccess for my table aliases so that's not a problem here.
 

AlexB123

Board Regular
Joined
Dec 19, 2014
Messages
194
I ended up removing the parentheses in the WHERE statement after I tried to use another field for the criteria. Once it worked, I replaced the new criteria with "Date_Loaded" and it ran!

Any ideas as to why the following worked? (Note: I realized I also needed to change ">" to "<").

Thanks!!!

Code:
SELECT a.ItemDBID, a.Item_Name, a.Item_Number, a.Item_Date, a.Item_Source, a.Item_Type, a.Date_Loaded 
INTO tblArchiveItems
FROM tblItems a
WHERE (a.Date_Loaded) < DateAdd("m",-36,Date());
 

james_lankford

Well-known Member
Joined
Jan 11, 2009
Messages
1,171
is it possible you had an invisible character after a.Date_Loaded ?

I once write a query at home, then emailed it to my work address
then when I got to work I opened the email and copied the query into Access

it wouldn't work -- it wouldn't return any rows when I could see the freaking row right there in the table

Chrome had combined multiple spaces into a single space and a special html character

that html character was messing everything up

I couldn't see it (it looked like a space to me-- but it wasn't a space and Access knew it wasn't a space)

spent hours on that before I finally figured it out
 

AlexB123

Board Regular
Joined
Dec 19, 2014
Messages
194
is it possible you had an invisible character after a.Date_Loaded ?
No, ever since I spent two days hunting down extra spaces in another database, that's one of the first things I check! LOL.

It definitely had something to do with the parentheses, which were around the entire "WHERE" statement. My working theory is that Access was looking for parameters because it thought it was part of a single expression? I dunno...
 

Micron

Well-known Member
Joined
Jun 3, 2015
Messages
1,782
FWIW I had a similar experience. Once.
Any such pasting now goes into Notepad first.
 

Forum statistics

Threads
1,085,934
Messages
5,386,841
Members
402,022
Latest member
recyclonic

Some videos you may like

This Week's Hot Topics

Top