Current Date problems

declanmc

New member
Local time
Today, 18:12
Joined
Mar 5, 2009
Messages
1
I am creating a database for a Cattery, where cats are boarded. I am creating a query where the end user is told what cats are currently being boarded in the cattery on the present day.

For example:

  • On the 05/03/2009, someone can open the query and see exactly what cats are in the cattery on that date.
The problem is I cannot fathom how to calculate this. The two fields I am working from are Date_From and Date_To, which contain the dates. I have worked out the difference using a DateDiff command but still cannot bring up just Cats that are in the cattery at the present moment.

Thanks in advance,
Declan.
 
try using the Between startdate and endate in a query. That will show you which cats are present.
 
I try to explain it.

In queries you can use the sum function.

Create a number field in your table that holds how many cats belong to one record (NumberOfCats). Mainly this will be one. One cat filed = one record holding the start date and end date.

Field1: sum(NumberOfCats)
Field2: start date / criteria: between xxx and yyy
Field3: end date / critera: between xxx and yyy

Hope this gives you a start.
 
in english the logic would be

if the Datefrom <= To Days Date AND Dateto >= todays date then the cat is still in the cattery.
 
Sorry I missed to tell you that the correct format is


between #10/02/09# and #15/02/09
 
try this, create a new query, view in SQL view, then paste the following (but remember to replace Table1 with the name of table with date fields in):

SELECT Table1.*
FROM Table1
WHERE (((Date()) Between [date_from] And [Date_to]));


View it design view later to see wat its doin.
basically u putting a field with the value of todays date and usinf yr date from and to as the criteria.
Regards
Husan
 

Users who are viewing this thread

Back
Top Bottom