Error when opening a my Query

[Bob Vance]

I dont understand what this error means:
The Microsoft access database engine does not recognize '[3]' as a valid
field name or expression

My Query:
SELECT tblOwnerInfo.OwnerID, [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
tblOwnerInfo.OwnerID = qPayableTotalForPaymentwithTotal.OwnerID) INNER JOIN
qOverDueRep ON qPayableTotalForPaymentwithTotal.OwnerID =
qOverDueRep.OwnerID
ORDER BY [OwnerLastName] & ", " & [OwnerFirstName];

 

[Bob Vance]

Thanks Chris, My query is relying on this query, Should I change it?

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))));

Your expressions using nz with digits as field names are invalid:

nz([3],0) AS tb3Months0, nz([2],0) AS tb2Months0, nz([1],0) AS
tb1Month0, nz([0],0) AS tbCurrent0

You can't use digits as field names. Change the [3], [2], etc in these
functions to field names in the data sources in the query's from clause.
Like this:

nz([lastquarter],0) AS tb3Months0, nz([last2months],0) AS tb2Months0, nz(
[lastmonth],0) AS
tb1Month0, nz([thismonth],0) AS tbCurrent0

But use the real field names which already exist in your tables or
queries.
I made these up for this example.

Chris

I dont understand what this error means:
The Microsoft access database engine does not recognize '[3]' as a valid
field name or expression

My Query:
SELECT tblOwnerInfo.OwnerID, [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
tblOwnerInfo.OwnerID = qPayableTotalForPaymentwithTotal.OwnerID) INNER
JOIN
qOverDueRep ON qPayableTotalForPaymentwithTotal.OwnerID =
qOverDueRep.OwnerID
ORDER BY [OwnerLastName] & ", " & [OwnerFirstName];

 

[Bob Vance]

Oops Sorry Chris, That query I just posted is a Crosstab Query..Regards Bob

Your expressions using nz with digits as field names are invalid:

nz([3],0) AS tb3Months0, nz([2],0) AS tb2Months0, nz([1],0) AS
tb1Month0, nz([0],0) AS tbCurrent0

You can't use digits as field names. Change the [3], [2], etc in these
functions to field names in the data sources in the query's from clause.
Like this:

nz([lastquarter],0) AS tb3Months0, nz([last2months],0) AS tb2Months0, nz(
[lastmonth],0) AS
tb1Month0, nz([thismonth],0) AS tbCurrent0

But use the real field names which already exist in your tables or
queries.
I made these up for this example.

Chris

I dont understand what this error means:
The Microsoft access database engine does not recognize '[3]' as a valid
field name or expression

My Query:
SELECT tblOwnerInfo.OwnerID, [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
tblOwnerInfo.OwnerID = qPayableTotalForPaymentwithTotal.OwnerID) INNER
JOIN
qOverDueRep ON qPayableTotalForPaymentwithTotal.OwnerID =
qOverDueRep.OwnerID
ORDER BY [OwnerLastName] & ", " & [OwnerFirstName];

 

[Tamijahunter]

ok

 

[Bob Vance]

Thanks Chris it is working!, But is it protical to have numbers in fields in
a Crosstab query? Regards Bob

When I first read this post I figured you were ok and didn't need further
help. But I think I should ask anyway. Is your query now working?
Please
post back if it isn't.

Chris

Oops Sorry Chris, That query I just posted is a Crosstab Query..Regards
Bob

Your expressions using nz with digits as field names are invalid:

[quoted text clipped - 34 lines]

qOverDueRep.OwnerID
ORDER BY [OwnerLastName] & ", " & [OwnerFirstName];

 

[Bob Vance]

Thanks Chris, My query is working, even when I added the new field
"AnyOverdue" but when I add the criteria Like "-1" I am getting this error "
Does not recognize tbCurrent as a field!

SELECT tblOwnerInfo.OwnerID, [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, [Payable]>[tbCurrent0] AS
AnyOverdue
FROM (tblOwnerInfo INNER JOIN qPayableTotalForPaymentwithTotal ON
tblOwnerInfo.OwnerID = qPayableTotalForPaymentwithTotal.OwnerID) INNER JOIN
qOverDueRep ON qPayableTotalForPaymentwithTotal.OwnerID =
qOverDueRep.OwnerID
WHERE ((([Payable]>[tbCurrent]) Like "-1"))
ORDER BY [OwnerLastName] & ", " & [OwnerFirstName];

When I first read this post I figured you were ok and didn't need further
help. But I think I should ask anyway. Is your query now working?
Please
post back if it isn't.

Chris

Oops Sorry Chris, That query I just posted is a Crosstab Query..Regards
Bob

Your expressions using nz with digits as field names are invalid:

[quoted text clipped - 34 lines]

qOverDueRep.OwnerID
ORDER BY [OwnerLastName] & ", " & [OwnerFirstName];