big troublewith date calculations 1

[NeilBelgium]

I've created several tables with short date as the type, and am trying to run queries that make calculations from these dates.

Have tried using now (), date (), but keep getting syntax and other errors.

How DO you calculate the following:

The difference between two dates, eg 4/1/2002 and 10/1/2002 (the non-american way round!). The answer should be six days, but how can it be achieved?

Also, how filter records that have a date either before today (<?) or after today (>?)

this is causing me no end of trouble,n and all help gratefully accepted!!

thanks, neil

 

[hsitz]

The DateDiff function can be used to calculate intervals in all sorts of units. To calculate number of days between your two dates, e.g., it would be:

datediff(&quot;d&quot;, #4/1/2002#, #10/1/2002#)

I'm not sure how your handling date formatting in European way, since on my computer the above function would return around 180 days (6 months x 30 days/month).

You should have no problem if you just put values in variable and reference variables, though:

datediff(&quot;d&quot;, Date1, Date2)

It would be worthwhile to read the online documenation for the datediff function.

the Date() function will return today's date. If you want to use it as a query criterion you could do it by putting something like this in the criteria box under a query column:

> date()
>= date()
< date()

-- Herb

<= date()

 

[NeilBelgium]

this is great, thanks! I'd read most of the access help topics, but never found the DateDiff function. I guess you either know it's there or you don't...........!

neil

 

[zinkey]

can u please tell me how i can programm dates on microsoft access. like for example if i were given starting date of an agreement and the ending date, how can i generate the remaining dates when ever i want to check?
hope to get an answer soon!
thanks

 

[zinkey]

how can add months and years to a date.. for example i want to see the date when i add 3 months to a specified date..i am really having trouble doing this.. could please help me..

 

[WildHare]

DATEADD(/interval/ , number of intervals, StartingDate)

Dateadd(&quot;m&quot;, 3, Date()) adds 3 months to today.

DateAdd(&quot;d&quot;, 7, SomeDate) adds 7 days to some date.

Dateadd(&quot;y&quot; , -10 , SomeDate) subtracts 10 years from some date.

How many of you believe in telekinesis? Raise my hand...
Another free Access forum:

http://www.forumco.com/jmhare

More Access stuff at

http://www.jmhare.com/wildhare2.htm

 

[WildHare]

Perhaps another thing to keep in mind is that if you are adding or subtracting pure DAYS, you can just add or subtract them from the date guy:

OneWeekfromToday = Date() + 7

OneWeekAgo = Date() - 7

Jim How many of you believe in telekinesis? Raise my hand...
Another free Access forum:

http://www.forumco.com/jmhare

More Access stuff at

http://www.jmhare.com/wildhare2.htm