Crosstab query prompts seven times for date range

[keith2511]

I have a crosstab query that takes the start and end date parameters from a form. This form is used in several other queries [which work perfectly] but the one below prompts the user for the date range seven times. The parameter definitions are the same in each query. The one difference between this and my other queries is that this is grouping by month, while others are grouping on different fields. Any idea why there are several prompts for the date range?
Thanks

 PARAMETERS [Forms]![Date_Range]![Start_Date] DateTime, [Forms]![Date_Range]![End_Date] DateTime;TRANSFORM Round(Sum([Total_Net]),2) AS Expr2
SELECT [All_Invoices_18-19].Supplier, Sum([All_Invoices_18-19].Total_Net) AS [Total Of Total_Net]
FROM [All_Invoices_18-19]
WHERE (((Format([Invoice_Date],"yyyy/mm")) Between [Forms].[Date_Range].[Start_Date] And [Forms].[Date_Range].[End_Date]))
GROUP BY [All_Invoices_18-19].Supplier
PIVOT Format([Invoice_Date],"yyyy/mm");

[pbaldy]

Potentially stupid question, is the form open when the query runs? I don't see offhand why it would prompt if so, particularly 7 times. That's just opening the query, not a form/report based on the query?

[CJ_London]

might have something to do with the fact you have [Forms]![Date_Range]![Start_Date] in the parameters and [Forms].[Date_Range].[Start_Date] in the criteria (bang and dot)

also be surprised if the criteria works anyway

WHERE (((Format([Invoice_Date],"yyyy/mm")) Between [Forms].[Date_Range].[Start_Date] And [Forms].[Date_Range].[End_Date]))

you are comparing a string with a date

would think it needs to be

WHERE ((([Invoice_Date]) Between [Forms].[Date_Range].[Start_Date] And [Forms].[Date_Range].[End_Date]))

[pbaldy]

Arg, I missed the bang v dot.

I assume the user enters an appropriate string on the form, since the same method works in other queries. Don't like the method since it requires the function to be applied to every record in the table.

[keith2511]

Thanks Ajax, that has worked.