Access Query- perameter box wont go away & Report grouping and subtotals

[Seattle5674]

Hello,



I am looking to fix a few quiries because they have the peramter box showing up whent they should not. How do I fix the peramter box from not showing up? example below




I am also trying to fix this report with grouping and subtotals. It current look like this below:



and I want it to look like this below:



any advise helps, thank you

[pbaldy]

The parameter prompt is Access telling you it can't find whatever is specified. In your case, I don't see Date Spent in either table, hence the prompt. Where is it you expect Access to get this value?

[pbaldy]

Oh, and you didn't show how the report design looks now. Looks like you need to group by customer and put the customer details in the group header, the vehicle details in the detail section.

[Seattle5674]

Hi pbaldy,

Here is the good news, I was able to get rid of the peramater box, however it is only when I have to show a field.

in the example below, I want the Date field to be gone when I run the query.






I had to add in the rental start and end dates to get the number of days between them- as a calculated field. I then had to take that date results number and * it by the price per day to get total spend. If I uncheck the SHOW box for Date: it brings up that permater box when I run it. That is what I want to get rid of.

[pbaldy]

Just do the math in Total Spent. Put your actual field names in:

Total Spent: Price * (End - Start)

You shouldn't need any of the other fields if you don't want them, even if they're used in the calculation.

[pbaldy]

By the way, spaces and symbols in your object names are not worth the trouble in the long run. Some use underscores, I like camel case: PricePerDay.

[Seattle5674]

I tried that and this is the result I got. are the brackets right? How do I avoid #NUM

Total Spent: [Price Per Day]*[Rental End Date]-[Rental Start Date]

[CJ_London]

you need to take account of calculation priorities - multiplication/division is calculated before addition/subtraction - see Pauls post#5 re placing of brackets

in case you are not aware, dates are stored as decimal type numbers. Today is 44590 and now (including time) is 44590.4668171296 where the value after the dp is the number of seconds so far today divided by 86400 (number of seconds in a day). How you see it as a date is just formatting which will be ignored when calculating

So if your date fields contain a time element hidden by formatting, your calculation will be wrong

I would also add that if a customer rents for less than a day - picks up in the morning, returns in the afternoon, your rental period will be 0.

[Seattle5674]

Thank you, I was able to add the right brackets () on my calc field and it worked.

[Micron]

Thank you, I was able to add the right brackets () on my calc field and it worked.

If you haven't heard of BEDMAS for calculations, you might want to check it out.