Firstly you don't have to change [Start date:] and [End date:] to actual
dates. These are the parameters. A parameter in a query accepts a value
from outside the query when its run (or indeed when a form or report based on
the query is opened). With a simple parameter like these Access pops up a
dialogue box for each prompting you to enter the dates. This works fine but
a more developed approach is to reference controls on a form as the
parameters; this means you'd open the form, enter the two dates in text boxes
or select them from combo boxes which list the relevant dates, and then open
the query (or better still a form or report based on the query) with a button
on the form. In a scenario like this the parameters would then take the form
of Forms!frmDatesDlg!txtStartdate and Forms!frmDatesDlg!txtEndDate.
Lets break the query down:
PARAMETERS [Start date:] DATETIME,
End date:] DATETIME;
This declares the two parameters as date/time data type, so when you enter a
date at the prompt Access knows it’s a date you are entering. A date such as
02/01/08 might otherwise be interpreted as an arithmetical expression 2
divided by 1 divided by 8, which is of course 0.25. Access implements dates
as a 64 bit floating point number with the integer part representing days and
the fractional part the time of day. Zero is 30 December 1899 00:00:00 (its
important to understand that date time values are always a point in time, so
there is no such thing as date value per se, rather one with a zero time of
day, i.e. midnight at the start of the day. So 0.25 is one quarter way into
day zero, i.e. 30 December 1899 06:00:00. Your query is unlikely to find
such a date time value in any row, I'd imagine!
Note how the individual parameter declarations are delimited by commas and
the PARAMETERS clause is terminated by a semi-colon.
SELECT "Year from " & [Start date:] & " to " & [End date:]
AS InvoiceYear,
DATEDIFF("d",Date1, Date2) AS InvoiceDuration,
COUNT (*) As Occurrences
This is the query's SELECT clause and determines which columns it returns.
The first column simply concatenates a couple of literal strings with the
dates you enter as the parameters at runtime, so it tells you what date range
is being looked at. The 'AS InvoiceYear' just gives this column a name.
Remember that if you use spaces or other special characters in object names,
however, you'll need to wrap them in brackets, e.g. [Invoice Year].
The second column subtracts the date time values in the two columns using
the DateDiff function (SQL keywords, function names etc are usually written
in upper case in SQL, with table and column names in lower or mixed case ( I
tend to use CamelCase where the words are joined up with the start of each
one capitalised, though some SQL programmers favour proper case for table
names and all lower case for column names).
The third column just counts the rows. Using the asterisk character counts
all rows in each group (determined by the GROUP BY clause, about which more
below).
FROM Invoices
This simply means the query uses a table called Invoices. As for this
example I've assumed only one table is being used the table is not joined to
any other(s) in a JOIN clause.
WHERE Date1 BETWEEN [Start date:] AND [End date:]
AND Date2 IS NOT NULL
The WHERE clause restricts the query to those rows where Date1 is between
(inclusively) the two dates entered as the parameter values, and where Date2
contains a date (IS NOT NULL). A WHERE clause restricts the data before
grouping it, i.e. only the restricted data is grouped and counted. In query
design view you'll sometimes include a column in a GROUP BY clause and put a
criterion in the same column in the design grid. This will give you a HAVING
clause not a WHERE clause. A HAVING clause restricts the data after grouping
it. People often create a HAVING clause incorrectly in this way when they
should create a WHERE clause. To do the latter in design view you add the
column a second time to the grid, select 'where' in the 'totals' row and
uncheck the 'show' checkbox. The legitimate use of a HAVING clause usually
acts on an aggregated value, e.g. you might want to restrict the results to
only those rows where the difference between the dates is more than 5 days,
for which the HAVING clause would be:
HAVING COUNT(*) > 5
A HAVING clause follows the GROUP BY clause, whereas a WHERE clause precedes
it.
GROUP BY DATEDIFF("d",Date1,Date2);
The GROUP BY clause groups the query's results by the difference between the
two dates, so it counts the rows for each distinct number of days returned in
the computed InvoiceDuration column. The semi colon terminates the SQL
statement.
Finally I see absolutely no reason why you can't use a BETWEEN….AND
operation, as I've done in my example. One thing to note about a
BETWEEN….AND operation, however, is that if any rows have dates on the final
days of the range, but with a non-zero time of day (which might not be
readily apparent due to the formatting of the column) those rows won't be
returned as if the final day is 31 March 2008 say, then only rows with a
value at midnight at the start of that day will be returned; anything on that
day with a non-zero time of day is outside the range. To prevent this
include a validation rule for each of the date columns in table design of:
DateValue([Date1])
and
DateValue([Date2])
respectively.
Before doing this you can correct any existing errant dates with update
queries:
UPDATE Invoices
SET Date1 = DATEVALUE(Date1)
WHERE Date1 IS NOT NULL;
and:
UPDATE Invoices
SET Date2 = DATEVALUE(Date2)
WHERE Date2 IS NOT NULL;
Ken Sheridan
Stafford, England
GregB said:
Ken, can you explain your code to me, i do understand basic sql statements
but i havn't used one with paramaters
PARAMETERS [Start date:] DATETIME,
[End date:] DATETIME;
SELECT "Year from " & [Start date:] & " to " & [End date:]
As InvoiceYear,
DATEDIFF("d",Date1, Date2) AS InvoiceDuration,
COUNT (*) As Occurrences
FROM Invoices
WHERE Date1 BETWEEN [Start date:] AND [End date:]
AND Date2 IS NOT NULL
GROUP BY DATEDIFF("d",Date1,Date2);
*Do i change all the [Start date:] and [End date:] to the years i want
or do i change DATETIME to the years I want in the paramters (or is that
stating the format), if so is there a certain format.. 02/01/08 etc
Also can someone explain to me, so that I am actually learning and don't
have to post everytime I have a question
why i couldn't use the Between
function?
Thanks again.. you guys are awsome!
