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.
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.
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.
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?
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 firstname.lastname@example.org