Results 1 to 5 of 5
  1. #1
    slimjen is offline Expert
    Windows XP Access 2007
    Join Date
    Mar 2010
    Posts
    727

    Post append query criteria

    All, using access 2010. I have a table that contains custid and state fields. Some customers can be in more than one state.



    Ex.
    Code:
    CustID   State
          1234      CA
          1234      IN
    I am trying to identify new customers and create an append query to add them once a month. But; as I stated; some customers may already be in one or more states and just adding another state therefore there are already in the table. So I need to append where both custid and state is not in the table not just CustID. When I try to use 2 where clauses;

    Code:
    where(([tblcust].[custid] is null and where ([tblcust].[state] is null))
    I get an error the expression is not valid. Can I use two where clauses when trying to append records that are not in the table? Please help.

  2. #2
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Only one key word is allowed in a SQL statement (unless it is a sub query).
    So try:
    Code:
    WHERE [tblcust].[custid] Is Null AND [tblcust].[state] Is Null


    BTW, "state" is a reserved word......

  3. #3
    wonder06 is offline Novice
    Windows 8 Access 2010 32bit
    Join Date
    Jan 2016
    Posts
    3
    Thanks for your reply. I made the changes as suggested and also change the criteria. It works but tblCust.Custid and tblCust.CState enter parameter popup asking for the user for input. I don't want the user to be confused by this each time they run the query. How do I get rid of this and still make this query work please?

    Code:
    INSERT INTO tblCust ( Custid, CState )
    SELECT tblstcu.cust, tblstcu.nstate
    FROM tblstcu
    WHERE (((tblstcu.cust) Not In ([tblCust].[Custid])) AND ((tblstcu.nstate) Not In ([tblCust].[CState])));

  4. #4
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    How do I get rid of this and still make this query work please?
    You do realize I know nothing about your database or your process/logic, right?


    As to why you are getting the pop-ups:
    Code:
    INSERT INTO tblCust ( Custid, CState )
    SELECT tblstcu.cust, tblstcu.nstate
    FROM tblstcu
    WHERE (((tblstcu.cust) Not In ([tblCust].[Custid])) AND ((tblstcu.nstate) Not In ([tblCust].[CState])));
    you cannot refer to fields in a table like this. The table must be included in the select SQL. Or you could use code to populate a variable and concatenate the variable in the SQL. Or refer to controls on a form that is open.

  5. #5
    slimjen is offline Expert
    Windows XP Access 2007
    Join Date
    Mar 2010
    Posts
    727
    Got it solved. Thanks all for your suggestions.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Using max date as criteria in append query?
    By Sam_NY in forum Queries
    Replies: 5
    Last Post: 10-30-2015, 04:13 PM
  2. Append Query destination Criteria
    By armyofdux in forum Queries
    Replies: 1
    Last Post: 03-09-2015, 09:20 AM
  3. Replies: 8
    Last Post: 04-13-2014, 02:28 PM
  4. Replies: 1
    Last Post: 03-23-2010, 04:01 PM
  5. Append query double criteria
    By Gerry in forum Queries
    Replies: 0
    Last Post: 03-23-2010, 03:55 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums