I'm in the process of trying to migrate a Database from Access 2003 to SQL Server, and I'm having a rather odd error that doesn't make any sense to me. It shows up when I'm looking at data stored on SQL Server through an Access frontend. The error goes away when I relink everything back to the Access database. In the end it looks like a query issue, so please bear with me....
When I try to run one of my reports, I get a "Runtime Error 3219: Invalid Operation" popup. The debugger brings up a line that says:
DoCmd.OpenReport "rpt_Promo_Oldest100Addresses", acViewPreview
So I go and check out the report in question, and I get the same error, only now it's coming straight from Access, rather than VB, so it only says "Invalid Operation." Looking at the report, I see that it's pulling data straight from one query, so I go check that out. Same error. That query pulls its data from three tables and a query. The tables are all fine, so I check out the query. Same error. That query pulls its data from two tables and three queries, but all those queries check out just fine. So first thing, I copy the SQL text of the query, and paste it into a new query, and the error persists (not surprisingly, but it's always worth trying!) The text of the query is:
SELECT Temp_NameInfo.Code_i32, tbl_Customers_PromotionalGift.Ship_dtm
FROM
(
(
(
Temp_NameInfo LEFT JOIN tbl_Customers_PromotionalGift
ON Temp_NameInfo.Code_i32 = tbl_Customers_PromotionalGift.Parent_i32
)
LEFT JOIN qry_Test_AttendedRetreatOrConference
ON Temp_NameInfo.Code_i32 = qry_Test_AttendedRetreatOrConference.Code_ctr
)
LEFT JOIN [qry_Test_Has MadeDonation]
ON Temp_NameInfo.Code_i32 = [qry_Test_Has MadeDonation].Code_ctr
)
LEFT JOIN qry_Test_HasMadePurchase
ON Temp_NameInfo.Code_i32 = qry_Test_HasMadePurchase.Code_ctr
WHERE (((qry_Test_AttendedRetreatOrConference.CountOfParticipantID_i32)>0))
OR ((([qry_Test_Has MadeDonation].CountOfID_i32)>0))
OR (((qry_Test_HasMadePurchase.CountOfID_i32)>0))
ORDER BY tbl_Customers_PromotionalGift.Ship_dtm, Temp_NameInfo.Code_i32;
If I change the "OR"s in the WHERE clause to "AND"s, the problem goes away (but the query pulls the wrong data). If I restrict the WHERE clause to one of the three comparisons, it works fine (regardless of which one. However, if I change the WHERE clause to add the three columns, and compare that number to 0, it gives the "Invalid Operation" error again. If I put any of the three columns in the SELECT clause, it gives the "Invalid Operation" error.
I don't get this. How can "x AND y AND z" be a valid operation, but "x OR y OR z" is not a valid operation?
Any thoughts?
Thanks,
Cliff
When I try to run one of my reports, I get a "Runtime Error 3219: Invalid Operation" popup. The debugger brings up a line that says:
DoCmd.OpenReport "rpt_Promo_Oldest100Addresses", acViewPreview
So I go and check out the report in question, and I get the same error, only now it's coming straight from Access, rather than VB, so it only says "Invalid Operation." Looking at the report, I see that it's pulling data straight from one query, so I go check that out. Same error. That query pulls its data from three tables and a query. The tables are all fine, so I check out the query. Same error. That query pulls its data from two tables and three queries, but all those queries check out just fine. So first thing, I copy the SQL text of the query, and paste it into a new query, and the error persists (not surprisingly, but it's always worth trying!) The text of the query is:
SELECT Temp_NameInfo.Code_i32, tbl_Customers_PromotionalGift.Ship_dtm
FROM
(
(
(
Temp_NameInfo LEFT JOIN tbl_Customers_PromotionalGift
ON Temp_NameInfo.Code_i32 = tbl_Customers_PromotionalGift.Parent_i32
)
LEFT JOIN qry_Test_AttendedRetreatOrConference
ON Temp_NameInfo.Code_i32 = qry_Test_AttendedRetreatOrConference.Code_ctr
)
LEFT JOIN [qry_Test_Has MadeDonation]
ON Temp_NameInfo.Code_i32 = [qry_Test_Has MadeDonation].Code_ctr
)
LEFT JOIN qry_Test_HasMadePurchase
ON Temp_NameInfo.Code_i32 = qry_Test_HasMadePurchase.Code_ctr
WHERE (((qry_Test_AttendedRetreatOrConference.CountOfParticipantID_i32)>0))
OR ((([qry_Test_Has MadeDonation].CountOfID_i32)>0))
OR (((qry_Test_HasMadePurchase.CountOfID_i32)>0))
ORDER BY tbl_Customers_PromotionalGift.Ship_dtm, Temp_NameInfo.Code_i32;
If I change the "OR"s in the WHERE clause to "AND"s, the problem goes away (but the query pulls the wrong data). If I restrict the WHERE clause to one of the three comparisons, it works fine (regardless of which one. However, if I change the WHERE clause to add the three columns, and compare that number to 0, it gives the "Invalid Operation" error again. If I put any of the three columns in the SELECT clause, it gives the "Invalid Operation" error.
I don't get this. How can "x AND y AND z" be a valid operation, but "x OR y OR z" is not a valid operation?
Any thoughts?
Thanks,
Cliff