Using Datediff

[Contro]

Hi there! I hope you can help me!

I have the following code:

SELECT COUNT(Applications.[Date Application Received]) AS TOTAL
FROM Applications
WHERE DateDiff('d',[Date Application Received],Date()) <6 AND
[Statement/Cert Letter/] = "Certificate";

Which returns the number of applications received between todays date
and 5 days previous.

However, I want to modify this so that the user enters both the first
date and the second date. I've tried experimenting by replacing the
Date() with an actual date, but it doesn't return the correct result.
Even if I put the same date that the Date() function would use. I'm
not sure how I get a prompt to appear so that the user can input the
dates anyway, although it sometimes happens for some reason!

Any help would be hugely appreciated! Thank you in advance!

Contro

[fredg]

On 8 Jul 2005 05:07:40 -0700, Contro wrote:

Hi there! I hope you can help me!

I have the following code:

SELECT COUNT(Applications.[Date Application Received]) AS TOTAL
FROM Applications
WHERE DateDiff('d',[Date Application Received],Date()) <6 AND
[Statement/Cert Letter/] = "Certificate";

Which returns the number of applications received between todays date
and 5 days previous.

However, I want to modify this so that the user enters both the first
date and the second date. I've tried experimenting by replacing the
Date() with an actual date, but it doesn't return the correct result.
Even if I put the same date that the Date() function would use. I'm
not sure how I get a prompt to appear so that the user can input the
dates anyway, although it sometimes happens for some reason!

Any help would be hugely appreciated! Thank you in advance!

Contro

I don't understand why you would want to enter both dates, but the
following works for me:
DateDiff("d",[Enter Date1],[Enter Date2])

If you enter 1/1/2005 and 6/1/2005 the difference is 151 days, which
will be returned for each record in the query.

I think you probably would want to enter just one of the arguments
manually
DateDiff("d",[Enter From date],Date())
or
DateDiff("d",[Date Application Received],[Enter To Date])
--
Fred
Please only reply to this newsgroup.
I do not reply to personal email.

[Contro]

That's great! I'll give that a try (probably Monday now), but I'm sure
it will work! I think what got me was the American date setting (I'm
from England). I thought that the code wasn't working as the
application received date is input in the English format (DDMMYYYY
although Month was "MAR" or what not).

Anyway, I'll give that a go, and thank you again!

Contro