Expression Error on Parameter Query with link to Cross Tab Query

  • Thread starter Thread starter Ann B
  • Start date Start date
A

Ann B

I 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.[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]), DateDiff("d",[Job Launch
Date],[1st Slate Date]), DateDiff("d",[1st Slate Date],[Final Slate Date]),
DateDiff("d",[Final HM Interview],[Offer Requested]), DateDiff("d",[Offer
Requested],[Offer Accepted]), DateDiff("d",[Offer Accepted],[Start Date]),
DateDiff("d",[Post Date],[Start Date]), DateDiff("d",[1st HM Interview
Date],[Final HM Interview]), Applicant.Status
HAVING (((Requisition.Status)="Filled") AND ((Applicant.[Start Date])
Between [Forms]![SubCycle Reporting Form]![Startdate] And [Forms]![Subcycle
Reporting Form]![Enddate]) AND ((Applicant.Status)="Hired"));
 
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
 
I will try removing the First name first. I had to do that because before I
made those two joins I was getting the same record twice or as many
applicants that were tied to the Req file-but the same name in all records.
I only wanted to display the Hire for the subcycle time.

It runs fine if I take out the date parameter all together. But they want
to limit it so they can run it by month for performance reports. It is
basically a time to fill report and showing the sub-cycle times of the
different stages in the hiring process.
 
Also 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 said:
I 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.[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]), DateDiff("d",[Job Launch
Date],[1st Slate Date]), DateDiff("d",[1st Slate Date],[Final Slate Date]),
DateDiff("d",[Final HM Interview],[Offer Requested]), DateDiff("d",[Offer
Requested],[Offer Accepted]), DateDiff("d",[Offer Accepted],[Start Date]),
DateDiff("d",[Post Date],[Start Date]), DateDiff("d",[1st HM Interview
Date],[Final HM Interview]), Applicant.Status
HAVING (((Requisition.Status)="Filled") AND ((Applicant.[Start Date])
Between [Forms]![SubCycle Reporting Form]![Startdate] And [Forms]![Subcycle
Reporting Form]![Enddate]) AND ((Applicant.Status)="Hired"));
Click to expand...
 
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
 
The cross tab query doesn't have any parameters itself. But I have some
fields linked in my base query to fields in a crosstab query. Does that make
sense? So if I need to put the parameters in the linked crosstab
query...what field would I put them in since none of those fields have a
parameter attached to them? Should I just put the parameters in the dialog
box of the crosstab query?
 
Did this-but getting
Invalid Bracketing of name [Between [Forms]![SubCycle Reporting
Form]![Startdate] And [Forms]![Subcycle Reporting Form]![Enddate]

What brackets do I need to remove in the SQL view?
 
You should have

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

You have TWO parameters so you need to declare them both. The Between
and AND are not parameters they are comparison operators and DO NOT
belong in the parameters clause.


'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================


Ann said:
Did this-but getting
Invalid Bracketing of name [Between [Forms]![SubCycle Reporting
Form]![Startdate] And [Forms]![Subcycle Reporting Form]![Enddate]

What brackets do I need to remove in the SQL view?

John Spencer said:
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
Click to expand...
Click to expand...
 
Bless you. Thank you very much. I was really sweating this. So much so I
was sick to my stomache. Thank you for your knowledge, patience and
expertise.

John Spencer said:
You should have

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

You have TWO parameters so you need to declare them both. The Between
and AND are not parameters they are comparison operators and DO NOT
belong in the parameters clause.


'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================


Ann said:
Did this-but getting
Invalid Bracketing of name [Between [Forms]![SubCycle Reporting
Form]![Startdate] And [Forms]![Subcycle Reporting Form]![Enddate]

What brackets do I need to remove in the SQL view?

John Spencer said:
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
Click to expand...
Click to expand...
Click to expand...
 
Back
Top