Query question

[BobNTN]

SELECT TblCustInfo.CID, TblCustInfo.Name, TblCustInfo.Addr, TblCustInfo.Zip, TblCustInfo.Cancel, TblCustInfo.SrvAmt
FROM TblCustInfo
GROUP BY TblCustInfo.CID, TblCustInfo.Name, TblCustInfo.Addr, TblCustInfo.Zip, TblCustInfo.Cancel, TblCustInfo.SrvAmt
HAVING (((TblCustInfo.Cancel)="n") AND ((TblCustInfo.SrvAmt)>[?]));

This query lists all customers and their service amounts that are greater than a prompted parameter input. As in, I input 22, it lists all customers with service charge amounts greater than 22.

I would like to add a column in a report that shows the difference in the SrvAmt and the input amount (parameter).
How does one capture the input amount to use in an expression to subtract it from the SrvAmt ?
Ex. I input 20, the SrvAmt is 34, the new 'diff' column would show 14.

I have tried every way I can think of to no avail.

As usual, probably something simple.

 

[pbaldy]

TblCustInfo.SrvAmt - [?] AS CalculatedField

As long as the text in the brackets is the same, you should only get prompted once.

 

[lcook1974]

I know this isn't the "best" way but I'm not familiar with VBA enough to guide you in that direction.

What I would I do is set the parameter as a "form" text box instead of having the parameter pop up through the query.

you can still run the query the same but instead of [?] you would have a button to run the report after you put in that value in a text box.

Then run the report and somewhere on that report have the report reference the text box with the number in it and do the calculation on the report.

If you post the DB I'll try to get you a sample on there and repost.

Larry

 

[lcook1974]

Hi Paul,
you beat me to it...

if you are using brackets as the paramenter in the query definition itself [Enter value here ] , how do you "save" that value?

Larry

 

[pbaldy]

I would also use a form to gather the user's input Larry. I'm not sure what you mean by "save" the value, but the answer is likely that you can't. It has a lifetime limited to the query being open.

 

[BobNTN]

Don't need to save any data.
What I am trying to do is=

Look at all customers whose monthly charge (SrvAmt) is more than a specified amount in anticipation of reducing some or all of those by the difference due to competition offering reduced rates, and, by the same report, see what financial impact it would be.

I can do the simple listing based on an amount entered, but I would like to add to the report what the impact would be.

It isn't really necessary, just a thought that would give needed info at a glance.

But it appears it is too difficult for me to do.

I did take the advice and added a pop-up form for entering the criteria amount.

 

[pbaldy]

Did you try what I posted? That would be very simple.

 

[BobNTN]

TblCustInfo.SrvAmt - [?] AS CalculatedField

As long as the text in the brackets is the same, you should only get prompted once.

Not sure I understand about the brackets nor making SrvAmt a calculated field

I do queries in design view since I am not that comfortable writing code

 

[pbaldy]

In design view:

CalculatedField: SrvAmt - [?]

or if you're using a form now:

CalculatedField: SrvAmt - Forms!FormName.TextboxName

 

[BobNTN]

Works perfect Paul.

As usual, you have bailed me out.

Thank you so much.

Also, thanks Larry for the form suggestion. Seems I understand that better.

And thanks to the rest for you time and suggestions.

Ain't a better forum anywhere nor nicer more helpful people.

 

[pbaldy]

Happy to help!

 

[BobNTN]

One more thing.
How do I close the little form automatically when the report is closed ?

 

[pbaldy]

Use DoCmd.Close with the appropriate arguments in the close event of the report.