Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2006

    Unanswered: Append Query and Validation problems


    I am doing an A level project and I need to build a database for a business that sells and orders products.

    I have a couple of problems that I hope someone can help me with.

    1. Firstly I am trying to make an append query that will move data from my "invoices" table (which contains data on the products sold) to my "archive" table. I would like data from the "invoices" table that is more than three months old to be transferred to the "archive" table. So far I have tried putting different formulas in the "date" field criteria in the query design view. I am not sure what expression to put in and so far everything I have tried has been unsuccessful.

    2. I also have a products table. It has the product ID, product name, Minimum quantity, maximum quantity and stock level. I would like to put some kind of validation on the "quantity" field so that when the stock level gets too high or too low, a message appears on the screen to tell the user. I tried typing in an expression in the validation rule box in the table design rule, =between[ProductsMinimumQuantity]And[ProductsMaximumQuantity] But I'm guessing that didn't work because my between values aren't numeric? Is there some kind of query i could perform that I could link to the table or a products form when I open it?

    I hope I've made sense and I'm sorry if my problem is too simple or if it seems like im on here to cheat! I'm surrounded by books and tearing my hair out, I don't think my knowledge of databases is enough to do what I want to do!!

  2. #2
    Join Date
    Feb 2004
    New Zealand
    Provided Answers: 8
    in the criteria in the date coloum

    <dateadd("d",-90,date()) this will only show data that is Less than 90 days old
    could use the <dateadd("m",-3,date()) I like the using the day count

    What i have done is written a reorder query that get print each morning @ 6.00am then @ lunch time
    hope this help

    See clear as mud

    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008

  3. #3
    Join Date
    Nov 2006
    Thanks that works a treat!! Although now I'm getting an error message saying it didn't add three record due to key violations. It still works I think. this message appeared after I added an extra record to my invoices table.

    For the reorder query does it just show products that are not between the maximum and minimum levels, or however you work it out?
    So a list is printed of what needs to be reordered?

Posting Permissions

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