No Current Record Error

[cariengon]

I'm running a make table query that I've run just fine over the last year. All of a sudden, it's running and then giving me a "No Current Record" error. I've tested all of the queries that build this query and they all work fine giving me the expected answer. There is no coding behind this. Any idea why this may be happening?

Thanks,
Carie

 

[genomon]

Any idea why this may be happening?

Could be a missing or altered field name in one of the source tables. Can you post back the SQL?

"Business conventions are important because they demonstrate how many people a company can operate without."

 

[cariengon]

Here's the SQL. I've been through the query and cannot see an altered or missing field. Maybe it's in one of the source queries. But I would think that the query would tell me where it didn't find the field...

SELECT [201_EFFECTIVE_Date_OpenClaims].[Final Status], [201_EFFECTIVE_Date_OpenClaims].[Resolved On], [201_EFFECTIVE_Date_OpenClaims].StatusOnEffectiveDate, DistributionTransactions.ClaimsDecLookupKEY, [201_EFFECTIVE_Date_GLAccounts].[Cash Account], [201_EFFECTIVE_Date_GLAccounts].[Cash Liability Account], [201_EFFECTIVE_Date_GLAccounts].[Cash Cost Center], [201_EFFECTIVE_Date_GLAccounts].[Stock Cash Account], [201_EFFECTIVE_Date_GLAccounts].[Stock Liability Account], [201_EFFECTIVE_Date_GLAccounts].[Stock Cost Center], RPT_ReserveReleases_1_Allowed.O, RPT_ReserveReleases_1_AllowedInt.O, RPT_ReserveReleases_1_AllowedTotal.O, RPT_ReserveReleases_1_Allowed.U, RPT_ReserveReleases_1_AllowedInt.U, RPT_ReserveReleases_1_AllowedTotal.U, RPT_ReserveReleases_1_Allowed.R, RPT_ReserveReleases_1_AllowedInt.R, RPT_ReserveReleases_1_AllowedTotal.R, RPT_ReserveReleases_1_AllowedTotal.D, RPT_ReserveReleases_1_Cash.O, RPT_ReserveReleases_1_Cash.U, RPT_ReserveReleases_1_Cash.R, RPT_ReserveReleases_1_Cash.D, RPT_ReserveReleases_1_Cash.EO, RPT_ReserveReleases_1_Cash.EU, RPT_ReserveReleases_1_Cash.ER, RPT_ReserveReleases_1_Cash.ED, RPT_ReserveReleases_1_Stock.O, RPT_ReserveReleases_1_Stock.U, RPT_ReserveReleases_1_Stock.T, RPT_ReserveReleases_1_Stock.R, RPT_ReserveReleases_1_Stock.D, RPT_ReserveReleases_1_Stock.EO, RPT_ReserveReleases_1_Stock.EU, RPT_ReserveReleases_1_Stock.ER, RPT_ReserveReleases_1_Stock.ED, RPT_ReserveReleases_1_CVV.O, RPT_ReserveReleases_1_CVV.U, RPT_ReserveReleases_1_CVV.R, RPT_ReserveReleases_1_CVV.D, DistributionTransactions.[Affiliated Claim Class Descr], DistributionTransactions.BCRT_Creditor_Num, [104_BCRT_Creditor_tbl].BCRT_Creditor_Name, [104_BCRT_Creditor_tbl].fld_Assigned_to, DistributionTransactions.CLAIM_SCHED_NO, RPT_ALLCreditors_1_UNION_CLAIMS_SCH.fld_Omnibus_Final, RPT_ALLCreditors_1_UNION_CLAIMS_SCH.fld_Reserve, RPT_ALLCreditors_1_UNION_CLAIMS_SCH.fld_OrderOrSettleDate, RPT_ALLCreditors_1_UNION_CLAIMS_SCH.CLAIM_NM, RPT_ALLCreditors_1_UNION_CLAIMS_SCH.ADDRESS1, RPT_ALLCreditors_1_UNION_CLAIMS_SCH.ADDRESS2, RPT_ALLCreditors_1_UNION_CLAIMS_SCH.ADDRESS3, RPT_ALLCreditors_1_UNION_CLAIMS_SCH.CITY, RPT_ALLCreditors_1_UNION_CLAIMS_SCH.STATE, RPT_ALLCreditors_1_UNION_CLAIMS_SCH.ZIP, RPT_ALLCreditors_1_UNION_CLAIMS_SCH.CONTRACT, RPT_ALLCreditors_1_UNION_CLAIMS_SCH.CASE, RPT_ALLCreditors_1_UNION_CLAIMS_SCH.fld_LegalEntity, RPT_ALLCreditors_1_UNION_CLAIMS_SCH.fld_Debtor_Group, RPT_ReserveReleases_1_CashPrincandInt_BCRT23num.[SumOfCash Distribution as Recovery of Principal ROUNDED], RPT_ReserveReleases_1_CashPrincandInt_BCRT23num.[SumOfCash Distribution Attributed to Interest ROUNDED], RPT_ReserveReleases_1_CashPrincandInt_BCRT23num.BCRT2_Num, RPT_ReserveReleases_1_CashPrincandInt_BCRT23num.BCRT3_Num, RPT_ReserveReleases_1_CashPrincandInt_BCRT23num.Withholding, RPT_ReserveReleases_1_CashPrincandInt_BCRT23num.Tax_ID INTO RPT_ReserveRelease_2_Detail_ALL_TABLE
FROM (((((((((((201_EFFECTIVE_Date_GLAccounts RIGHT JOIN DistributionTransactions ON [201_EFFECTIVE_Date_GLAccounts].ClaimsDecLookupKEY = DistributionTransactions.ClaimsDecLookupKEY) LEFT JOIN 201_EFFECTIVE_Date_OpenClaims ON DistributionTransactions.CLAIM_SCHED_NO = [201_EFFECTIVE_Date_OpenClaims].CLAIM_SCHED_NO) LEFT JOIN RPT_ReserveReleases_1_AllowedTotal ON DistributionTransactions.ClaimsDecLookupKEY = RPT_ReserveReleases_1_AllowedTotal.ClaimsDecLookupKEY) LEFT JOIN RPT_ReserveReleases_1_Cash ON DistributionTransactions.ClaimsDecLookupKEY = RPT_ReserveReleases_1_Cash.ClaimsDecLookupKEY) LEFT JOIN RPT_ReserveReleases_1_Stock ON DistributionTransactions.ClaimsDecLookupKEY = RPT_ReserveReleases_1_Stock.ClaimsDecLookupKEY) LEFT JOIN 104_BCRT_Creditor_tbl ON DistributionTransactions.BCRT_Creditor_Num = [104_BCRT_Creditor_tbl].BCRT_Creditor_Num) LEFT JOIN RPT_ALLCreditors_1_UNION_CLAIMS_SCH ON DistributionTransactions.CLAIM_SCHED_NO = RPT_ALLCreditors_1_UNION_CLAIMS_SCH.[Claim or Sch Num]) LEFT JOIN RPT_ReserveReleases_1_Allowed ON DistributionTransactions.ClaimsDecLookupKEY = RPT_ReserveReleases_1_Allowed.ClaimsDecLookupKEY) LEFT JOIN RPT_ReserveReleases_1_AllowedInt ON DistributionTransactions.ClaimsDecLookupKEY = RPT_ReserveReleases_1_AllowedInt.ClaimsDecLookupKEY) LEFT JOIN DistributionManualTransactions ON DistributionTransactions.ClaimsDecLookupKEY = DistributionManualTransactions.ClaimsDecLookupKEY) LEFT JOIN RPT_ReserveReleases_1_CVV ON DistributionTransactions.ClaimsDecLookupKEY = RPT_ReserveReleases_1_CVV.ClaimsDecLookupKEY) LEFT JOIN RPT_ReserveReleases_1_CashPrincandInt_BCRT23num ON DistributionTransactions.ClaimsDecLookupKEY = RPT_ReserveReleases_1_CashPrincandInt_BCRT23num.ClaimsDecLookupKEY
GROUP BY [201_EFFECTIVE_Date_OpenClaims].[Final Status], [201_EFFECTIVE_Date_OpenClaims].[Resolved On], [201_EFFECTIVE_Date_OpenClaims].StatusOnEffectiveDate, DistributionTransactions.ClaimsDecLookupKEY, [201_EFFECTIVE_Date_GLAccounts].[Cash Account], [201_EFFECTIVE_Date_GLAccounts].[Cash Liability Account], [201_EFFECTIVE_Date_GLAccounts].[Cash Cost Center], [201_EFFECTIVE_Date_GLAccounts].[Stock Cash Account], [201_EFFECTIVE_Date_GLAccounts].[Stock Liability Account], [201_EFFECTIVE_Date_GLAccounts].[Stock Cost Center], RPT_ReserveReleases_1_Allowed.O, RPT_ReserveReleases_1_AllowedInt.O, RPT_ReserveReleases_1_AllowedTotal.O, RPT_ReserveReleases_1_Allowed.U, RPT_ReserveReleases_1_AllowedInt.U, RPT_ReserveReleases_1_AllowedTotal.U, RPT_ReserveReleases_1_Allowed.R, RPT_ReserveReleases_1_AllowedInt.R, RPT_ReserveReleases_1_AllowedTotal.R, RPT_ReserveReleases_1_AllowedTotal.D, RPT_ReserveReleases_1_Cash.O, RPT_ReserveReleases_1_Cash.U, RPT_ReserveReleases_1_Cash.R, RPT_ReserveReleases_1_Cash.D, RPT_ReserveReleases_1_Cash.EO, RPT_ReserveReleases_1_Cash.EU, RPT_ReserveReleases_1_Cash.ER, RPT_ReserveReleases_1_Cash.ED, RPT_ReserveReleases_1_Stock.O, RPT_ReserveReleases_1_Stock.U, RPT_ReserveReleases_1_Stock.T, RPT_ReserveReleases_1_Stock.R, RPT_ReserveReleases_1_Stock.D, RPT_ReserveReleases_1_Stock.EO, RPT_ReserveReleases_1_Stock.EU, RPT_ReserveReleases_1_Stock.ER, RPT_ReserveReleases_1_Stock.ED, RPT_ReserveReleases_1_CVV.O, RPT_ReserveReleases_1_CVV.U, RPT_ReserveReleases_1_CVV.R, RPT_ReserveReleases_1_CVV.D, DistributionTransactions.[Affiliated Claim Class Descr], DistributionTransactions.BCRT_Creditor_Num, [104_BCRT_Creditor_tbl].BCRT_Creditor_Name, [104_BCRT_Creditor_tbl].fld_Assigned_to, DistributionTransactions.CLAIM_SCHED_NO, RPT_ALLCreditors_1_UNION_CLAIMS_SCH.fld_Omnibus_Final, RPT_ALLCreditors_1_UNION_CLAIMS_SCH.fld_Reserve, RPT_ALLCreditors_1_UNION_CLAIMS_SCH.fld_OrderOrSettleDate, RPT_ALLCreditors_1_UNION_CLAIMS_SCH.CLAIM_NM, RPT_ALLCreditors_1_UNION_CLAIMS_SCH.ADDRESS1, RPT_ALLCreditors_1_UNION_CLAIMS_SCH.ADDRESS2, RPT_ALLCreditors_1_UNION_CLAIMS_SCH.ADDRESS3, RPT_ALLCreditors_1_UNION_CLAIMS_SCH.CITY, RPT_ALLCreditors_1_UNION_CLAIMS_SCH.STATE, RPT_ALLCreditors_1_UNION_CLAIMS_SCH.ZIP, RPT_ALLCreditors_1_UNION_CLAIMS_SCH.CONTRACT, RPT_ALLCreditors_1_UNION_CLAIMS_SCH.CASE, RPT_ALLCreditors_1_UNION_CLAIMS_SCH.fld_LegalEntity, RPT_ALLCreditors_1_UNION_CLAIMS_SCH.fld_Debtor_Group, RPT_ReserveReleases_1_CashPrincandInt_BCRT23num.[SumOfCash Distribution as Recovery of Principal ROUNDED], RPT_ReserveReleases_1_CashPrincandInt_BCRT23num.[SumOfCash Distribution Attributed to Interest ROUNDED], RPT_ReserveReleases_1_CashPrincandInt_BCRT23num.BCRT2_Num, RPT_ReserveReleases_1_CashPrincandInt_BCRT23num.BCRT3_Num, RPT_ReserveReleases_1_CashPrincandInt_BCRT23num.Withholding, RPT_ReserveReleases_1_CashPrincandInt_BCRT23num.Tax_ID, DistributionTransactions.ClaimsDecLookupKEY
ORDER BY [104_BCRT_Creditor_tbl].BCRT_Creditor_Name, DistributionTransactions.ClaimsDecLookupKEY;

T

 

[genomon]

WOW. Getting dizzy just looking at it. I'll mark the thread and try & get back tomorrow.

"Business conventions are important because they demonstrate how many people a company can operate without."

 

[cariengon]

The wierd thing is, is that I have a duplicate query of this one that has some criteria. When I run that query, it works just fine. My first test was to remove the criteria for all fields on this duplicate query - when I did and ran it, it got the same error. Then, I tried just removing 1 set of criteria (CLAIM_SCH_NO) and then I got the error... Something weird is going on here...

Thanks so much!
Carie

 

[genomon]

When adding & removing criteria as you mention, it is easy to remove one too many (or few) parens, which might give you the error.

"Business conventions are important because they demonstrate how many people a company can operate without."

 

[cariengon]

This is all being done in the design view, not the SQL view, so I wouldn't have messed up any of the parens. After playing with it, I think it's corruption in one of the tables. This query worked fine a few days ago and I haven't changed anything on it. It's suddenly giving me this error. We're going to rebuild the table and hope that solves our issue.

 

[cariengon]

Is there a way to delete this thread??

We found the error. It was a missing record in a table. Not sure why it would have errored out this way, being that it was all left joins - so if the record was missing, it shouldn't have mattered... Oh well...

Thanks for trying to help!!
~Carie

 

[genomon]

Carie - only the TT Gods can delete. Happy to hear you found resolution, though!

"Business conventions are important because they demonstrate how many people a company can operate without."