Report Error "Database Engine"

[Bob Vance]

When tring to open my report I am getting this error!

The Microsoft Access Database Engine does not recognize '[2]' as a valid
field name or expression

The Only reference I have to [2] is in my report query below, funny tho it
works in my database but will not work in my friends database and both
databases should be the same except for the data in it

SELECT tblOwnerInfwnerID, [OwnerLastName] & ", " & [OwnerFirstName] AS
OwnerName, nz([3],0) AS tb3Months0, nz([2],0) AS tb2Months0, nz([1],0) AS
tb1Month0, nz([0],0) AS tbCurrent0,
Dues([tb3Months0],[tb2Months0],[tb1Month0],[tbCurrent0],3) AS tb3Months,
Dues([tb3Months0],[tb2Months0],[tb1Month0],[tbCurrent0],2) AS tb2Months,
Dues([tb3Months0],[tb2Months0],[tb1Month0],[tbCurrent0],1) AS tb1Month,
Dues([tb3Months0],[tb2Months0],[tb1Month0],[tbCurrent0],0) AS tbCurrent,
qPayableTotalForPaymentwithTotal.Payable
FROM (tblOwnerInfo INNER JOIN qPayableTotalForPaymentwithTotal ON
tblOwnerInfwnerID = qPayableTotalForPaymentwithTotal.OwnerID) INNER JOIN
qOverDueRep ON qPayableTotalForPaymentwithTotal.OwnerID =
qOverDueRep.OwnerID
ORDER BY [OwnerLastName] & ", " & [OwnerFirstName];

 

[Karl Hoaglund]

Hi Bob. I'd first try to determine whether the problem is happening in
the query or in the report. Does the query run correctly if you open
it on its own? If so, do you see a field [2] in the results?

Karl

 

[Bob Vance]

Thanks Karl, the qry is working in my database but not in the other and
really the only difference is that there is different data in tables, Could
it be something to do with a table!
Regards Bob

 

[Bob Vance]

Thanks Karl, the qry is working in my database but not in the other and
really the only difference is that there is different data in tables,
Could it be something to do with a table!
Regards Bob

Hi Bob. I'd first try to determine whether the problem is happening in
the query or in the report. Does the query run correctly if you open
it on its own? If so, do you see a field [2] in the results?

Karl

It must have something to do with this Crosstab Query as it has the field 2
in it
I am actually getting this error now when I try to open either query " Date
Type mismatch in criteria expression"
Thanks Bob


TRANSFORM Sum(qOwnerPercentAmountInPaymentMethodRep.AmountSummary) AS Dues
SELECT qOwnerPercentAmountInPaymentMethodRep.OwnerID
FROM qOwnerPercentAmountInPaymentMethodRep
GROUP BY qOwnerPercentAmountInPaymentMethodRep.OwnerID
PIVOT
IIf(qOwnerPercentAmountInPaymentMethodRep.AmountSummary<0,3,IIf(MonthsDue([OnDate])>=3,3,IIf(MonthsDue([OnDate])>=2,2,IIf(MonthsDue([OnDate])>=1,1,0))));

 

[Bob Vance]

Thanks Karl, the qry is working in my database but not in the other and
really the only difference is that there is different data in tables,
Could it be something to do with a table!
Regards Bob

Hi Bob. I'd first try to determine whether the problem is happening in
the query or in the report. Does the query run correctly if you open
it on its own? If so, do you see a field [2] in the results?

Karl

It must have something to do with this Crosstab Query as it has the field
2 in it
I am actually getting this error now when I try to open either query "
Date Type mismatch in criteria expression"
Thanks Bob


TRANSFORM Sum(qOwnerPercentAmountInPaymentMethodRep.AmountSummary) AS Dues
SELECT qOwnerPercentAmountInPaymentMethodRep.OwnerID
FROM qOwnerPercentAmountInPaymentMethodRep
GROUP BY qOwnerPercentAmountInPaymentMethodRep.OwnerID
PIVOT
IIf(qOwnerPercentAmountInPaymentMethodRep.AmountSummary<0,3,IIf(MonthsDue([OnDate])>=3,3,IIf(MonthsDue([OnDate])>=2,2,IIf(MonthsDue([OnDate])>=1,1,0))));

Oops Sorry a : "Data Type mismatch in criteria expression"
Regards Bob

 

[John Spencer]

Modify the pivot clause to force the necessary columns to be returned. If you
use the In clause in the PIVOT clause it will force the columns for Dues. It
does have the effect of NOT returning additional columns. In your case, that
should not be an issue for this query.

TRANSFORM Sum(qOwnerPercentAmountInPaymentMethodRep.AmountSummary) AS Dues
SELECT qOwnerPercentAmountInPaymentMethodRep.OwnerID
FROM qOwnerPercentAmountInPaymentMethodRep
GROUP BY qOwnerPercentAmountInPaymentMethodRep.OwnerID
PIVOT IIf(qOwnerPercentAmountInPaymentMethodRep.AmountSummary<0,3,
IIf(MonthsDue([OnDate])>=3,3,IIf(MonthsDue([OnDate])>=2,2,IIf(MonthsDue([OnDate])>=1,1,0))))
In (0,1,2,3);

My preference would be to change the Pivot Clause slightly to get slightly
more intuitive column (field) names.
PIVOT IIf(MonthsDue([OnDate])>=3,"ThreeorMoreMonths",
IIf(MonthsDue([OnDate])>=2,"TwoMonths",
IIf(MonthsDue([OnDate])>=1,"OneMonth","ZeroMonths")))) In
("ThreeorMoreMonths","TwoMonths","OneMonth","ZeroMonths");

Thanks Karl, the qry is working in my database but not in the other and
really the only difference is that there is different data in tables,
Could it be something to do with a table!
Regards Bob

Hi Bob. I'd first try to determine whether the problem is happening in
the query or in the report. Does the query run correctly if you open
it on its own? If so, do you see a field [2] in the results?

Karl

--

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County