Results 1 to 7 of 7

Thread: Append Query Error --- Enter Parameter Value when None exist
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Dec 2014
    Posts
    194
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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())));

  2. #2
    Board Regular
    Join Date
    Jun 2015
    Location
    Ontario, Canada
    Posts
    1,722
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Append Query Error --- Enter Parameter Value when None exist

    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 by Micron; Sep 26th, 2018 at 11:13 PM. Reason: formatting
    Tips for posting problems:
    1) "doesn't work" doesn't help. Post error message text/numbers and/or state what's happening.
    2) if posting code or sql, use code tags; specify on which code line errors occur, if applicable
    3) try to be specific; assume we know nothing about your issue - because we don't!

    Make all suggested changes in copies of your database or to its objects.

    "1 out of 1010 people understand binary. The other 1001 don't."

  3. #3
    MrExcel MVP
    Moderator
    xenou's Avatar
    Join Date
    Mar 2007
    Location
    Clev. OH, USA
    Posts
    16,492
    Post Thanks / Like
    Mentioned
    12 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Append Query Error --- Enter Parameter Value when None exist

    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.

    Using: Office 2010/Win7 (work) Office 2010/Win7 (home)

    You are rich in proportion to the number of things you can let alone.
    -- Henry David Thoreau

  4. #4
    Board Regular
    Join Date
    Dec 2014
    Posts
    194
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Append Query Error --- Enter Parameter Value when None exist

    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());

  5. #5
    Board Regular
    Join Date
    Jan 2009
    Posts
    1,165
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Append Query Error --- Enter Parameter Value when None exist

    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

  6. #6
    Board Regular
    Join Date
    Dec 2014
    Posts
    194
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Append Query Error --- Enter Parameter Value when None exist

    Quote Originally Posted by james_lankford View Post
    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...

  7. #7
    Board Regular
    Join Date
    Jun 2015
    Location
    Ontario, Canada
    Posts
    1,722
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Append Query Error --- Enter Parameter Value when None exist

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

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •