Performing a Count in a Query

[Guest]

Hi

I have the following SQL code for a query

SELECT Session.Name, Count(Session.[SO Number]) AS CountOfSO
FROM [Session]
WHERE (((Session.Date) Between [Forms]![Date Entry]![StartDate] And
[Forms]![Date Entry]![EndDate]))
GROUP BY Session.Name;

Basically what I want to do is for a user to open a form enter two dates and
for a report to be returned giving the names and the Count of the SO Numbers
that are associated with that name,
At the moment it is working but after I enter in the two dates I get a popup
"enter parameter Value" asking for a date, if I just press ok it works, but I
want to get rid of this extra popup but Iam not sure where it is coming from
in the Query above,

Can you have a look and provide suggestions any suggestions if possible,

Kind Regards,

 

[Douglas J. Steele]

Is form Date Entry open when you're running the query? If not, that's why
you're getting the prompts.

If it is open, have you perhaps misspelled the names of the controls on the
form?

 

[Guest]

Another possibility is that the controls on the form are referenced in the
report, e.g. in an expression as the ControlSource of a text box to show the
date parameters in the report. If the form has been closed by this time
you'd be prompted for the values. If you do want the form to close
automatically do it with code in the report's Close event procedure to ensure
its still open when the query and report need to reference it.

I'd recommend that you declare the parameters as date/time parameter values
entered in short date format might be interpreted as an arithmetical
expression otherwise and give the wrong results. Modify the query like so:

PARAMETERS
[Forms]![Date Entry]![StartDate] DATETIME,
[Forms]![Date Entry]![EndDate] DATETIME;
SELECT etc.

Also I'd suggest you rename the Date column in the query to SessionDate; the
former is the name of a built in function, and should therefore be avoided as
an object name. If you do use it as a column name be sure to wrap it in
brackets: [Date]. The same applies to Name which would be better as
SessionName.

And finally, make sure that in the table definition the column is validated
to accept only values with a zero time of day, DateValue(SessionDate),
otherwise you might find that rows with dates on the final day of the date
range are not returned by the query. You can be sure of picking these up,
however, if you use the following alternative syntax:

WHERE SessionDate >= [Forms]![Date Entry]![StartDate]
AND SessionDate < [Forms]![Date Entry]![EndDate] + 1

Ken Sheridan
Stafford, England

 

[Guest]

Hi

Thanks for your help, it turned out to be a problem with the report, I
created a new report and it seems to work ok now, thanks again

Another possibility is that the controls on the form are referenced in the
report, e.g. in an expression as the ControlSource of a text box to show the
date parameters in the report. If the form has been closed by this time
you'd be prompted for the values. If you do want the form to close
automatically do it with code in the report's Close event procedure to ensure
its still open when the query and report need to reference it.

I'd recommend that you declare the parameters as date/time parameter values
entered in short date format might be interpreted as an arithmetical
expression otherwise and give the wrong results. Modify the query like so:

PARAMETERS
[Forms]![Date Entry]![StartDate] DATETIME,
[Forms]![Date Entry]![EndDate] DATETIME;
SELECT etc.

Also I'd suggest you rename the Date column in the query to SessionDate; the
former is the name of a built in function, and should therefore be avoided as
an object name. If you do use it as a column name be sure to wrap it in
brackets: [Date]. The same applies to Name which would be better as
SessionName.

And finally, make sure that in the table definition the column is validated
to accept only values with a zero time of day, DateValue(SessionDate),
otherwise you might find that rows with dates on the final day of the date
range are not returned by the query. You can be sure of picking these up,
however, if you use the following alternative syntax:

WHERE SessionDate >= [Forms]![Date Entry]![StartDate]
AND SessionDate < [Forms]![Date Entry]![EndDate] + 1

Ken Sheridan
Stafford, England

Hi

I have the following SQL code for a query

SELECT Session.Name, Count(Session.[SO Number]) AS CountOfSO
FROM [Session]
WHERE (((Session.Date) Between [Forms]![Date Entry]![StartDate] And
[Forms]![Date Entry]![EndDate]))
GROUP BY Session.Name;

Basically what I want to do is for a user to open a form enter two dates and
for a report to be returned giving the names and the Count of the SO Numbers
that are associated with that name,
At the moment it is working but after I enter in the two dates I get a popup
"enter parameter Value" asking for a date, if I just press ok it works, but I
want to get rid of this extra popup but Iam not sure where it is coming from
in the Query above,

Can you have a look and provide suggestions any suggestions if possible,

Kind Regards,