Lets put the other part in and see how it goes. While we are at it we can
tidy up another thing. When you put a criterion in a 'shown' column in
design view in a grouped query Access creates a HAVING clause rather than a
WHERE clause. It normally doesn't make any difference but a HAVING clause is
really for operating on the results after grouping, e.g. to return only
customers whose orders sum to more than 10,000 USD or GBP. When restricting
the query before grouping a WHERE clause should be used, so try pasting this
into the query in SQL view in place of the current SQL:
PARAMETERS
[Forms]![WeeklyDriver]![Text2] DATETIME,
[Forms]![WeeklyDriver]![Text4] DATETIME;
SELECT agent.AGENT_NAME, schedheader.[Date],
MAX(schedheader.SCHED_ID) AS MaxOfSCHED_ID,
LAST(schedheader.SCHEDULE_LENGTH) AS LastOfSCHEDULE_LENGTH
FROM agent INNER JOIN schedheader
ON agent.AGENT_ID = schedheader.AGENT_ID
WHERE agent.AGENT_NAME NOT LIKE 'test*' AND
schedheader.[Date] >= [Forms]![WeeklyDriver]![Text4] AND
schedheader.[Date] < DATEADD("d",1,[Forms]![WeeklyDriver]![Text4])
GROUP BY agent.AGENT_NAME, schedheader.[Date];
Note that I've wrapped the [Date] column name in square brackets to avoid it
being confused with the Date function. As its already qualified with the
table name there should be no confusion, but its best to play safe. Ideally
Date should not be used as a column name.
I notice you've used the LAST operator. This might not necessarily give you
what you are aiming at here. If it’s the value in the row in the date range
with the latest date you might need to use a subquery to identify this rather
than the LAST operator.
Ken Sheridan
Stafford, England
Bunky said:
Hi Ken,
I may have been a bit premature. I looked at the SQL code (shown below) and
the greater than side of the expression is missing leaving just the less than
side. This returns entirely too many records (very large files!) or just
goes into program not responding. I have cut and pasted your code several
times and saved it but Access always modifies it.
PARAMETERS [Forms]![WeeklyDriver]![Text2] DateTime,
[Forms]![WeeklyDriver]![Text4] DateTime;
SELECT agent.AGENT_NAME, schedheader.DATE, Max(schedheader.SCHED_ID) AS
MaxOfSCHED_ID, Last(schedheader.SCHEDULE_LENGTH) AS LastOfSCHEDULE_LENGTH
FROM agent INNER JOIN schedheader ON agent.AGENT_ID = schedheader.AGENT_ID
GROUP BY agent.AGENT_NAME, schedheader.DATE
HAVING (((agent.AGENT_NAME) Not Like 'test*') AND
((schedheader.DATE)<DateAdd("d",1,[Forms]![WeeklyDriver]![Text4])));
Bunky said:
Ken,
You nailed it! Works perfectly! Many Thanks Again!
Kent
:
It sounds like you are doing it in design view not SQL view. In design view
you'd put the following, as one line, in the 'criteria' row of the column:
=[Forms]![WeeklyDriver]![Text2] And [Date]<DateAdd("d",1,[Forms]![WeeklyDriver]![Text4])
After you save the query Access will have moved things around if you open it
in design view again, but the underlying logic is uncjanged.
Ken Sheridan
Stafford, England
:
Hi Ken!
We haven't conversed for some time!
First off I declared the PARAMETERS as you sugguested but, alas, to no
avail. Then I cut and pasted your Where clause in the criteria of the query
and when I try to execute the query two things occur.
1. It automatically puts double quotes around the "Where [Date]" See example
"WHERE [Date]">=[Forms]![WeeklyDriver]![Text2] And
[Date]<DateAdd("d",1,[Forms]![WeeklyDriver]![Text4])
2. If I try to execute it anyway, I get the message that "The expression is
incorrectly typed or too complex to be evaluated."
What am I doing incorrectly?
Here is the entire SQL Code:
PARAMETERS [Forms]![WeeklyDriver]![Text2] DateTime,
[Forms]![WeeklyDriver]![Text4] DateTime;
SELECT schedheader.DATE, Max(schedheader.SCHED_ID) AS MaxOfSCHED_ID,
Last(schedheader.SCHEDULE_LENGTH) AS LastOfSCHEDULE_LENGTH
FROM schedheader
GROUP BY schedheader.DATE
HAVING (("WHERE [Date]">=[Forms]![WeeklyDriver]![Text2] And
[Date]<DateAdd("d",1,[Forms]![WeeklyDriver]![Text4])));
As Always, Thank you so Much for your help and all your co-horts as well.
:
Try declaring the parameters:
PARAMETERS
[Forms]![WeeklyDriver]![Text2] DATETIME,
[Forms]![WeeklyDriver]![Text4] DATETIME;
SELECT etc
Its always a good idea to declare date/time parameters as otherwise they
might be misinterpreted as arithmetical expressions. This won't produce an
error only an incorrect result as the value to which the arithmetical
expression evaluates will correspond to a date due to Access's implementation
of the date/time data type as a 64 bit floating point number. The resulting
date/time value probably won't be in your database however; its more than
likely one on 30 December 1899, which is day-zero in Access.
BTW if you cannot guarantee that the date/time values all have a zero time
of day element you'll be on safer ground using:
WHERE [Date] >= [Forms]![WeeklyDriver]![Text2] AND [Date] < DATEADD("d",1,
[Forms]![WeeklyDriver]![Text4])
rather than a BETWEEN….AND operation. If a date/time on the final day of
the range contains a non-zero time of day (which might not be readily
apparent) then it won't be returned by a BETWEEN….AND operation, but will be
using the alternative method of defining the range.
Ken Sheridan
Stafford, England
:
I have a query that is reading a linked (via OBDC) Table from a 3rd party
vendor. I am passing a date to it that is being chosen in a calander popup.
The code I am using in the query is Between [Forms]![WeeklyDriver]![Text2]
and [Forms]![WeeklyDriver]![Text4]. I do not get any results. When I enter
the data directly (ie between #03/01/09# and #03/16/09#, I get results. When
I put Between [Enter start date] And [Enter End Date] in the query; no
results. I have had others check my spelling etc. and it all looks correct.
Any Ideas? Oh, the field I am pulling on is named 'Date' by the Vendor.
