Crosstab query won't run date selection criteria

[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

 

[Van T. Dinh]

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.

 

[Julia Boswell]

Thanks, but if you remove the square brackets, Access automatically adds
them in again!

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)

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

 

[John Spencer (MVP)]

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 Boswell]

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

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.

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

 

[Van T. Dinh]

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

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

 

[Julia Boswell]

Excellent, thanks very much, that works!

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)

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

 

[Julia Boswell]

This has now been sorted. All I needed to do was set the column headings in
the crosstab query's properties!

 

[Van T. Dinh]

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)