Developer Forum

have wrote a query based on a form. A few of the fields I needed to show
in the query I had to get from a Cross tab query. Now my form parameters are
not working. I need it to run between two dates. I have double checked all
items and I see no visable errors. But I need help. I am getting a Jet
Database engine does not recognize the expression. I found a help article
that said I should add the parameters to the Query dialog box as well to
resolve this issue. But it has not and I get a seperate error that says
invalid bracketing. Here is the SQL statement. (without the Parameters
Dialog box filled in):

SELECT First(Requisition.Cos) AS FirstOfCos, Requisition.Status,
Requisition.[Function Title], Requisition.[Position Title],
Requisition.[Business title], Requisition.[Sub Business], Requisition.[Band
Title], Requisition.[HRM Email], Requisition.[Hiring Manager],
Requisition.Replacement, Requisition.[Search Strategy], Requisition.[Posting
Type], Requisition.[HeadCount Type], Requisition.[Location City],
Requisition.[Location Country], Requisition.[Location SCZ],
Requisition.Recruiter, Requisition.[Staffing Logistics Specialist],
Requisition.[Sourcing Recruiter], First(Applicant.[First Name]) AS
[FirstOfFirst Name], First(Applicant.[Last Name]) AS [FirstOfLast Name],
Applicant.Source, Applicant.[Specific Source], Applicant.[Pipeline Hire],
Requisition.[Post Date], Requisition.[Job Launch Date], Requisition.[1st
Slate Date], Requisition.[Final Slate Date], Requisition.[1st HM Interview
Date], Requisition.[Final HM Interview], [Tracker Event Cross Tab 2].[Offer
Requested], [Tracker Event Cross Tab 2].[Offer Accepted], Applicant.[Start
Date], DateDiff("d",[Post Date],[Job Launch Date]) AS [Post to Job Launch],
DateDiff("d",[Job Launch Date],[1st Slate Date]) AS [Launch to 1st Slate],
DateDiff("d",[1st Slate Date],[Final Slate Date]) AS [1st Slate to Final
Slate], DateDiff("d",[Final HM Interview],[Offer Requested]) AS [Final HM
Interview to Offer Requested], DateDiff("d",[Offer Requested],[Offer
Accepted]) AS [Offer Requested to Offer Accepted], DateDiff("d",[Offer
Accepted],[Start Date]) AS [Offer Accepted to Start], DateDiff("d",[Post
Date],[Start Date]) AS [Post to Start]
FROM ((Applicant INNER JOIN Requisition ON Applicant.Cos = Requisition.Cos)
INNER JOIN [Applicant Event History] ON (Applicant.Cos = [Applicant Event
History].Cos) AND (Applicant.[First Name] = [Applicant Event History].[First
Name]) AND (Applicant.[Last Name] = [Applicant Event History].[Last Name]))
INNER JOIN [Tracker Event Cross Tab 2] ON ([Applicant Event History].[Last
Name] = [Tracker Event Cross Tab 2].[Last Name]) AND ([Applicant Event
History].[First Name] = [Tracker Event Cross Tab 2].[First Name]) AND
(Requisition.Cos = [Tracker Event Cross Tab 2].Cos)
GROUP BY Requisition.Status, Requisition.[Function Title],
Requisition.[Position Title], Requisition.[Business title], Requisition.[Sub
Business], Requisition.[Band Title], Requisition.[HRM Email],
Requisition.[Hiring Manager], Requisition.Replacement, Requisition.[Search
Strategy], Requisition.[Posting Type], Requisition.[HeadCount Type],
Requisition.[Location City], Requisition.[Location Country],
Requisition.[Location SCZ], Requisition.Recruiter, Requisition.[Staffing
Logistics Specialist], Requisition.[Sourcing Recruiter], Applicant.Source,
Applicant.[Specific Source], Applicant.[Pipeline Hire], Requisition

this is just a guess but it may be because you are doing this

First(Applicant.[First Name]) AS
[FirstOfFirst Name], First(Applicant.[Last Name]) AS [FirstOfLast
Name],

you could just try diong one first in that table

it could be the complex joins you are using as well

but thats all just speculation it is hard to diagnose it unless it is
in a format that can be played with

lso when I do add it to the parameter dialog box I get the invalid
bracketing error. So I have seen that you can go into SQL and remove the
extra brackets. When I do that I get Syntax Error in PARAMETER CLAUSE. I
can run this without the parameters-but they need them. I edited them to
look like this:

PARAMETERS Between Forms![SubCycle Reporting Form]![StartDate] And
Forms![SubCycle Reporting Form]![Enddate] DateTime;

I also tried with brackets around the forms-same error. Help.

"Ann B" wrote:

First thing, with a crosstab query you MUST declare your parameters and
if any other queries are used in the crosstab their parameters must also
be declared in that query

++ Open the query in design mode
++ Select Query: Parameters from the Menu
++ Fill in the EXACT name of the parameter in column 1
++ Select the data type of the parameter in column 2