Error 3265 (Item not found in this collection)

[Tony Girgenti]

When i execute the following select, using Access XP, VBA, DAO, external
tables and try to print the following line using "Debug.Print", I get "Error
3265(Item not found in this collection)" on sa_lin.item_no. I tried doing
"Cusrs!sa_lin.item_no", "Cusrs!sa_lin!item_no", "Cusrs.sa_lin.item_no" and
several other variations. Any help that any body can provide would be
greatly appreciated. Thanks, Tony

CusQry = "SELECT DISTINCT cust.*, sa_lin.item_no " & _
"FROM (cust " & _
"INNER JOIN sa_hdr " & _
"ON cust.nbr = sa_hdr.cust_no) " & _
"INNER JOIN sa_lin " & _
"ON sa_hdr.ticket_no = sa_lin.hdr_ticket_no " & _
"WHERE sa_lin.item_no BETWEEN '" & StartItem & "' AND '" &
EndItem & "' " & _
"AND sa_hdr.post_dat BETWEEN '" & StartDate & "' AND '" &
EndDate & "' " & _
"AND cust.zip_cod BETWEEN '" & StartZipCode & "' AND '" &
EndZipCode & "' " & _
"AND (cust.cat = '" & CustCat & "' OR '" & CustCat & "' = '"
& Hex(0) & "');"

Set Cusrs = dbs.OpenRecordset(CusQry, dbOpenSnapshot)

If (Cusrs.RecordCount = 0) Then
CustQuery = 0
End If

Debug.Print "rec = " & Cusrs!NBR & " Line = " & Cusrs![sa_lin.item_no]

 

[Douglas J. Steele]

Access doesn't let you use table.field notation to distinguish between
fields with the same name. You'll have to use the AS keyword to create an
alias.

CusQry = "SELECT DISTINCT cust.*, sa_lin.item_no AS item_number " & _
"FROM (cust " & _ ...

then

Debug.Print "rec = " & Cusrs!NBR & " Line = " & Cusrs![item_number]

 

[Tony Girgenti]

Hi Doug. Thanks for your help.

I'll try this, but why does it not error out on the "Cusrs!NBR" field ?
Also, "item_no" is not duplicated anywhere in the other tables, so why is it
a problem with duplicates ?

Thanks,
Tony

Access doesn't let you use table.field notation to distinguish between
fields with the same name. You'll have to use the AS keyword to create an
alias.

CusQry = "SELECT DISTINCT cust.*, sa_lin.item_no AS item_number " & _
"FROM (cust " & _ ...

then

Debug.Print "rec = " & Cusrs!NBR & " Line = " & Cusrs![item_number]


--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)

When i execute the following select, using Access XP, VBA, DAO, external
tables and try to print the following line using "Debug.Print", I get "Error
3265(Item not found in this collection)" on sa_lin.item_no. I tried doing
"Cusrs!sa_lin.item_no", "Cusrs!sa_lin!item_no", "Cusrs.sa_lin.item_no" and
several other variations. Any help that any body can provide would be
greatly appreciated. Thanks, Tony

CusQry = "SELECT DISTINCT cust.*, sa_lin.item_no " & _
"FROM (cust " & _
"INNER JOIN sa_hdr " & _
"ON cust.nbr = sa_hdr.cust_no) " & _
"INNER JOIN sa_lin " & _
"ON sa_hdr.ticket_no = sa_lin.hdr_ticket_no " & _
"WHERE sa_lin.item_no BETWEEN '" & StartItem & "' AND '" &
EndItem & "' " & _
"AND sa_hdr.post_dat BETWEEN '" & StartDate & "' AND '" &
EndDate & "' " & _
"AND cust.zip_cod BETWEEN '" & StartZipCode & "' AND '" &
EndZipCode & "' " & _
"AND (cust.cat = '" & CustCat & "' OR '" & CustCat & "'

=

'"
& Hex(0) & "');"

Set Cusrs = dbs.OpenRecordset(CusQry, dbOpenSnapshot)

If (Cusrs.RecordCount = 0) Then
CustQuery = 0
End If

Debug.Print "rec = " & Cusrs!NBR & " Line = " & Cusrs![sa_lin.item_no]

 

[Douglas J. Steele]

If item_no isn't duplicated anywhere, then you don't need to change your
SQL, and instead just change the Debug.Print statement:

Debug.Print "rec = " & Cusrs!NBR & " Line = " & Cusrs![item_no]

The recordset returned by Access only has field names. If a field name is
duplicated, Access will give one of them (usually the second) another name.
If no field names are duplicated, you'll have no problem.

--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)

Hi Doug. Thanks for your help.

I'll try this, but why does it not error out on the "Cusrs!NBR" field ?
Also, "item_no" is not duplicated anywhere in the other tables, so why is it
a problem with duplicates ?

Thanks,
Tony

Access doesn't let you use table.field notation to distinguish between
fields with the same name. You'll have to use the AS keyword to create an
alias.

CusQry = "SELECT DISTINCT cust.*, sa_lin.item_no AS item_number " & _
"FROM (cust " & _ ...

then

Debug.Print "rec = " & Cusrs!NBR & " Line = " & Cusrs![item_number]


--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)

When i execute the following select, using Access XP, VBA, DAO, external
tables and try to print the following line using "Debug.Print", I get "Error
3265(Item not found in this collection)" on sa_lin.item_no. I tried doing
"Cusrs!sa_lin.item_no", "Cusrs!sa_lin!item_no", "Cusrs.sa_lin.item_no" and
several other variations. Any help that any body can provide would be
greatly appreciated. Thanks, Tony

CusQry = "SELECT DISTINCT cust.*, sa_lin.item_no " & _
"FROM (cust " & _
"INNER JOIN sa_hdr " & _
"ON cust.nbr = sa_hdr.cust_no) " & _
"INNER JOIN sa_lin " & _
"ON sa_hdr.ticket_no = sa_lin.hdr_ticket_no " & _
"WHERE sa_lin.item_no BETWEEN '" & StartItem & "' AND

'"

&

'"

&

'"

&

"'

=

'"
& Hex(0) & "');"

Set Cusrs = dbs.OpenRecordset(CusQry, dbOpenSnapshot)

If (Cusrs.RecordCount = 0) Then
CustQuery = 0
End If

Debug.Print "rec = " & Cusrs!NBR & " Line = " & Cusrs![sa_lin.item_no]