Parameters Expression In A CrossTab Query

[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

 

[Rob Parker]

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]

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;

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

 

[Rob Parker]

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;

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

 

[Brian]

Brilliant!

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;

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