[Microsoft][ODBC Microsoft Access Driver]Number value out of range (null)

  • Quote

    [Microsoft][ODBC Microsoft Access Driver]Number value out of range (null)


    I get this message when using Excel to query an Access DB. I specify what query, not table, I want to retrieve. For one request, the data is returned; for another request on a query with nearly identical SQL, I get the message above. I've replaced all null cells in the source Excel sheet with hyphens --same message.


    I've been able to find nothing regarding a fix for this error message. Could someone please tell me what the heck is going on here?


    Thank you very much in advance for any assistance that you can provide.


    Regards,


    Bubbis

    :silverha:

  • Re: [Microsoft][ODBC Microsoft Access Driver]Number value out of range (null)


    Thanks for the response, Will. Below are both Access queries that I want to retrieve data from in Excel, beginning with the one that 'works.' They're monsters, but the only differences between them are the final WHERE clause.


    Quote


    SELECT AGT_PROF_BB_20051031.AGENT, AGT_PROF_BB_20051031.YEARBOOK, AGT_PROF_BB_20051031.AGENTNAME, AGT_PROF_BB_20051031.AGNTTYPE, AGT_PROF_BB_20051031.AGNTSTATUS, AGT_PROF_BB_20051031.CONTRTS AS ContVol, AGT_PROF_BB_20051031.AMOUNTFIN AS AmtFin, AGT_PROF_BB_20051031.PRPFEARN AS PRPF_Earn, AGT_PROF_BB_20051031.RPMEARN, AGT_PROF_BB_20051031.[COMMISS$] AS Commission, (COFandVOC.COF_Perc*AGT_PROF_BB_20051031.[TOTARAVG$]*(-1)) AS COF, (PRPF_Earn+COF) AS Net_Int_Margin, AGT_PROF_BB_20051031.BADDEBT AS Bad_Debt, (Net_Int_Margin+Bad_Debt) AS Credit_Margin, (COFandVOC.Var_Op_Cost*ContVol*(-1)) AS VarOpCost, (Credit_Margin+VarOpCost) AS Net_Earnings, (AGT_PROF_BB_20051031.CANCELS/ContVol) AS CXLPerc, ((-1)*(Bad_Debt/(PRPF_Earn+AGT_PROF_BB_20051031.RPMEARN+Commission))) AS Loss_Ratio, ((-1)*(Bad_Debt/AGT_PROF_BB_20051031.[TOTARAVG$])) AS Charge_Off_Perc, (AmtFin/ContVol) AS AvgAmtFin, AGT_PROF_BB_20051031.PL06CONT, AGT_PROF_BB_20051031.PL06CNCL, AGT_PROF_BB_20051031.[PL06PREM$], AGT_PROF_BB_20051031.[PL06DPAY$], AGT_PROF_BB_20051031.PL06AMFN, AGT_PROF_BB_20051031.[PL06EARN$], AGT_PROF_BB_20051031.[PL06LOSS$], AGT_PROF_BB_20051031.[PL06FLAG#], AGT_PROF_BB_20051031.[PL06FLAG$], AGT_PROF_BB_20051031.[PL06BILL#], AGT_PROF_BB_20051031.[PL06BILL$], AGT_PROF_BB_20051031.[PL06ARBL#], AGT_PROF_BB_20051031.[PL06ARBL$], AGT_PROF_BB_20051031.PL12CONT, AGT_PROF_BB_20051031.PL12CNCL, AGT_PROF_BB_20051031.[PL12PREM$], AGT_PROF_BB_20051031.[PL12DPAY$], AGT_PROF_BB_20051031.PL12AMFN, AGT_PROF_BB_20051031.[PL12EARN$], AGT_PROF_BB_20051031.[PL12LOSS$], AGT_PROF_BB_20051031.[PL12FLAG#], AGT_PROF_BB_20051031.[PL12FLAG$], AGT_PROF_BB_20051031.[PL12BILL#], AGT_PROF_BB_20051031.[PL12BILL$], AGT_PROF_BB_20051031.[PL12ARBL#], AGT_PROF_BB_20051031.[PL12ARBL$], AGT_PROF_BB_20051031.PLCMCONT, AGT_PROF_BB_20051031.PLCMCNCL, AGT_PROF_BB_20051031.[PLCMPREM$], AGT_PROF_BB_20051031.[PLCMDPAY$], AGT_PROF_BB_20051031.PLCMAMFN, AGT_PROF_BB_20051031.[PLCMEARN$], AGT_PROF_BB_20051031.[PLCMLOSS$], AGT_PROF_BB_20051031.[PLCMFLAG#], AGT_PROF_BB_20051031.[PLCMFLAG$], AGT_PROF_BB_20051031.[PLCMBILL#], AGT_PROF_BB_20051031.[PLCMBILL$], AGT_PROF_BB_20051031.[PLCMARBL#], AGT_PROF_BB_20051031.[PLCMARBL$], AGT_PROF_BB_20051031.FLDREP, AGT_PROF_BB_20051031.FLDREPNME, AGT_PROF_BB_20051031.[T06ARBAL#], AGT_PROF_BB_20051031.[T06ARBAL$], AGT_PROF_BB_20051031.[T06ARAVG$], AGT_PROF_BB_20051031.[T12ARBAL#], AGT_PROF_BB_20051031.[T12ARBAL$], AGT_PROF_BB_20051031.[T12ARAVG$], AGT_PROF_BB_20051031.[TCMARBAL#], AGT_PROF_BB_20051031.[TCMARBAL$], AGT_PROF_BB_20051031.[TCMARAVG$], AGT_PROF_BB_20051031.[TOTARBAL#], AGT_PROF_BB_20051031.[TOTARBAL$], AGT_PROF_BB_20051031.[TOTARAVG$]
    FROM AGT_PROF_BB_20051031 LEFT JOIN COFandVOC ON AGT_PROF_BB_20051031.YEARBOOK = COFandVOC.Year
    WHERE (((AGT_PROF_BB_20051031.AGNTTYPE)="B" Or (AGT_PROF_BB_20051031.AGNTTYPE)="M"));


    Quote


    SELECT AGT_PROF_BB_20051031.AGENT, AGT_PROF_BB_20051031.YEARBOOK, AGT_PROF_BB_20051031.AGENTNAME, AGT_PROF_BB_20051031.AGNTTYPE, AGT_PROF_BB_20051031.AGNTSTATUS, AGT_PROF_BB_20051031.CONTRTS AS ContVol, AGT_PROF_BB_20051031.AMOUNTFIN AS AmtFin, AGT_PROF_BB_20051031.PRPFEARN AS PRPF_Earn, AGT_PROF_BB_20051031.RPMEARN, AGT_PROF_BB_20051031.[COMMISS$] AS Commission, (COFandVOC.COF_Perc*AGT_PROF_BB_20051031.[TOTARAVG$]*(-1)) AS COF, (PRPF_Earn+COF) AS Net_Int_Margin, AGT_PROF_BB_20051031.BADDEBT AS Bad_Debt, (Net_Int_Margin+Bad_Debt) AS Credit_Margin, (COFandVOC.Var_Op_Cost*ContVol*(-1)) AS VarOpCost, (Credit_Margin+VarOpCost) AS Net_Earnings, (AGT_PROF_BB_20051031.CANCELS/ContVol) AS CXLPerc, ((-1)*(Bad_Debt/(PRPF_Earn+AGT_PROF_BB_20051031.RPMEARN+Commission))) AS Loss_Ratio, ((-1)*(Bad_Debt/AGT_PROF_BB_20051031.[TOTARAVG$])) AS Charge_Off_Perc, (AmtFin/ContVol) AS AvgAmtFin, AGT_PROF_BB_20051031.PL06CONT, AGT_PROF_BB_20051031.PL06CNCL, AGT_PROF_BB_20051031.[PL06PREM$], AGT_PROF_BB_20051031.[PL06DPAY$], AGT_PROF_BB_20051031.PL06AMFN, AGT_PROF_BB_20051031.[PL06EARN$], AGT_PROF_BB_20051031.[PL06LOSS$], AGT_PROF_BB_20051031.[PL06FLAG#], AGT_PROF_BB_20051031.[PL06FLAG$], AGT_PROF_BB_20051031.[PL06BILL#], AGT_PROF_BB_20051031.[PL06BILL$], AGT_PROF_BB_20051031.[PL06ARBL#], AGT_PROF_BB_20051031.[PL06ARBL$], AGT_PROF_BB_20051031.PL12CONT, AGT_PROF_BB_20051031.PL12CNCL, AGT_PROF_BB_20051031.[PL12PREM$], AGT_PROF_BB_20051031.[PL12DPAY$], AGT_PROF_BB_20051031.PL12AMFN, AGT_PROF_BB_20051031.[PL12EARN$], AGT_PROF_BB_20051031.[PL12LOSS$], AGT_PROF_BB_20051031.[PL12FLAG#], AGT_PROF_BB_20051031.[PL12FLAG$], AGT_PROF_BB_20051031.[PL12BILL#], AGT_PROF_BB_20051031.[PL12BILL$], AGT_PROF_BB_20051031.[PL12ARBL#], AGT_PROF_BB_20051031.[PL12ARBL$], AGT_PROF_BB_20051031.PLCMCONT, AGT_PROF_BB_20051031.PLCMCNCL, AGT_PROF_BB_20051031.[PLCMPREM$], AGT_PROF_BB_20051031.[PLCMDPAY$], AGT_PROF_BB_20051031.PLCMAMFN, AGT_PROF_BB_20051031.[PLCMEARN$], AGT_PROF_BB_20051031.[PLCMLOSS$], AGT_PROF_BB_20051031.[PLCMFLAG#], AGT_PROF_BB_20051031.[PLCMFLAG$], AGT_PROF_BB_20051031.[PLCMBILL#], AGT_PROF_BB_20051031.[PLCMBILL$], AGT_PROF_BB_20051031.[PLCMARBL#], AGT_PROF_BB_20051031.[PLCMARBL$], AGT_PROF_BB_20051031.FLDREP, AGT_PROF_BB_20051031.FLDREPNME, AGT_PROF_BB_20051031.[T06ARBAL#], AGT_PROF_BB_20051031.[T06ARBAL$], AGT_PROF_BB_20051031.[T06ARAVG$], AGT_PROF_BB_20051031.[T12ARBAL#], AGT_PROF_BB_20051031.[T12ARBAL$], AGT_PROF_BB_20051031.[T12ARAVG$], AGT_PROF_BB_20051031.[TCMARBAL#], AGT_PROF_BB_20051031.[TCMARBAL$], AGT_PROF_BB_20051031.[TCMARAVG$], AGT_PROF_BB_20051031.[TOTARBAL#], AGT_PROF_BB_20051031.[TOTARBAL$], AGT_PROF_BB_20051031.[TOTARAVG$]
    FROM AGT_PROF_BB_20051031 LEFT JOIN COFandVOC ON AGT_PROF_BB_20051031.YEARBOOK=COFandVOC.Year
    WHERE (((AGT_PROF_BB_20051031.AGNTSTATUS)="X" Or (AGT_PROF_BB_20051031.AGNTSTATUS)="C"));


    Both the AGNTTYPE and AGNTSTATUS columns include no nulls and are of the 'text' type.


    Thank you for assisting me, and sorry about the enormity of the Access queries.


    Regards,


    bubbis

    :silverha:

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!