The Microsoft Jet database engine does not recognize <name> as a

G

Guest

I have been able to get cross tab query working with criteria options. I can
use ENTER to return a specific week on my cross tab query and it works fine!
When I try to use Between to get a start date and an end date my cross tab
falls over!!

Any ideas?
Thanks



PARAMETERS [Enter Site] Text ( 255 ), [Enter Start Date] Text ( 255 );
SELECT [Forecast Data].Site, [Forecast Data].[Year_Week No], [Forecast
Data].Product, [Forecast Data].Kilos, [Grampian Sites].[Site Code]
FROM [Grampian Sites] INNER JOIN [Forecast Data] ON [Grampian Sites].Site =
[Forecast Data].Site
WHERE ((([Forecast Data].[Year_Week No])=[Enter Start Date]) AND (([Grampian
Sites].[Site Code])=[Enter Site]));
 
J

John Spencer

"Falls over" is not very descriptive of whatever problem you are having.
That could mean
-- The query doesn't run and you get an error message
-- The query returns bad results (too many, too few, wrong ones, no records)
-- The query runs forever/locks up your computer

You have typed [Enter Start Date] as Text instead of as DateTime. Is that
correct?
That implies that Year_Week No is a text field. Is that the case?
 
G

Guest

The query doesn't run and I get the following error message - The Microsoft
Jet database engine does not recognize '[Start Date:2005_01]' as a valid
field name or expression.

2005_01 is a customised date field e.g. Week 01 of 2005!! which would make
it a text field. In the original query (which the cross tab runs over) when I
have Between [Start Date] And [End Date] in the criteria, the records return
as I would expect.

When I try and run the cross tab I get the error message.



John Spencer said:
"Falls over" is not very descriptive of whatever problem you are having.
That could mean
-- The query doesn't run and you get an error message
-- The query returns bad results (too many, too few, wrong ones, no records)
-- The query runs forever/locks up your computer

You have typed [Enter Start Date] as Text instead of as DateTime. Is that
correct?
That implies that Year_Week No is a text field. Is that the case?


Gary B said:
I have been able to get cross tab query working with criteria options. I
can
use ENTER to return a specific week on my cross tab query and it works
fine!
When I try to use Between to get a start date and an end date my cross tab
falls over!!

Any ideas?
Thanks



PARAMETERS [Enter Site] Text ( 255 ), [Enter Start Date] Text ( 255 );
SELECT [Forecast Data].Site, [Forecast Data].[Year_Week No], [Forecast
Data].Product, [Forecast Data].Kilos, [Grampian Sites].[Site Code]
FROM [Grampian Sites] INNER JOIN [Forecast Data] ON [Grampian Sites].Site
=
[Forecast Data].Site
WHERE ((([Forecast Data].[Year_Week No])=[Enter Start Date]) AND
(([Grampian
Sites].[Site Code])=[Enter Site]));
Click to expand...
Click to expand...
 
J

John Spencer

Well, without seeing the SQL for any of the queries, all I can propose is
that JET thinks you have [Start Date:2005-01] in the SQL. It can't find a
field or defined parameter with that name. That is why the error message.

Please copy and post the SQL of your query (queries).

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


Gary B said:
The query doesn't run and I get the following error message - The
Microsoft
Jet database engine does not recognize '[Start Date:2005_01]' as a valid
field name or expression.

2005_01 is a customised date field e.g. Week 01 of 2005!! which would make
it a text field. In the original query (which the cross tab runs over)
when I
have Between [Start Date] And [End Date] in the criteria, the records
return
as I would expect.

When I try and run the cross tab I get the error message.



John Spencer said:
"Falls over" is not very descriptive of whatever problem you are having.
That could mean
-- The query doesn't run and you get an error message
-- The query returns bad results (too many, too few, wrong ones, no
records)
-- The query runs forever/locks up your computer

You have typed [Enter Start Date] as Text instead of as DateTime. Is that
correct?
That implies that Year_Week No is a text field. Is that the case?


Gary B said:
I have been able to get cross tab query working with criteria options. I
can
use ENTER to return a specific week on my cross tab query and it works
fine!
When I try to use Between to get a start date and an end date my cross
tab
falls over!!

Any ideas?
Thanks



PARAMETERS [Enter Site] Text ( 255 ), [Enter Start Date] Text ( 255 );
SELECT [Forecast Data].Site, [Forecast Data].[Year_Week No], [Forecast
Data].Product, [Forecast Data].Kilos, [Grampian Sites].[Site Code]
FROM [Grampian Sites] INNER JOIN [Forecast Data] ON [Grampian
Sites].Site
=
[Forecast Data].Site
WHERE ((([Forecast Data].[Year_Week No])=[Enter Start Date]) AND
(([Grampian
Sites].[Site Code])=[Enter Site]));
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

SQL from first Query. This runs ok.

SELECT [Forecast Data].Site, [Forecast Data].[Year_Week No], [Forecast
Data].Product, [Forecast Data].Kilos, [Grampian Sites].[Site Code]
FROM [Grampian Sites] INNER JOIN [Forecast Data] ON [Grampian Sites].Site =
[Forecast Data].Site
WHERE ((([Forecast Data].[Year_Week No]) Between [Start Date:2005_01] And
[End Date:2005_52]) AND (([Grampian Sites].[Site Code])=[Enter Site]));

When I put Between [Start Date:2005_01] And [End Date:2005_52]) into Query
Parameters I get asked 3 times for the parameters.

Cross tab query SQL

TRANSFORM Sum([Coupar Arisings].Kilos) AS SumOfKilos
SELECT [Coupar Arisings].Product, Sum([Coupar Arisings].Kilos) AS [Total Of
Kilos]
FROM [Coupar Arisings]
GROUP BY [Coupar Arisings].Product
PIVOT [Coupar Arisings].[Year_Week No];



John Spencer said:
Well, without seeing the SQL for any of the queries, all I can propose is
that JET thinks you have [Start Date:2005-01] in the SQL. It can't find a
field or defined parameter with that name. That is why the error message.

Please copy and post the SQL of your query (queries).

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


Gary B said:
The query doesn't run and I get the following error message - The
Microsoft
Jet database engine does not recognize '[Start Date:2005_01]' as a valid
field name or expression.

2005_01 is a customised date field e.g. Week 01 of 2005!! which would make
it a text field. In the original query (which the cross tab runs over)
when I
have Between [Start Date] And [End Date] in the criteria, the records
return
as I would expect.

When I try and run the cross tab I get the error message.



John Spencer said:
"Falls over" is not very descriptive of whatever problem you are having.
That could mean
-- The query doesn't run and you get an error message
-- The query returns bad results (too many, too few, wrong ones, no
records)
-- The query runs forever/locks up your computer

You have typed [Enter Start Date] as Text instead of as DateTime. Is that
correct?
That implies that Year_Week No is a text field. Is that the case?


I have been able to get cross tab query working with criteria options. I
can
use ENTER to return a specific week on my cross tab query and it works
fine!
When I try to use Between to get a start date and an end date my cross
tab
falls over!!

Any ideas?
Thanks



PARAMETERS [Enter Site] Text ( 255 ), [Enter Start Date] Text ( 255 );
SELECT [Forecast Data].Site, [Forecast Data].[Year_Week No], [Forecast
Data].Product, [Forecast Data].Kilos, [Grampian Sites].[Site Code]
FROM [Grampian Sites] INNER JOIN [Forecast Data] ON [Grampian
Sites].Site
=
[Forecast Data].Site
WHERE ((([Forecast Data].[Year_Week No])=[Enter Start Date]) AND
(([Grampian
Sites].[Site Code])=[Enter Site]));
Click to expand...
Click to expand...
Click to expand...
 
J

John Spencer

Assumption:
Your parameters are all text
They are spelled as posted in the query

Based on that, does the following query work as expected?

Parameters [Start Date:2005_01] Text (255),
[End Date:2005_52] Text (255) ,
[Enter Site] Text (255);
SELECT [Forecast Data].Site, [Forecast Data].[Year_Week No],
[Forecast Data].Product, [Forecast Data].Kilos, [Grampian Sites].[Site Code]
FROM [Grampian Sites] INNER JOIN [Forecast Data]
ON [Grampian Sites].Site = [Forecast Data].Site
WHERE ((([Forecast Data].[Year_Week No]) Between [Start Date:2005_01] And
[End Date:2005_52]) AND (([Grampian Sites].[Site Code])=[Enter Site]));


Gary B said:
SQL from first Query. This runs ok.

SELECT [Forecast Data].Site, [Forecast Data].[Year_Week No], [Forecast
Data].Product, [Forecast Data].Kilos, [Grampian Sites].[Site Code]
FROM [Grampian Sites] INNER JOIN [Forecast Data] ON [Grampian Sites].Site
=
[Forecast Data].Site
WHERE ((([Forecast Data].[Year_Week No]) Between [Start Date:2005_01] And
[End Date:2005_52]) AND (([Grampian Sites].[Site Code])=[Enter Site]));

When I put Between [Start Date:2005_01] And [End Date:2005_52]) into Query
Parameters I get asked 3 times for the parameters.

Cross tab query SQL

TRANSFORM Sum([Coupar Arisings].Kilos) AS SumOfKilos
SELECT [Coupar Arisings].Product, Sum([Coupar Arisings].Kilos) AS [Total
Of
Kilos]
FROM [Coupar Arisings]
GROUP BY [Coupar Arisings].Product
PIVOT [Coupar Arisings].[Year_Week No];



John Spencer said:
Well, without seeing the SQL for any of the queries, all I can propose is
that JET thinks you have [Start Date:2005-01] in the SQL. It can't find
a
field or defined parameter with that name. That is why the error
message.

Please copy and post the SQL of your query (queries).

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


Gary B said:
The query doesn't run and I get the following error message - The
Microsoft
Jet database engine does not recognize '[Start Date:2005_01]' as a
valid
field name or expression.

2005_01 is a customised date field e.g. Week 01 of 2005!! which would
make
it a text field. In the original query (which the cross tab runs over)
when I
have Between [Start Date] And [End Date] in the criteria, the records
return
as I would expect.

When I try and run the cross tab I get the error message.



:

"Falls over" is not very descriptive of whatever problem you are
having.
That could mean
-- The query doesn't run and you get an error message
-- The query returns bad results (too many, too few, wrong ones, no
records)
-- The query runs forever/locks up your computer

You have typed [Enter Start Date] as Text instead of as DateTime. Is
that
correct?
That implies that Year_Week No is a text field. Is that the case?


I have been able to get cross tab query working with criteria
options. I
can
use ENTER to return a specific week on my cross tab query and it
works
fine!
When I try to use Between to get a start date and an end date my
cross
tab
falls over!!

Any ideas?
Thanks



PARAMETERS [Enter Site] Text ( 255 ), [Enter Start Date] Text (
255 );
SELECT [Forecast Data].Site, [Forecast Data].[Year_Week No],
[Forecast
Data].Product, [Forecast Data].Kilos, [Grampian Sites].[Site Code]
FROM [Grampian Sites] INNER JOIN [Forecast Data] ON [Grampian
Sites].Site
=
[Forecast Data].Site
WHERE ((([Forecast Data].[Year_Week No])=[Enter Start Date]) AND
(([Grampian
Sites].[Site Code])=[Enter Site]));
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

John

Exactly what I was looking for. Thanks

What was I doing wrong (or just didn't understand)

Thanks again

John Spencer said:
Assumption:
Your parameters are all text
They are spelled as posted in the query

Based on that, does the following query work as expected?

Parameters [Start Date:2005_01] Text (255),
[End Date:2005_52] Text (255) ,
[Enter Site] Text (255);
SELECT [Forecast Data].Site, [Forecast Data].[Year_Week No],
[Forecast Data].Product, [Forecast Data].Kilos, [Grampian Sites].[Site Code]
FROM [Grampian Sites] INNER JOIN [Forecast Data]
ON [Grampian Sites].Site = [Forecast Data].Site
WHERE ((([Forecast Data].[Year_Week No]) Between [Start Date:2005_01] And
[End Date:2005_52]) AND (([Grampian Sites].[Site Code])=[Enter Site]));


Gary B said:
SQL from first Query. This runs ok.

SELECT [Forecast Data].Site, [Forecast Data].[Year_Week No], [Forecast
Data].Product, [Forecast Data].Kilos, [Grampian Sites].[Site Code]
FROM [Grampian Sites] INNER JOIN [Forecast Data] ON [Grampian Sites].Site
=
[Forecast Data].Site
WHERE ((([Forecast Data].[Year_Week No]) Between [Start Date:2005_01] And
[End Date:2005_52]) AND (([Grampian Sites].[Site Code])=[Enter Site]));

When I put Between [Start Date:2005_01] And [End Date:2005_52]) into Query
Parameters I get asked 3 times for the parameters.

Cross tab query SQL

TRANSFORM Sum([Coupar Arisings].Kilos) AS SumOfKilos
SELECT [Coupar Arisings].Product, Sum([Coupar Arisings].Kilos) AS [Total
Of
Kilos]
FROM [Coupar Arisings]
GROUP BY [Coupar Arisings].Product
PIVOT [Coupar Arisings].[Year_Week No];



John Spencer said:
Well, without seeing the SQL for any of the queries, all I can propose is
that JET thinks you have [Start Date:2005-01] in the SQL. It can't find
a
field or defined parameter with that name. That is why the error
message.

Please copy and post the SQL of your query (queries).

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


The query doesn't run and I get the following error message - The
Microsoft
Jet database engine does not recognize '[Start Date:2005_01]' as a
valid
field name or expression.

2005_01 is a customised date field e.g. Week 01 of 2005!! which would
make
it a text field. In the original query (which the cross tab runs over)
when I
have Between [Start Date] And [End Date] in the criteria, the records
return
as I would expect.

When I try and run the cross tab I get the error message.



:

"Falls over" is not very descriptive of whatever problem you are
having.
That could mean
-- The query doesn't run and you get an error message
-- The query returns bad results (too many, too few, wrong ones, no
records)
-- The query runs forever/locks up your computer

You have typed [Enter Start Date] as Text instead of as DateTime. Is
that
correct?
That implies that Year_Week No is a text field. Is that the case?


I have been able to get cross tab query working with criteria
options. I
can
use ENTER to return a specific week on my cross tab query and it
works
fine!
When I try to use Between to get a start date and an end date my
cross
tab
falls over!!

Any ideas?
Thanks



PARAMETERS [Enter Site] Text ( 255 ), [Enter Start Date] Text (
255 );
SELECT [Forecast Data].Site, [Forecast Data].[Year_Week No],
[Forecast
Data].Product, [Forecast Data].Kilos, [Grampian Sites].[Site Code]
FROM [Grampian Sites] INNER JOIN [Forecast Data] ON [Grampian
Sites].Site
=
[Forecast Data].Site
WHERE ((([Forecast Data].[Year_Week No])=[Enter Start Date]) AND
(([Grampian
Sites].[Site Code])=[Enter Site]));
Click to expand...
Click to expand...
Click to expand...
 
J

John Spencer

Glad it is working.

As for what you were doing wrong - I really don't know. You might be able to
track it down if you had your old queries and wanted to compare what you
attempted to the last posting.

Gary said:
John

Exactly what I was looking for. Thanks

What was I doing wrong (or just didn't understand)

Thanks again

John Spencer said:
Assumption:
Your parameters are all text
They are spelled as posted in the query

Based on that, does the following query work as expected?

Parameters [Start Date:2005_01] Text (255),
[End Date:2005_52] Text (255) ,
[Enter Site] Text (255);
SELECT [Forecast Data].Site, [Forecast Data].[Year_Week No],
[Forecast Data].Product, [Forecast Data].Kilos, [Grampian Sites].[Site Code]
FROM [Grampian Sites] INNER JOIN [Forecast Data]
ON [Grampian Sites].Site = [Forecast Data].Site
WHERE ((([Forecast Data].[Year_Week No]) Between [Start Date:2005_01] And
[End Date:2005_52]) AND (([Grampian Sites].[Site Code])=[Enter Site]));


Gary B said:
SQL from first Query. This runs ok.

SELECT [Forecast Data].Site, [Forecast Data].[Year_Week No], [Forecast
Data].Product, [Forecast Data].Kilos, [Grampian Sites].[Site Code]
FROM [Grampian Sites] INNER JOIN [Forecast Data] ON [Grampian Sites].Site
=
[Forecast Data].Site
WHERE ((([Forecast Data].[Year_Week No]) Between [Start Date:2005_01] And
[End Date:2005_52]) AND (([Grampian Sites].[Site Code])=[Enter Site]));

When I put Between [Start Date:2005_01] And [End Date:2005_52]) into Query
Parameters I get asked 3 times for the parameters.

Cross tab query SQL

TRANSFORM Sum([Coupar Arisings].Kilos) AS SumOfKilos
SELECT [Coupar Arisings].Product, Sum([Coupar Arisings].Kilos) AS [Total
Of
Kilos]
FROM [Coupar Arisings]
GROUP BY [Coupar Arisings].Product
PIVOT [Coupar Arisings].[Year_Week No];



:

Well, without seeing the SQL for any of the queries, all I can propose is
that JET thinks you have [Start Date:2005-01] in the SQL. It can't find
a
field or defined parameter with that name. That is why the error
message.

Please copy and post the SQL of your query (queries).

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


The query doesn't run and I get the following error message - The
Microsoft
Jet database engine does not recognize '[Start Date:2005_01]' as a
valid
field name or expression.

2005_01 is a customised date field e.g. Week 01 of 2005!! which would
make
it a text field. In the original query (which the cross tab runs over)
when I
have Between [Start Date] And [End Date] in the criteria, the records
return
as I would expect.

When I try and run the cross tab I get the error message.



:

"Falls over" is not very descriptive of whatever problem you are
having.
That could mean
-- The query doesn't run and you get an error message
-- The query returns bad results (too many, too few, wrong ones, no
records)
-- The query runs forever/locks up your computer

You have typed [Enter Start Date] as Text instead of as DateTime. Is
that
correct?
That implies that Year_Week No is a text field. Is that the case?


I have been able to get cross tab query working with criteria
options. I
can
use ENTER to return a specific week on my cross tab query and it
works
fine!
When I try to use Between to get a start date and an end date my
cross
tab
falls over!!

Any ideas?
Thanks



PARAMETERS [Enter Site] Text ( 255 ), [Enter Start Date] Text (
255 );
SELECT [Forecast Data].Site, [Forecast Data].[Year_Week No],
[Forecast
Data].Product, [Forecast Data].Kilos, [Grampian Sites].[Site Code]
FROM [Grampian Sites] INNER JOIN [Forecast Data] ON [Grampian
Sites].Site
=
[Forecast Data].Site
WHERE ((([Forecast Data].[Year_Week No])=[Enter Start Date]) AND
(([Grampian
Sites].[Site Code])=[Enter Site]));
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

Microsoft Jet engine does not recognize " 3
The Microsoft Jet database engine does not recognize... 5
Microsoft Jet database engine does not recognize... 2
Using automation to load a cross tab query to Excel 11
Expression Error on Parameter Query with link to Cross Tab Query 8
The Microsoft Jet database engine does not recognize 0
Grouping and Aggregating Data in Jet SQL 1
Query_Paramater Question 2

Top