Parameters Expression In A CrossTab Query

  • Thread starter Thread starter Brian
  • Start date Start date
B

Brian

I am trying to restrict dates [TransDate] in a crosstab query. I have tried
the following expression suggested by a community member but it ignores the
parameters -
PARAMETERS [Start Date:] DateTime, [End Date:] DateTime;
TRANSFORM Sum([PLINV&EXPPart2].Amt) AS SumOfAmt
SELECT [PLINV&EXPPart2].TransDate
FROM [PLINV&EXPPart2]
GROUP BY [PLINV&EXPPart2].TransDate
PIVOT [PLINV&EXPPart2].DebExp;

Can someone please put me right? Assuming I get the query to work I would
prefer to go a step further and possibly get the dates passed from a form
with a calendar control
 
Hi Brian,

You are inputting the desired parameters, but not doing anything with them.
You need either a WHERE or HAVING clause in your query, but you don't have
one. You need to put the parameters in the appropriate criteria field(s) of
your query.

HTH,

Rob
 
Thanks for the tip Rob, I did what you suggested an it worked fine so many
thanks for that.
I am now going for broke.
I got clever and used the following expression where the dates are coming
from a form but I keep geting syntax error messages.
Can I ask you
Is the where statement correct?
Do I whack the whole string in the parameter declaration box of just some
part of it?
Thanks
Brian
PARAMETERS Between [forms]![Main menu]![txtstartdate] AND [forms]![main
menu]![txtenddate] DateTime;
TRANSFORM Sum([PLINV&EXPPart2].Amt) AS SumOfAmt
SELECT [PLINV&EXPPart2].TransDate
FROM [PLINV&EXPPart2]
WHERE ((([PLINV&EXPPart2].TransDate) Between [Forms]![Main
Menu]![txtstartdate] And [forms]![Main Menu]![txtenddate]))
GROUP BY [PLINV&EXPPart2].TransDate
PIVOT [PLINV&EXPPart2].DebExp;


Rob Parker said:
Hi Brian,

You are inputting the desired parameters, but not doing anything with them.
You need either a WHERE or HAVING clause in your query, but you don't have
one. You need to put the parameters in the appropriate criteria field(s) of
your query.

HTH,

Rob
I am trying to restrict dates [TransDate] in a crosstab query. I
have tried the following expression suggested by a community member
but it ignores the parameters -
PARAMETERS [Start Date:] DateTime, [End Date:] DateTime;
TRANSFORM Sum([PLINV&EXPPart2].Amt) AS SumOfAmt
SELECT [PLINV&EXPPart2].TransDate
FROM [PLINV&EXPPart2]
GROUP BY [PLINV&EXPPart2].TransDate
PIVOT [PLINV&EXPPart2].DebExp;

Can someone please put me right? Assuming I get the query to work I
would prefer to go a step further and possibly get the dates passed
from a form with a calendar control
Click to expand...
Click to expand...
 
The PARAMETERS clause of your query should contain the parameter names and
datatypes, as a comma-separated list. In your case, it will be:

PARAMETERS [forms]![Main menu]![txtstartdate] DateTime, [forms]![main
menu]![txtenddate] DateTime;

HTH,

Rob

Thanks for the tip Rob, I did what you suggested an it worked fine so
many thanks for that.
I am now going for broke.
I got clever and used the following expression where the dates are
coming from a form but I keep geting syntax error messages.
Can I ask you
Is the where statement correct?
Do I whack the whole string in the parameter declaration box of just
some part of it?
Thanks
Brian
PARAMETERS Between [forms]![Main menu]![txtstartdate] AND
[forms]![main menu]![txtenddate] DateTime;
TRANSFORM Sum([PLINV&EXPPart2].Amt) AS SumOfAmt
SELECT [PLINV&EXPPart2].TransDate
FROM [PLINV&EXPPart2]
WHERE ((([PLINV&EXPPart2].TransDate) Between [Forms]![Main
Menu]![txtstartdate] And [forms]![Main Menu]![txtenddate]))
GROUP BY [PLINV&EXPPart2].TransDate
PIVOT [PLINV&EXPPart2].DebExp;


Rob Parker said:
Hi Brian,

You are inputting the desired parameters, but not doing anything
with them. You need either a WHERE or HAVING clause in your query,
but you don't have one. You need to put the parameters in the
appropriate criteria field(s) of your query.

HTH,

Rob
I am trying to restrict dates [TransDate] in a crosstab query. I
have tried the following expression suggested by a community member
but it ignores the parameters -
PARAMETERS [Start Date:] DateTime, [End Date:] DateTime;
TRANSFORM Sum([PLINV&EXPPart2].Amt) AS SumOfAmt
SELECT [PLINV&EXPPart2].TransDate
FROM [PLINV&EXPPart2]
GROUP BY [PLINV&EXPPart2].TransDate
PIVOT [PLINV&EXPPart2].DebExp;

Can someone please put me right? Assuming I get the query to work I
would prefer to go a step further and possibly get the dates passed
from a form with a calendar control
Click to expand...
Click to expand...
Click to expand...
 
Brilliant!

Rob Parker said:
The PARAMETERS clause of your query should contain the parameter names and
datatypes, as a comma-separated list. In your case, it will be:

PARAMETERS [forms]![Main menu]![txtstartdate] DateTime, [forms]![main
menu]![txtenddate] DateTime;

HTH,

Rob

Thanks for the tip Rob, I did what you suggested an it worked fine so
many thanks for that.
I am now going for broke.
I got clever and used the following expression where the dates are
coming from a form but I keep geting syntax error messages.
Can I ask you
Is the where statement correct?
Do I whack the whole string in the parameter declaration box of just
some part of it?
Thanks
Brian
PARAMETERS Between [forms]![Main menu]![txtstartdate] AND
[forms]![main menu]![txtenddate] DateTime;
TRANSFORM Sum([PLINV&EXPPart2].Amt) AS SumOfAmt
SELECT [PLINV&EXPPart2].TransDate
FROM [PLINV&EXPPart2]
WHERE ((([PLINV&EXPPart2].TransDate) Between [Forms]![Main
Menu]![txtstartdate] And [forms]![Main Menu]![txtenddate]))
GROUP BY [PLINV&EXPPart2].TransDate
PIVOT [PLINV&EXPPart2].DebExp;


Rob Parker said:
Hi Brian,

You are inputting the desired parameters, but not doing anything
with them. You need either a WHERE or HAVING clause in your query,
but you don't have one. You need to put the parameters in the
appropriate criteria field(s) of your query.

HTH,

Rob

Brian wrote:
I am trying to restrict dates [TransDate] in a crosstab query. I
have tried the following expression suggested by a community member
but it ignores the parameters -
PARAMETERS [Start Date:] DateTime, [End Date:] DateTime;
TRANSFORM Sum([PLINV&EXPPart2].Amt) AS SumOfAmt
SELECT [PLINV&EXPPart2].TransDate
FROM [PLINV&EXPPart2]
GROUP BY [PLINV&EXPPart2].TransDate
PIVOT [PLINV&EXPPart2].DebExp;

Can someone please put me right? Assuming I get the query to work I
would prefer to go a step further and possibly get the dates passed
from a form with a calendar control
Click to expand...
Click to expand...
Click to expand...
 
Back
Top