Crosstab Query Parameters Using Form

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a form that the users select a project name and enter a date to and
from. I would like to run a cross-tab query that selects the records that
equal the project name and >= date from and <= date to.

I can't seem to get this criteria entered correctly in the parameters. Any
help would be greatly appreciated.

Thanks!
 
You didn't state what your error message is or provide the SQL view of your
query.

Crosstab queries require you to specify the data types of all parameters.
Select Query->Parameters and enter you parameter expessions and select the
data types.
 
The error message is "Invalid bracketing of
[=[Forms]!frmM_MetricsSearch![List1]]". I notice brackets are inserted that
I don't type in the parameters.

The SQL View is:

PARAMETERS [=[Forms]!frmM_MetricsSearch![List1]] Text ( 255 ),
[[>=Forms]!frmM_MetricsSearch![DateFrom And
<=Forms]!frmM_MetricsSearch![DateTo]] DateTime;
TRANSFORM Count(tblS_Leads.ID) AS CountOfID
SELECT tblS_Leads.[Neterion Owner], Count(tblS_Leads.ID) AS [Total Of ID]
FROM tblS_Leads LEFT JOIN tblS_CallResults ON tblS_Leads.ID =
tblS_CallResults.OriginID
GROUP BY tblS_Leads.[Neterion Owner]
PIVOT tblS_Leads.[Lead Quality];
 
I have experience Access putting in invalid bracketing on its own. Modify
your SQL manually to:
PARAMETERS [Forms]!frmM_MetricsSearch![List1] Text ( 255 ),
[Forms]!frmM_MetricsSearch![DateFrom] DateTime,
[Forms]!frmM_MetricsSearch![DateTo] DateTime;
TRANSFORM Count(tblS_Leads.ID) AS CountOfID
SELECT tblS_Leads.[Neterion Owner], Count(tblS_Leads.ID) AS [Total Of ID]
FROM tblS_Leads LEFT JOIN tblS_CallResults ON tblS_Leads.ID =
tblS_CallResults.OriginID
GROUP BY tblS_Leads.[Neterion Owner]
PIVOT tblS_Leads.[Lead Quality];

Keep in mind that this query is not being filtered by the parameters. I you
want to filter based on the parameters, they must be in the Where clause of
this or a source query.

--
Duane Hookom
MS Access MVP
--

Bernie said:
The error message is "Invalid bracketing of
[=[Forms]!frmM_MetricsSearch![List1]]". I notice brackets are inserted
that
I don't type in the parameters.

The SQL View is:

PARAMETERS [=[Forms]!frmM_MetricsSearch![List1]] Text ( 255 ),
[[>=Forms]!frmM_MetricsSearch![DateFrom And
<=Forms]!frmM_MetricsSearch![DateTo]] DateTime;
TRANSFORM Count(tblS_Leads.ID) AS CountOfID
SELECT tblS_Leads.[Neterion Owner], Count(tblS_Leads.ID) AS [Total Of ID]
FROM tblS_Leads LEFT JOIN tblS_CallResults ON tblS_Leads.ID =
tblS_CallResults.OriginID
GROUP BY tblS_Leads.[Neterion Owner]
PIVOT tblS_Leads.[Lead Quality];


Duane Hookom said:
You didn't state what your error message is or provide the SQL view of
your
query.

Crosstab queries require you to specify the data types of all parameters.
Select Query->Parameters and enter you parameter expessions and select
the
data types.
Click to expand...
Click to expand...
 
I don't have experience using the parameter values, so I assumed that is what
filtered the crosstab query. If I use a source query for the crosstab that
sets the criteria on the form values, I receive an error when I run the
crosstab that it doesn't recognize the form and form field that I am using as
a criteria on the source query.

How can you run a crosstab query filtering on values from a form (including
greater than/less than for dates)?

Bernie


Duane Hookom said:
I have experience Access putting in invalid bracketing on its own. Modify
your SQL manually to:
PARAMETERS [Forms]!frmM_MetricsSearch![List1] Text ( 255 ),
[Forms]!frmM_MetricsSearch![DateFrom] DateTime,
[Forms]!frmM_MetricsSearch![DateTo] DateTime;
TRANSFORM Count(tblS_Leads.ID) AS CountOfID
SELECT tblS_Leads.[Neterion Owner], Count(tblS_Leads.ID) AS [Total Of ID]
FROM tblS_Leads LEFT JOIN tblS_CallResults ON tblS_Leads.ID =
tblS_CallResults.OriginID
GROUP BY tblS_Leads.[Neterion Owner]
PIVOT tblS_Leads.[Lead Quality];

Keep in mind that this query is not being filtered by the parameters. I you
want to filter based on the parameters, they must be in the Where clause of
this or a source query.

--
Duane Hookom
MS Access MVP
--

Bernie said:
The error message is "Invalid bracketing of
[=[Forms]!frmM_MetricsSearch![List1]]". I notice brackets are inserted
that
I don't type in the parameters.

The SQL View is:

PARAMETERS [=[Forms]!frmM_MetricsSearch![List1]] Text ( 255 ),
[[>=Forms]!frmM_MetricsSearch![DateFrom And
<=Forms]!frmM_MetricsSearch![DateTo]] DateTime;
TRANSFORM Count(tblS_Leads.ID) AS CountOfID
SELECT tblS_Leads.[Neterion Owner], Count(tblS_Leads.ID) AS [Total Of ID]
FROM tblS_Leads LEFT JOIN tblS_CallResults ON tblS_Leads.ID =
tblS_CallResults.OriginID
GROUP BY tblS_Leads.[Neterion Owner]
PIVOT tblS_Leads.[Lead Quality];


Duane Hookom said:
You didn't state what your error message is or provide the SQL view of
your
query.

Crosstab queries require you to specify the data types of all parameters.
Select Query->Parameters and enter you parameter expessions and select
the
data types.

--
Duane Hookom
MS Access MVP
--

I have a form that the users select a project name and enter a date to
and
from. I would like to run a cross-tab query that selects the records
that
equal the project name and >= date from and <= date to.

I can't seem to get this criteria entered correctly in the parameters.
Any
help would be greatly appreciated.

Thanks!
Click to expand...
Click to expand...
Click to expand...
 
You set the criteria exactly the same as any other query other than having
to set the data types of the parameters:

PARAMETERS [Forms]!frmM_MetricsSearch![List1] Text ( 255 ),
[Forms]!frmM_MetricsSearch![DateFrom] DateTime,
[Forms]!frmM_MetricsSearch![DateTo] DateTime;
TRANSFORM Count(tblS_Leads.ID) AS CountOfID
SELECT tblS_Leads.[Neterion Owner], Count(tblS_Leads.ID) AS [Total Of ID]
FROM tblS_Leads LEFT JOIN tblS_CallResults ON tblS_Leads.ID =
tblS_CallResults.OriginID
WHERE [DateField] Between [Forms]!frmM_MetricsSearch![DateFrom] And
[Forms]!frmM_MetricsSearch![DateTo]
GROUP BY tblS_Leads.[Neterion Owner]
PIVOT tblS_Leads.[Lead Quality];


--
Duane Hookom
MS Access MVP
--

Bernie said:
I don't have experience using the parameter values, so I assumed that is
what
filtered the crosstab query. If I use a source query for the crosstab
that
sets the criteria on the form values, I receive an error when I run the
crosstab that it doesn't recognize the form and form field that I am using
as
a criteria on the source query.

How can you run a crosstab query filtering on values from a form
(including
greater than/less than for dates)?

Bernie


Duane Hookom said:
I have experience Access putting in invalid bracketing on its own. Modify
your SQL manually to:
PARAMETERS [Forms]!frmM_MetricsSearch![List1] Text ( 255 ),
[Forms]!frmM_MetricsSearch![DateFrom] DateTime,
[Forms]!frmM_MetricsSearch![DateTo] DateTime;
TRANSFORM Count(tblS_Leads.ID) AS CountOfID
SELECT tblS_Leads.[Neterion Owner], Count(tblS_Leads.ID) AS [Total Of ID]
FROM tblS_Leads LEFT JOIN tblS_CallResults ON tblS_Leads.ID =
tblS_CallResults.OriginID
GROUP BY tblS_Leads.[Neterion Owner]
PIVOT tblS_Leads.[Lead Quality];

Keep in mind that this query is not being filtered by the parameters. I
you
want to filter based on the parameters, they must be in the Where clause
of
this or a source query.

--
Duane Hookom
MS Access MVP
--

Bernie said:
The error message is "Invalid bracketing of
[=[Forms]!frmM_MetricsSearch![List1]]". I notice brackets are inserted
that
I don't type in the parameters.

The SQL View is:

PARAMETERS [=[Forms]!frmM_MetricsSearch![List1]] Text ( 255 ),
[[>=Forms]!frmM_MetricsSearch![DateFrom And
<=Forms]!frmM_MetricsSearch![DateTo]] DateTime;
TRANSFORM Count(tblS_Leads.ID) AS CountOfID
SELECT tblS_Leads.[Neterion Owner], Count(tblS_Leads.ID) AS [Total Of
ID]
FROM tblS_Leads LEFT JOIN tblS_CallResults ON tblS_Leads.ID =
tblS_CallResults.OriginID
GROUP BY tblS_Leads.[Neterion Owner]
PIVOT tblS_Leads.[Lead Quality];


:

You didn't state what your error message is or provide the SQL view of
your
query.

Crosstab queries require you to specify the data types of all
parameters.
Select Query->Parameters and enter you parameter expessions and select
the
data types.

--
Duane Hookom
MS Access MVP
--

I have a form that the users select a project name and enter a date
to
and
from. I would like to run a cross-tab query that selects the
records
that
equal the project name and >= date from and <= date to.

I can't seem to get this criteria entered correctly in the
parameters.
Any
help would be greatly appreciated.

Thanks!
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Parameter Value in Crosstab Query 4
Parameter Queries in Crosstab Query 2
Multiple Parameter Crosstab query filter by form 1
crosstab query with datea parameters 2
crosstab query parameters for dates 1
Parameter Query with DSum 1
crosstab parameterized query 1
Parameter form for crosstab query 4

Back
Top