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

[AlexB123]

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?

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]

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

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.

 

[xenou]

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]

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!!!

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]

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]

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]

FWIW I had a similar experience. Once.
Any such pasting now goes into Notepad first.