Sum Query With Parameters

[channell]

Hello,

I have a report that when it opens, a form pops up so information can be
entered in. I have slightly restructured the Parameter Query the report is
linked to, and now the Form pops up completely blank. I know what is causing
it, but I can't fix it to save my life. Here is what is going on.

I changed the query to a Sum Query for a Sum of Volume that is being
produce. The query works great. The report works great. But, the Form to
input information does not work, it just pops up completely blank. I even
tried putting in a sup query that is a Sum query, but that returned the same
result. I tried using Sum on the Volume, and then 'Where' on all the other
fields, but again, this really did not work.

Any suggestions would be of great value! Thank you so much for all your help!

Query:

[DAILYINFO ID]
[Volume] 'Which I need summed up

And that is about it.

-Scott Channell

 

[Amy E. Baggott]

Is the form bound to the query at all?

 

[channell]

Yes, the form is bound to the Query with Parameters. Should I unbind it? I
don't even know if that would work? The form that pops open asks for asks
for dates and also an item from a combo box. Hope that helps.

-Scott Channell

Is the form bound to the query at all?
--
Amy E. Baggott

"I''m going crazy and I''m taking all of you with me!" -- Linda Grayson

Hello,

I have a report that when it opens, a form pops up so information can be
entered in. I have slightly restructured the Parameter Query the report is
linked to, and now the Form pops up completely blank. I know what is causing
it, but I can't fix it to save my life. Here is what is going on.

I changed the query to a Sum Query for a Sum of Volume that is being
produce. The query works great. The report works great. But, the Form to
input information does not work, it just pops up completely blank. I even
tried putting in a sup query that is a Sum query, but that returned the same
result. I tried using Sum on the Volume, and then 'Where' on all the other
fields, but again, this really did not work.

Any suggestions would be of great value! Thank you so much for all your help!

Query:

[DAILYINFO ID]
[Volume] 'Which I need summed up

And that is about it.

-Scott Channell

 

[Ken Sheridan]

In most cases an unbound form would be used for entering parameters, but a
bound form can also be used. In your case I suspect it should be unbound as
it sounds like all you need to do is supply values for the parameters by
entering the dates and selecting an item from a combo box. The report's
query would then reference the controls on the unbound form as parameters.
The form would be opened first and the report opened from a button on the
form. There are a few other things you need to consider:

1. Date parameters in a query should always be declared as such in the
query as otherwise they might be interpreted by Access as arithmetical
expressions if entered in short date format.

2. If you are using a BETWEEN….AND operation in the query to restrict the
results by a date range you must ensure that the date/time values in the
table all have zero times of day. Otherwise define the date range
differently using '>= start date and < end date +1'.

3. As you've changed the query to an aggregating query you may well have
inadvertently created a HAVING clause in the query when you should have a
WHERE clause.

If you post the SQL of the query here we may be able to help further.

Ken Sheridan
Stafford, England

Yes, the form is bound to the Query with Parameters. Should I unbind it? I
don't even know if that would work? The form that pops open asks for asks
for dates and also an item from a combo box. Hope that helps.

-Scott Channell

Is the form bound to the query at all?
--
Amy E. Baggott

"I''m going crazy and I''m taking all of you with me!" -- Linda Grayson

Hello,

I have a report that when it opens, a form pops up so information can be
entered in. I have slightly restructured the Parameter Query the report is
linked to, and now the Form pops up completely blank. I know what is causing
it, but I can't fix it to save my life. Here is what is going on.

I changed the query to a Sum Query for a Sum of Volume that is being
produce. The query works great. The report works great. But, the Form to
input information does not work, it just pops up completely blank. I even
tried putting in a sup query that is a Sum query, but that returned the same
result. I tried using Sum on the Volume, and then 'Where' on all the other
fields, but again, this really did not work.

Any suggestions would be of great value! Thank you so much for all your help!

Query:

[DAILYINFO ID]
[Volume] 'Which I need summed up

And that is about it.

-Scott Channell

 

[channell]

Ken Sheridan,

I cannot thank you enough. It never once occurred to me that I didn't have
to bind my pop up forms to the same query my reports were running from. See,
they have been working fine all this time (past 4 or 5 months), until I
aggregate my Queries. This has saved me a tremendous amount of time. This
works perfectly!

You have helped me so much these past months, and I cannot thank you enough.
I truly appreciate all you do Ken.

Sincerely,

Scott Channell

In most cases an unbound form would be used for entering parameters, but a
bound form can also be used. In your case I suspect it should be unbound as
it sounds like all you need to do is supply values for the parameters by
entering the dates and selecting an item from a combo box. The report's
query would then reference the controls on the unbound form as parameters.
The form would be opened first and the report opened from a button on the
form. There are a few other things you need to consider:

1. Date parameters in a query should always be declared as such in the
query as otherwise they might be interpreted by Access as arithmetical
expressions if entered in short date format.

2. If you are using a BETWEEN….AND operation in the query to restrict the
results by a date range you must ensure that the date/time values in the
table all have zero times of day. Otherwise define the date range
differently using '>= start date and < end date +1'.

3. As you've changed the query to an aggregating query you may well have
inadvertently created a HAVING clause in the query when you should have a
WHERE clause.

If you post the SQL of the query here we may be able to help further.

Ken Sheridan
Stafford, England

Yes, the form is bound to the Query with Parameters. Should I unbind it? I
don't even know if that would work? The form that pops open asks for asks
for dates and also an item from a combo box. Hope that helps.

-Scott Channell

Is the form bound to the query at all?
--
Amy E. Baggott

"I''m going crazy and I''m taking all of you with me!" -- Linda Grayson


:

Hello,

I have a report that when it opens, a form pops up so information can be
entered in. I have slightly restructured the Parameter Query the report is
linked to, and now the Form pops up completely blank. I know what is causing
it, but I can't fix it to save my life. Here is what is going on.

I changed the query to a Sum Query for a Sum of Volume that is being
produce. The query works great. The report works great. But, the Form to
input information does not work, it just pops up completely blank. I even
tried putting in a sup query that is a Sum query, but that returned the same
result. I tried using Sum on the Volume, and then 'Where' on all the other
fields, but again, this really did not work.

Any suggestions would be of great value! Thank you so much for all your help!

Query:

[DAILYINFO ID]
[Volume] 'Which I need summed up

And that is about it.

-Scott Channell