Solved Syntax error in Parameter clause for Crosstab Qry (1 Viewer)

[rondon]

Hi
I have never declared a parameter in a query before any help welcomed

I have a crosstab query based on another query called qryCountAgency2 in this query one of the columns is ReportYear which has the criteria set to Between [StartDate] And [EndDate].

When I run my crosstab qry I get a message saying it doesn't recognise StartDate. I have looked this up and it seems I need to declare the Parameter, but I'm not sure which query to put it in the qryCountAgency2 or the Crosstab query. I have a feeling I am writing the Parameter wrong as shown below

thanks Ron

PARAMETERS [ReportYear:] Between [StartDate] and [EndDate];
TRANSFORM Count(qrylCountAgency2.[CountOfAgencyName]) AS CountOfCountOfAgencyName
SELECT qrylCountAgency2.[AgencyName], Count(qrylCountAgency2.[CountOfAgencyName]) AS [Total Of CountOfAgencyName]
FROM qrylCountAgency2
GROUP BY qrylCountAgency2.[AgencyName]
PIVOT qrylCountAgency2.[ReportYear];PARAMETERS [ReportYear:] Between [StartDate] and [EndDate];
TRANSFORM Count(qrylCountAgency2.[CountOfAgencyName]) AS CountOfCountOfAgencyName
SELECT qrylCountAgency2.[AgencyName], Count(qrylCountAgency2.[CountOfAgencyName]) AS [Total Of CountOfAgencyName]
FROM qrylCountAgency2
GROUP BY qrylCountAgency2.[AgencyName]
PIVOT qrylCountAgency2.[ReportYear];

 

[theDBguy]

Try:

PARAMETERS [StartDate] DateTime, [EndDate] DateTime;

Sent from phone...

 

[rondon]

PARAMETERS [StartDate] DateTime, [EndDate] DateTime;

I am entered the Parameter but on entering the date into the parameter box StartDate it returns that it is (not valid for this field) is this because I enter a short date of the Year only eg: 2012. I tried removing Time but it still did not work

Between [StartDate] And [EndDate]

 

[Minty]

You are declaring the parameter as a DateTIme, but entering a number not a date.
Ensure you are comparing eggs with eggs. So either declare and enter a full date and compare dates or enter and declare a number and compare a number.

 

[rondon]

You are declaring the parameter as a DateTIme, but entering a number not a date.
Ensure you are comparing eggs with eggs. So either declare and enter a full date and compare dates or enter and declare a number and compare a number.

Thank you I see this now

 

[theDBguy]

Thank you I see this now

Hi. Good luck with your project.