preview report based on NumOfDays

[hengsin]

How can i preview the report based on Number of Days, let
say it's less than 14 days? Since the Number of Days is
calculated using VBA code on the report itself. If there
is a field in the query is NumOfDays, i know it can be
done easily by using query. But there is no NumOfDays in my query.

THanks

 

[Steve101]

If you subtract two date fields from each other, then the result is the number of days between them. So:
[tt]
#1/15/2003# - #1/2/03#
[/tt]
returns a result of 13.

I'll assume that you have a date field called YourDateField in your query, and you want to compare it to see whether its within 14 days of the current date. The SQL would look something like this:
[tt]
SELECT *
FROM tblYourTable
WHERE Date() - YourDateField + 1 <= 14
[/tt]

Note:
(a) The +1 bit is to make the dates inclusive
(b) The 14 could be made dynamic by placing it on a form which is open at the time the query is run. If the form is frmYourForm, and the control is called txtNumDays, then the SQL would be:
[tt]
...
WHERE Date() - YourDateField + 1 <= val(Forms!frmYourForm!txtNumDays)
...
[/tt]

Hope this helps,


Steve Lewy
Solutions Developer
steve@lewycomputing.com.au
(dont cut corners or you'll go round in circles)

 

[hengsin]

thanks, it'w working