Results 1 to 7 of 7
  1. #1
    Join Date
    Jul 2002
    Posts
    35

    Red face Unanswered: trying to run query with dates

    I am trying to create a query that runs of the date of a record.

    I have 2 tables, I've simplified them for my example.

    Contracts Table has 3 fields: acc #, start, expire
    Purchase table has 3 fields: acc #, date of purchase, amount

    I am trying to create a query that has the acc #, start, expire info from the contracts table and the date of purchase and amount from the 2nd table, but the date of purchase has to fall between the start and the expire date.

    I have tried to add the following in the criteria section of a query, under the field, date of purchase.

    >=[contracts].[start] and <=[contracts].[expire]

    between [contracts].[start] and [contracts].[expire]

    >=#[contracts].[start]# and <=#[contracts].[expire]#. This one actually won't run, but I tried.

    Since customers get discounts for having a contract. I want to know which of their purchases will get the discount. They could have bought before the contract started and bought after it expired.

    Any suggestions, Thanks

  2. #2
    Join Date
    Jul 2002
    Posts
    7
    Your first criteria should do the job. I have created a mini test db and attached for you to use or view rather. You may want to check the format of the fields in your tables to be sure they are both on date.

    I hope this helps.
    Attached Files Attached Files

  3. #3
    Join Date
    Jul 2002
    Posts
    35
    Thanks for the help but I downloaded the zip file but was unable to open the database, it said it was an unrecognizable file. Any suggestions?

    Thanks

  4. #4
    Join Date
    Jul 2002
    Posts
    7
    Well lets see.

    Here is the sql for the query i built inside the db ~ the tables were really simple, your fields as you described above.


    SELECT Table1.acctNum, Table2.datep, Table2.amt, Table1.start, Table1.exp
    FROM Table1 INNER JOIN Table2 ON Table1.acctNum = Table2.acct
    WHERE (((Table1.acctNum)=[Acct Number:]) AND
    ((Table2.datep)>=[start] And (Table2.datep)<=[exp]));

    Hope this helps. Your setup of the formats of the fields in the tables is the key though.

  5. #5
    Join Date
    Jul 2002
    Posts
    35
    I would really like to see what the database you created. Maybe I could'nt view it because you created it in a newer version of Access. Unfortunatly I am currently using Access 97. Maybe you can save it under this version?

    Thanks for all your help.

    Parra

  6. #6
    Join Date
    Jul 2002
    Posts
    7
    I am using access 2k, and your right you can't open it with 97, but you can import the table and queries from that db to one you are using. If you still have the file I attached then, create a blank db in your current version of access. Then import the tables and queries into your db from the one in the file. To import the tables / query in the db window right click and it will give you a pop menu that has an import entry. Be sure that the file is uncompressed already. It was zipped when i put it on the board.

    If you have an email address i can send it to you directly with out zipping it. If you would like me to do so please shoot me a quick note to drpichon@hotmail.com

  7. #7
    Join Date
    Jul 2002
    Posts
    35
    Tried what you recommended but I got the same error message. I will go around the company and see who has the newer version. Thanks

Posting Permissions

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