Does anyone know of a workaround...

[Turb]

... for not being able to use user input parameters in a summing query?
I have this:

SELECT MPC00.TS_NUMBER, MPC00.CLAIM_RCVD, MPC00.RINV_AM_PD, MPC00.MODEL_NAME, Sum(DSum("RINV_AM_PD","COSTS")) AS TOTAL
FROM MPC00
WHERE (((MPC00.CLAIM_RCVD) Between #8/1/2006# And #8/30/2006#))
GROUP BY MPC00.TS_NUMBER, MPC00.CLAIM_RCVD, MPC00.RINV_AM_PD, MPC00.MODEL_NAME
ORDER BY MPC00.TS_NUMBER DESC;

And would love to be able to have the user input the date range.
I tried using a straight select query to "feed" the summing query, but that didn't work either.
Please Help!

 

[rjoubert]

Create a form with two text boxes for the date range, and put a button on the form to run the query. Behind the button click event, dynamically create the sql and run the query.

"WHERE (((MPC00.CLAIM_RCVD) Between #" & Me.StartDate.Value & "# And #" & Me.EndDate.Value & "#))

 

[Turb]

rjoubert,
Thank you for your response.
Um... dynamically create the sql?
Not sure I understand.

 

[dhookom]

There should be no issues with using parameter prompts or better yet references to controls on a form. I'm really confused by the CLAIM_RCVD in the SELECT and GROUP BY fields. Also using Sum of a DSum() is very unconventional since there is no where clause in the DSum().

Can you tell us what you are attempting to do?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[Turb]

dhookom,
The issue arises from using parameter prompts in a query, not on a query underlaying a form. It seems that a form is the only way to get around this.
The sql I included above is a query in sql view and CLAIM_RCVD is a date field.
I am using "Sum(DSum("RINV_AM_PD","COSTS")) AS TOTAL" because for some reason when I use it this way I get exactly two decimals, whereas if I use "DSum("RINV_AM_PD","COSTS") AS TOTAL" I get six decimal places.

 

[dhookom]

The number of decimal places shouldn't make a difference since this should be formatted in your report or form.

I'm still not sure what issue you had attempting to use either a parameter prompt or a criteria based on controls on a form. [CLAIM_RCVD] is a field in your results so most any method should work.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[Turb]

dhookum,
I think your missing the point; I was not using a form or report, I was actually using a query. But could not input parameters and sum in the same query; if I tried to sum, I got this error:

The expression you entered as a query parameter produced this error: "Microsoft Office Access can't find the name 'Enter Start Date' you entered in the expression.

I assumed that Access couldn't do both in one query (or for that matter, in two sequential queries) and asked if anyone knew a work-around for this issue.
rjoubert was kind enough to point me in a new direction, but I'm not sure what he meant by "dynamically create the sql".
I can have a command button on a form open the query, but what is me.startdate.value to a query?

 

[lespaul]

I doubt Duane is missing the point....

Access can do both a sum and parameters in the same query.

me.startdate.value refers to the text box on the form (assuming the name is 'startdate') and me is a shortcut for referring to the form you are working with.

It looks like you want to sum the costs that are in a different table. How are the two tables related (or what field is the same in both tables?) Replace the SomeField below with that field and get a join. You may also have to declare the parameters before the SQL.

SELECT M.TS_NUMBER, M.CLAIM_RCVD, M.RINV_AM_PD, M.MODEL_NAME, Sum(R.COSTS) AS TOTAL
FROM MPC00 M
INNER JOIN RINV_AM_PD R ON M.SomeField = R.SomeField
WHERE (((MPC00.CLAIM_RCVD) Between [Enter Start Date] And [Enter End Date]))
GROUP BY M.TS_NUMBER, M.CLAIM_RCVD, M.RINV_AM_PD, M.MODEL_NAME
ORDER BY M.TS_NUMBER DESC;

As far as your problem with the decimals, as Duane said, you should concern yourself with formatting in the FORM or REPORT that you will display the query results in. If you aren't showing the query to anyone, why does it matter how many decimal points?

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi

 

[Turb]

Leslie,
I'm sorry, I'm getting frustrated and perhaps I haven't been entirely clear.
All data is coming from a single table named MPC00.
The query I've been tying to get to take user input on the CLAIM_RCVD field and sum the RINV_AM_PD field is named COSTS.
I have been, rather ineffectively, trying to get the query to give me the information from the TS_NUMBER, CLAIM_RCVD, RINV_AM_PD, MODEL_NAME fields of table MPC00 while summing the RINV_AM_PD field, but only doing so using the data from a specific date range (CLAIM_RCVD) as entered by the user.
I have only been working with a form since recieving the suggestion from rjoubert. And I'm not entirely sure of how to "dynamically create the sql" behind a command button of a form.

 

[lespaul]

ok, so you have some data in a table MPC00.....what does the data look like in the table? What do you want the results of the query to look like?

So you have created a form, that has, what, two text boxes or some other control, for the start and end date? what did you name those controls?

What do you want to happen when you press the button on the form and run the query? Display the query results in a datasheet? in a report?


Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi

 

[PHV]

Something like this ?
PARAMETERS [StartDate] DateTime, [EndDate] DateTime;
SELECT TS_NUMBER, CLAIM_RCVD, RINV_AM_PD, MODEL_NAME, Sum(RINV_AM_PD) AS COSTS
FROM MPC00
WHERE CLAIM_RCVD Between [StartDate]And [EndDate]
GROUP BY TS_NUMBER, CLAIM_RCVD, RINV_AM_PD, MODEL_NAME
ORDER BY TS_NUMBER DESC

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886