Results 1 to 11 of 11
  1. #1
    matt4003 is offline Novice
    Windows XP Access 2003
    Join Date
    Dec 2009
    Posts
    10

    Query Criteria Lost After Table Update

    Hello All,



    I finally figured out (with the help of RuralGuy) how to query the right price from a part table.

    Now, I lose the Criteria I wrote in the query when I make changes to the part table it is referencing....I don't understand why I would lose the criteria in any case.....

    Ideas?

    Thanks,
    Matt

  2. #2
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows XP Access 2002
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    Can you give us a little more information to go on? Is it a static query. saved with a name? What changes are you making to the table it references?

  3. #3
    matt4003 is offline Novice
    Windows XP Access 2003
    Join Date
    Dec 2009
    Posts
    10
    Hello RuralGuy,

    Yes, this is a static query. It is saved with a name. Seems my problem is even stranger than originally thought. Originally, I thought the query lost the criteria when I changed one of the reference tables, but I just tested it again, and here is the process I used.
    - Added the criteria in: (remember the part_number query you helped me with)
    Part_Number field
    date () >= tbl.price!Start_date AND date() <= tbl.End_date!
    - Run query (query has a prompt for start date and end date)
    Works great.
    - Save the query.
    - Open the query again, it prompts me to add the start and end date
    Works great and close the query, but don't save and it doesn't ask me to save, shouldn't need to.
    - Open the query again, it prompts me to add the start and end date
    Now it doesn't work. It adds every price in the tblprice for each part (total of 4 prices).

    Why would this happen?? Do I make sense??

    Thanks,
    Matt

  4. #4
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows XP Access 2002
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    How about posting the SQL view of your query?

  5. #5
    matt4003 is offline Novice
    Windows XP Access 2003
    Join Date
    Dec 2009
    Posts
    10
    Does this help?

    SELECT tblconsumption.ship_memo, tblconsumption.part_number, tblconsumption.quantity, tblSite.site_location, tblconsumption.date, Month([date]) AS [Month], tblSpecs.nick_name, tblSpecs.material_type, tblconsumption.[po number], tblreceipts.type, Sum(qryNetReceipts.SumOfquantity) AS SumOfSumOfquantity, tblreceipts.rec_date, tblSpecs.price, Sum([price]*tblconsumption!quantity) AS Revenue, [date]-[rec_date] AS [Days in Consignment], tblShipped.[Siltronic Plant], tblShipped.[ship date], [rec_date]-[ship date] AS [Transit Time], tblconsumption.Comment, tblSpecs.price_start, tblSpecs.price_end
    FROM tblShipped RIGHT JOIN ((tblreceipts RIGHT JOIN (tblSite INNER JOIN (tblconsumption INNER JOIN tblSpecs ON tblconsumption.part_number = tblSpecs.part_name) ON tblSite.site_code = tblconsumption.plant) ON tblreceipts.lot_number = tblconsumption.lot_number) LEFT JOIN qryNetReceipts ON tblconsumption.lot_number = qryNetReceipts.lot_number) ON tblShipped.[Del document] = tblconsumption.ship_memo
    GROUP BY tblconsumption.ship_memo, tblconsumption.part_number, tblconsumption.quantity, tblSite.site_location, tblconsumption.date, Month([date]), tblSpecs.nick_name, tblSpecs.material_type, tblconsumption.[po number], tblreceipts.type, tblreceipts.rec_date, tblSpecs.price, tblShipped.[Siltronic Plant], tblShipped.[ship date], [rec_date]-[ship date], tblconsumption.Comment, tblSpecs.price_start, tblSpecs.price_end
    HAVING (([date]>=[price_start] And [date]<=[price_end]) AND ((tblconsumption.date)>=[Start Date] And (tblconsumption.date)<=[End Date]))
    ORDER BY tblconsumption.date;

  6. #6
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows XP Access 2002
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    It looks to me like it should ask for [Start Date] and [End Date] every time it is run. BTW, Date is a reserved word.

  7. #7
    matt4003 is offline Novice
    Windows XP Access 2003
    Join Date
    Dec 2009
    Posts
    10
    Thanks for the hint about "date". I fixed that, but still having the problem of the query ignoring or releasing my criteria's. So here is the SQL before I lose it:
    SELECT tblSite.site_location, tblconsumption.con_date, tblconsumption.ship_memo, tblconsumption.part_number, tblconsumption.quantity, tblSpecs.part_name, tblSpecs.material_type, tblSpecs.price, tblconsumption!quantity*[price] AS Revenue, tblSpecs.price_start, tblSpecs.price_end
    FROM tblSite INNER JOIN (tblSpecs INNER JOIN tblconsumption ON tblSpecs.part_name = tblconsumption.part_number) ON tblSite.site_code = tblconsumption.plant
    GROUP BY tblSite.site_location, tblconsumption.con_date, tblconsumption.ship_memo, tblconsumption.part_number, tblconsumption.quantity, tblSpecs.part_name, tblSpecs.material_type, tblSpecs.price, tblconsumption!quantity*[price], tblSpecs.price_start, tblSpecs.price_end
    HAVING (((tblconsumption.con_date)>=[Start Date] And (tblconsumption.con_date)<=[End Date]) AND ([con_date]>=[price_start] And [con_date]<=[price_end]))
    ORDER BY tblconsumption.con_date;


    Here is the SQL after I lose the criteria:
    SELECT tblSite.site_location, tblconsumption.con_date, tblconsumption.ship_memo, tblconsumption.part_number, tblconsumption.quantity, tblSpecs.part_name, tblSpecs.material_type, tblSpecs.price, tblconsumption!quantity*[price] AS Revenue, tblSpecs.price_start, tblSpecs.price_end
    FROM tblSite INNER JOIN (tblSpecs INNER JOIN tblconsumption ON tblSpecs.part_name = tblconsumption.part_number) ON tblSite.site_code = tblconsumption.plant
    GROUP BY tblSite.site_location, tblconsumption.con_date, tblconsumption.ship_memo, tblconsumption.part_number, tblconsumption.quantity, tblSpecs.part_name, tblSpecs.material_type, tblSpecs.price, tblconsumption!quantity*[price], tblSpecs.price_start, tblSpecs.price_end
    HAVING (((tblconsumption.con_date)>=[price_start] And (tblconsumption.con_date)<=[price_end]))
    ORDER BY tblconsumption.con_date;

    Do I need to "save as" after I write a criteria and have a brand new query.

    This is really weird. I have never had problems before losing criteria that was hard coded into a static query.

  8. #8
    matt4003 is offline Novice
    Windows XP Access 2003
    Join Date
    Dec 2009
    Posts
    10
    Looking at the SQL in the Forum View, which is nice for comparing, it is clear that the HAVING ((tblconsumption.con_date)>= .... these switched on their own...I didn't change anything, other than close (after a save) and reopen. Weird.

  9. #9
    matt4003 is offline Novice
    Windows XP Access 2003
    Join Date
    Dec 2009
    Posts
    10
    Sorry for all the posts....working through it and discovering things as I go.

    Okay, I figured it out. Because "date" was a reserved word I had problems there. Then my query was [con_date]>=[price_start] AND [con_date]<=[price_end] under the part_number field in critera. Well that was causing problems with the [Start Date] and [End Date]. So I changed the part_number field criteria to date()>=[price_start] AND date()<=[price_end], the program added it as an expression at the end of the query and kept it. Now that I am using Date correctly, it seems to be holding and work great.

    RuralGuy, sorry again for the posts and extra work, but your help has helped me learn and solve the problem...thanks!

    Cheers,
    Matt

  10. #10
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows XP Access 2002
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    When you say Open and Close the query, what are you doing? Are you just double clicking the query from the database window?

  11. #11
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows XP Access 2002
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    That's great Matt! No need to apologize for anything. My job is to just guide you until you learn the solution. Have a Happy New Year.

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

Similar Threads

  1. Replies: 1
    Last Post: 03-23-2010, 04:01 PM
  2. Update Query- selective update?
    By stephenaa5 in forum Queries
    Replies: 1
    Last Post: 10-29-2009, 11:15 AM
  3. Replies: 4
    Last Post: 09-03-2009, 02:01 PM
  4. Lost report formatting
    By kiltedcueball in forum Database Design
    Replies: 2
    Last Post: 06-23-2009, 12:28 AM
  5. Completly lost...
    By fairytalesrcute in forum Access
    Replies: 1
    Last Post: 05-14-2009, 09:24 AM

Tags for this Thread

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