Here is how I would rework your SQL:
PARAMETERS [begin date] DateTime, [end date] DateTime;
SELECT tblclient.Client,
tblProject.[Project Abbv],
Sum(([stop time]-[start time])*24) AS [Total Time]
FROM (tblclient INNER JOIN tblProject
ON tblclient.ClientID = tblProject.Client)
INNER JOIN tblinput ON (tblProject.ProjectID =
tblinput.Project) AND (tblclient.ClientID = tblinput.Client)
WHERE (((tblinput.[Date]) Between [begin date] And [end date]))
GROUP BY tblclient.Client, tblProject.[Project Abbv];
Do any of the tables (tblclient, tblProject, or tblinput) contain a field
named "end date"? If yes, then you'll need to change the parameter [end
date] to a different "name", such as [ending date].
Also, I note that you're using Date as the name of a control on a form. It
and many other words are reserved words in ACCESS and should not be used for
control names, field names, etc. See these Knowledge Base articles for more
information about reserved words and characters that should not be used:
List of reserved words in Access 2002 and Access 2003
http://support.microsoft.com/default.aspx?scid=kb;en-us;286335
List of Microsoft Jet 4.0 reserved words
http://support.microsoft.com/?id=321266
Special characters that you must avoid when you work with Access
databases
http://support.microsoft.com/?id=826763
See this site for code that allows you to validate your names as not being
VBA keywords:
basIsValidIdent - Validate Names to Make Sure They Aren't VBA Keywords
http://www.trigeminal.com/lang/1033/codes.asp?ItemID=18#18
--
Ken Snell
<MS ACCESS MVP>
GingerVA said:
Hi Ken,
Here is the SQL statement with the isse: SELECT tblclient.Client,
tblProject.[Project Abbv], Sum(([stop time]-[start time])*24) AS [Total
Time]
FROM (tblclient INNER JOIN tblProject ON tblclient.ClientID =
tblProject.Client) INNER JOIN tblinput ON (tblProject.ProjectID =
tblinput.Project) AND (tblclient.ClientID = tblinput.Client)
WHERE (((tblinput.Date) Between [begin date] And [end date]))
GROUP BY tblclient.Client, tblProject.[Project Abbv];
Here is the same SQL statement with the between statement using the exact
dates: SELECT tblclient.Client, tblProject.[Project Abbv], Sum(([stop
time]-[start time])*24) AS [Total Time]
FROM (tblclient INNER JOIN tblProject ON tblclient.ClientID =
tblProject.Client) INNER JOIN tblinput ON (tblProject.ProjectID =
tblinput.Project) AND (tblclient.ClientID = tblinput.Client)
WHERE (((tblinput.Date) Between #5/1/2007# And #5/31/2007#))
GROUP BY tblclient.Client, tblProject.[Project Abbv];
Thanks for your help.
Ginger
:
Post the SQL statement of your query. Most likely, your parameter name
matches the name of a field in the query's field list.
--
Ken Snell
<MS ACCESS MVP>
In my query I have a date field. Under criteria for this field I can
enter
'Between #05/01/2007# and #05/31/2007#' and it returns all records
between
those dates.
But, when I change the parameter criteria to 'Between [begin date] and
[end
date]' and run the query the Enter Parameter Value dialog box pops up
and
allows me to enter the first date. I click ok and the Enter Parameter
Value
dialog box DISAPPEARS! It does not prompt me for the 2nd date!
Any suggestions as to what I can do to fix?
Thanks!
