Crosstab query won't run date selection criteria

  • Thread starter Thread starter Julia Boswell
  • Start date Start date
J

Julia Boswell

Hi all,

I've got a cross tab query that runs fine until I enter the date parameter
of:

Between [Forms]![frmSelectAnt]![txtDateFrom] And
[Forms]![frmSelectAnt]![txtDateTo]

Originally if this was just in the criteria I got a Jet Engine error. After
reading through the postings on the group I took some advice and edited the
query's parameters to the same as the text in the field criteria and
selected date/time as the type.

The error message has now changed to read "Invalid bracketing of name
Between [Forms] etc"

Both txtDateFrom and txtDateTo are medium date format text fields.

Any ideas? Thanks in advance

Julia
 
Since you are not using space or special symbols in the names of the Forms
or Control, try without the square brackets.

Make sure you change the Param decs as well.
 
Thanks, but if you remove the square brackets, Access automatically adds
them in again!
Van T. Dinh said:
Since you are not using space or special symbols in the names of the Forms
or Control, try without the square brackets.

Make sure you change the Param decs as well.

--
HTH
Van T. Dinh
MVP (Access)



Julia Boswell said:
Hi all,

I've got a cross tab query that runs fine until I enter the date parameter
of:

Between [Forms]![frmSelectAnt]![txtDateFrom] And
[Forms]![frmSelectAnt]![txtDateTo]

Originally if this was just in the criteria I got a Jet Engine error. After
reading through the postings on the group I took some advice and edited the
query's parameters to the same as the text in the field criteria and
selected date/time as the type.

The error message has now changed to read "Invalid bracketing of name
Between [Forms] etc"

Both txtDateFrom and txtDateTo are medium date format text fields.

Any ideas? Thanks in advance

Julia
Click to expand...
Click to expand...
 
OK, can you copy and post the SQL of your query?

(Possibly unneeded instructions follow)
Open the query
Select View:Sql from the Menu
Select all the text
Copy it
Paste it into the message

As a guess, your parameter has an extra bracket in it when you are defining it.
It probably looks something like
[[Forms]![frmSelectAnt]![txtDateFrom]
Note the extra bracket at the start. I find this happens sometimes when I try
doing a cut and paste from the criteria cell into the cell in the parameter
definition dialog.
 
Here it is, and you're right there are a couple of extra brackets added -
before the Between and after the first Forms. I've tried removing them both
and I still get the same error message.

PARAMETERS [Between [Forms]]![frmSelectAnt]![txtDateFrom] And
[Forms]![frmSelectAnt]![txtDateTo] DateTime;
TRANSFORM Sum(qryDataLinksAntBase.fldAntValue) AS SumOffldAntValue
SELECT qryDataLinksAntBase.fldBU AS BU, qryDataLinksAntBase.fldProjectName
AS Project
FROM qryDataLinksAntBase
WHERE (((qryDataLinksAntBase.fldMonthDue) Between
[Forms]![frmSelectAnt]![txtDateFrom] And
[Forms]![frmSelectAnt]![txtDateTo]))
GROUP BY qryDataLinksAntBase.fldBU, qryDataLinksAntBase.fldProjectName,
qryDataLinksAntBase.fldMonthDue
ORDER BY Format([fldMonthDue],"yy:mm")
PIVOT Format([fldMonthDue],"yy:mm");

Julia
John Spencer (MVP) said:
OK, can you copy and post the SQL of your query?

(Possibly unneeded instructions follow)
Open the query
Select View:Sql from the Menu
Select all the text
Copy it
Paste it into the message

As a guess, your parameter has an extra bracket in it when you are defining it.
It probably looks something like
[[Forms]![frmSelectAnt]![txtDateFrom]
Note the extra bracket at the start. I find this happens sometimes when I try
doing a cut and paste from the criteria cell into the cell in the parameter
definition dialog.


Julia said:
Hi all,

I've got a cross tab query that runs fine until I enter the date parameter
of:

Between [Forms]![frmSelectAnt]![txtDateFrom] And
[Forms]![frmSelectAnt]![txtDateTo]

Originally if this was just in the criteria I got a Jet Engine error. After
reading through the postings on the group I took some advice and edited the
query's parameters to the same as the text in the field criteria and
selected date/time as the type.

The error message has now changed to read "Invalid bracketing of name
Between [Forms] etc"

Both txtDateFrom and txtDateTo are medium date format text fields.

Any ideas? Thanks in advance

Julia
Click to expand...
Click to expand...
 
Aha, they are 2 separate Parameters, not one as per your SQL String. Try:

PARAMETERS
[Forms]]![frmSelectAnt]![txtDateFrom] DateTime,
[Forms]![frmSelectAnt]![txtDateTo] DateTime;
.....
 
Excellent, thanks very much, that works!
Van T. Dinh said:
Aha, they are 2 separate Parameters, not one as per your SQL String. Try:

PARAMETERS
[Forms]]![frmSelectAnt]![txtDateFrom] DateTime,
[Forms]![frmSelectAnt]![txtDateTo] DateTime;
....

--
HTH
Van T. Dinh
MVP (Access)



Julia Boswell said:
Here it is, and you're right there are a couple of extra brackets added -
before the Between and after the first Forms. I've tried removing them both
and I still get the same error message.

PARAMETERS [Between [Forms]]![frmSelectAnt]![txtDateFrom] And
[Forms]![frmSelectAnt]![txtDateTo] DateTime;
TRANSFORM Sum(qryDataLinksAntBase.fldAntValue) AS SumOffldAntValue
SELECT qryDataLinksAntBase.fldBU AS BU, qryDataLinksAntBase.fldProjectName
AS Project
FROM qryDataLinksAntBase
WHERE (((qryDataLinksAntBase.fldMonthDue) Between
[Forms]![frmSelectAnt]![txtDateFrom] And
[Forms]![frmSelectAnt]![txtDateTo]))
GROUP BY qryDataLinksAntBase.fldBU, qryDataLinksAntBase.fldProjectName,
qryDataLinksAntBase.fldMonthDue
ORDER BY Format([fldMonthDue],"yy:mm")
PIVOT Format([fldMonthDue],"yy:mm");

Julia
Click to expand...
Click to expand...
 
This has now been sorted. All I needed to do was set the column headings in
the crosstab query's properties!
 
It looks like this post should be aattached to your previous thread in this
new group rather than this thread.

Van T. Dinh
MVP (Access)
 
Back
Top