Error when opening a my Query

B

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];
 
B

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

Chris O'C via AccessMonster.com said:
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


Bob said:
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];
 
B

Bob Vance

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

Chris O'C via AccessMonster.com said:
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


Bob said:
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];
 
B

Bob Vance

Thanks Chris it is working!, But is it protical to have numbers in fields in
a Crosstab query? Regards Bob
Chris O'C via AccessMonster.com said:
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


Bob said:
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];
 
B

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

Chris O'C via AccessMonster.com said:
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


Bob said:
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];
 

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

Top