Access Report

  • Thread starter Thread starter SueOT
  • Start date Start date
S

SueOT

I have created an access report based on a crosstab query.

The crosstab query has a parameter in it to select data
from a specific week no. I spcified the parameter in the
Query - Parameters dialog box. Ther query runs and asks
for the parameter correctly.

The report however asks twice for the parameter and then
gives me the mess "The Microsoft Jet database engine does
not recognise the [Week No] as a valid field name or
expression (error3070).

Can anyone offer any suggestions?
 
PARAMETERS [Week No] Long;
TRANSFORM Sum([Daily Sales Query].[Sales Quantity]) AS
[SumOfSales Quantity]
SELECT [Daily Sales Query].Date, [Daily Sales Query].
[Week No], [Daily Sales Query].Site, Sum([Daily Sales
Query].[Sales Quantity]) AS [Total Of Sales Quantity]
FROM [Daily Sales Query]
WHERE ((([Daily Sales Query].[Week No])=[week No]))
GROUP BY [Daily Sales Query].Date, [Daily Sales Query].
[Week No], [Daily Sales Query].Site
PIVOT [Daily Sales Query].[Sales Type];
-----Original Message-----
Post your query's SQL view.

--
Duane Hookom
MS Access MVP


I have created an access report based on a crosstab query.

The crosstab query has a parameter in it to select data
from a specific week no. I spcified the parameter in the
Query - Parameters dialog box. Ther query runs and asks
for the parameter correctly.

The report however asks twice for the parameter and then
gives me the mess "The Microsoft Jet database engine does
not recognise the [Week No] as a valid field name or
expression (error3070).

Can anyone offer any suggestions?
Click to expand...


.
Click to expand...
 
The first issue I notice is that your parameter is the same as a field name:
(([Daily Sales Query].[Week No])=[week No])
I'm surprised you get any prompt at all from this query. Do you get the
prompt when running the query on it's own or just when run in the report? Do
you truly have a field in the [Daily Sales Query] named [Week No]?

BTW: I always like to recommend not placing spaces in object names. IE
qselDailySales, SalesQuantity, WeekNo, SalesType,...
Also, Date is the name of a function so it would be better to name your
field SaleDate.

--
Duane Hookom
Microsoft Access MVP


PARAMETERS [Week No] Long;
TRANSFORM Sum([Daily Sales Query].[Sales Quantity]) AS
[SumOfSales Quantity]
SELECT [Daily Sales Query].Date, [Daily Sales Query].
[Week No], [Daily Sales Query].Site, Sum([Daily Sales
Query].[Sales Quantity]) AS [Total Of Sales Quantity]
FROM [Daily Sales Query]
WHERE ((([Daily Sales Query].[Week No])=[week No]))
GROUP BY [Daily Sales Query].Date, [Daily Sales Query].
[Week No], [Daily Sales Query].Site
PIVOT [Daily Sales Query].[Sales Type];
-----Original Message-----
Post your query's SQL view.

--
Duane Hookom
MS Access MVP


I have created an access report based on a crosstab query.

The crosstab query has a parameter in it to select data
from a specific week no. I spcified the parameter in the
Query - Parameters dialog box. Ther query runs and asks
for the parameter correctly.

The report however asks twice for the parameter and then
gives me the mess "The Microsoft Jet database engine does
not recognise the [Week No] as a valid field name or
expression (error3070).

Can anyone offer any suggestions?
Click to expand...


.
Click to expand...
Click to expand...
 
Back
Top