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

AlexB123

Board Regular
Joined
Dec 19, 2014
Messages
207
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())));
 

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
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:
Upvote 0
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.
 
Upvote 0
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());
 
Upvote 0
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
 
Upvote 0
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...
 
Upvote 0
FWIW I had a similar experience. Once.
Any such pasting now goes into Notepad first.
 
Upvote 0

Forum statistics

Threads
1,213,560
Messages
6,114,309
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top