What is wrong with my append query?

Posted on 2007-08-07
Last Modified: 2013-11-05
I'm getting this error message :You tried to assign the null value to a variable that is not a variant data type" when i run an append query.

The query worked fine in the past with smaller data sets -  first time this has happened. No other changes to the query.  I tried using top 1, but still get error. I changed to Select query and it worked fine. I checked the properties of all the table fields - no "Required" fields and "Allow zero length" is set to "YES"  for all text fields. Any other suggestions?

Question by:tomfolinsbee
    LVL 119

    Expert Comment

    by:Rey Obrero
    post the sql of your append query

    Author Comment

    INSERT INTO tblrptMonitorHeader ( ID, NameA, NameB, tblBloomberg_TICKER_AND_EXCH_CODE, tblBloomberg_1_TICKER_AND_EXCH_CODE, NextAnnA, NextAnnB, [Day], [Avg], [Min], [Max], Win, Loss, Trade, Corr, DF, LastRatio, AvgRatio, StdevRatio, LastA, LastB, tblBloomberg_BEST_PE_RATIO, tblBloomberg_BEST_DIV_YLD, tblBloomberg_BEST_PX_BPS_RATIO, tblBloomberg_1_BEST_PE_RATIO, tblBloomberg_1_BEST_DIV_YLD, tblBloomberg_1_BEST_PX_BPS_RATIO, BEST_PEG_RATIOA, BEST_PEG_RATIOB, BEST_PX_SALES_RATIOA, BEST_PX_SALES_RATIOB, [EBIT%A], [EBIT%B], EQY_REC_CONSA, EQY_REC_CONSB, TOT_BUY_RECA, TOT_BUY_RECB, TOT_HOLD_RECA, TOT_HOLD_RECB, TOT_SELL_RECA, TOT_SELL_RECB, BEST_PX_CPS_RATIOA, BEST_PX_CPS_RATIOB, Spread_PE, Spread_PB, Spread_DY, ROA_A, ROE_A, ROA_B, ROE_B, Beta_A, Beta_B, tblBloomberg_Avg_Daily_Value_Traded_20D, tblBloomberg_1_Avg_Daily_Value_Traded_20D, MRG_A, MRG_B, RSI_A, RSI_B, TRR1_A, TRR1_B, TRR5_A, TRR5_B, 4WKUP_A, 4WKDN_A, 4WKUP_B, 4WKDN_B, MeanReturn, [%RatioChg], SideA, SideB, NDE_A, NDE_B, LastZ, AbsZ, IndustrySubGroupA, IndustrySubGroupB, PreviousZ, AbsPreviousZ, ChgAbsZ, COUNTRY_FULL_NAME, PositionOpenDays, StrategyA, StrategyB, StrategyRatio, CommentA, CommentB, Comment )
    SELECT tf_RealisedReturnGroupByPair.PairID, tblBloomberg.SHORT_NAME AS NameA, tblBloomberg_1.SHORT_NAME AS NameB, tblBloomberg.TICKER_AND_EXCH_CODE, tblBloomberg_1.TICKER_AND_EXCH_CODE, tblBloomberg.Expected_Report_DT AS NextAnnA, tblBloomberg_1.Expected_Report_DT AS NextAnnB, tf_RealisedReturnGroupByPair.Day, tf_RealisedReturnGroupByPair.Avg, tf_RealisedReturnGroupByPair.Min, tf_RealisedReturnGroupByPair.Max, tf_RealisedReturnGroupByPair.Win, tf_RealisedReturnGroupByPair.Loss, tf_RealisedReturnGroupByPair.Trade, tf_Statistic.Corr, tf_Statistic.DF, tf_Statistic.LastRatio, tf_Statistic.AvgRatio, tf_Statistic.StdevRatio, tblBloomberg.PX_LAST AS LastA, tblBloomberg_1.PX_LAST AS LastB, tblBloomberg.BEST_PE_RATIO, tblBloomberg.BEST_DIV_YLD, tblBloomberg.BEST_PX_BPS_RATIO, tblBloomberg_1.BEST_PE_RATIO, tblBloomberg_1.BEST_DIV_YLD, tblBloomberg_1.BEST_PX_BPS_RATIO, tblBloomberg.BEST_PEG_RATIO, tblBloomberg_1.BEST_PEG_RATIO, tblBloomberg.BEST_PX_SALES_RATIO, tblBloomberg_1.BEST_PX_SALES_RATIO, tblBloomberg!BEST_EBIT/tblBloomberg!BEST_SALES AS [EBIT%A], tblBloomberg_1!BEST_EBIT/tblBloomberg_1!BEST_SALES AS [EBIT%B], tblBloomberg.EQY_REC_CONS, tblBloomberg_1.EQY_REC_CONS, tblBloomberg.TOT_BUY_REC, tblBloomberg_1.TOT_BUY_REC, tblBloomberg.TOT_HOLD_REC, tblBloomberg_1.TOT_HOLD_REC, tblBloomberg.TOT_SELL_REC, tblBloomberg_1.TOT_SELL_REC, tblBloomberg.BEST_PX_CPS_RATIO, tblBloomberg_1.BEST_PX_CPS_RATIO, Abs(tblBloomberg!BEST_PE_RATIO-tblBloomberg_1!BEST_PE_RATIO) AS Spread_PE, Abs(tblBloomberg!BEST_PX_BPS_RATIO-tblBloomberg_1!BEST_PX_BPS_RATIO) AS Spread_PB, tblBloomberg!BEST_DIV_YLD-tblBloomberg_1!BEST_DIV_YLD AS Spread_DY, tblBloomberg.BEST_ROA AS ROA_A, tblBloomberg.BEST_ROE AS ROE_A, tblBloomberg_1.BEST_ROA AS ROA_B, tblBloomberg_1.BEST_ROE AS ROE_B, tblBloomberg.Eqy_Beta AS Beta_A, tblBloomberg_1.Eqy_Beta AS Beta_B, tblBloomberg.Avg_Daily_Value_Traded_20D, tblBloomberg_1.Avg_Daily_Value_Traded_20D, tblBloomberg.MRG_6M_RATIO AS MRG_A, tblBloomberg_1.MRG_6M_RATIO AS MRG_B, tblBloomberg.RSI_PX_52WEEK AS RSI_A, tblBloomberg_1.RSI_PX_52WEEK AS RSI_B, tblBloomberg.PREV_BUS_TRR_1DAY AS TRR1_A, tblBloomberg_1.PREV_BUS_TRR_1DAY AS TRR1_B, tblBloomberg.PREV_BUS_TRR_5DAY AS TRR5_A, tblBloomberg_1.PREV_BUS_TRR_5DAY AS TRR5_B, tblBloomberg.BEST_EPS_4WK_UP AS 4WKUP_A, tblBloomberg.BEST_EPS_4WK_DN AS 4WKDN_A, tblBloomberg_1.BEST_EPS_4WK_UP AS 4WKUP_B, tblBloomberg_1.BEST_EPS_4WK_DN AS 4WKDN_B, Abs(tf_Statistic!LastRatio/tf_Statistic!AvgRatio-1) AS MeanReturn, tf_Statistic!LastRatio/tf_Statistic_PreviousDay!LastRatio-1 AS [%RatioChg], IIf(tf_Statistic!Zscore<-2,"Long ",IIf(tf_Statistic!Zscore>2,"Short ","")) AS SideA, IIf(tf_Statistic!Zscore<-2,"Short",IIf(tf_Statistic!Zscore>2,"Long ","")) AS SideB, tblBloomberg.NET_DEBT_TO_SHRHLDR_EQTY AS NDE_A, tblBloomberg_1.NET_DEBT_TO_SHRHLDR_EQTY AS NDE_B, tf_Statistic.Zscore, Abs(tf_Statistic!Zscore) AS AbsZ, tblBloomberg.INDUSTRY_SUBGROUP, tblBloomberg_1.INDUSTRY_SUBGROUP, tf_Statistic_PreviousDay!Zscore AS PreviousZ, Abs(tf_Statistic_PreviousDay!Zscore) AS AbsPreviousZ, Abs(tf_Statistic!Zscore)-Abs(tf_Statistic_PreviousDay!Zscore) AS ChgAbsZ, tblBloomberg.COUNTRY_FULL_NAME, Now()-tf_Statistic!PreviousTradeDate AS PositionOpenDays, IIf(tf_Statistic!LastRatio<tf_Statistic!AvgRatio,"LONG " & tblBloomberg!SHORT_NAME & " (" & tblBloomberg!TICKER_AND_EXCH_CODE & ")","SHORT " & tblBloomberg!SHORT_NAME & "( " & tblBloomberg!TICKER_AND_EXCH_CODE & ")") AS StrategyA, IIf(tf_Statistic!LastRatio<tf_Statistic!AvgRatio,"SHORT " & tblBloomberg_1!SHORT_NAME & "( " & tblBloomberg_1!TICKER_AND_EXCH_CODE & ")","LONG " & tblBloomberg_1!SHORT_NAME & "( " & tblBloomberg_1!TICKER_AND_EXCH_CODE & ")") AS StrategyB, IIf(tf_Statistic!LastRatio<tf_Statistic!AvgRatio," Ratio " & Round(tf_Statistic!LastRatio,4) & " or lower"," Ratio " & Round(tf_Statistic!LastRatio,4) & " or higher") AS StrategyRatio, Comment.Comment, Comment_1.Comment, Pair.PublishComment
    FROM (Comment RIGHT JOIN (tf_Statistic_PreviousDay INNER JOIN (tf_Statistic RIGHT JOIN (((tf_RealisedReturnGroupByPair RIGHT JOIN Pair ON tf_RealisedReturnGroupByPair.PairID = Pair.ID) INNER JOIN tblBloomberg ON Pair.SecurityA = tblBloomberg.Ticker) INNER JOIN tblBloomberg AS tblBloomberg_1 ON Pair.SecurityB = tblBloomberg_1.Ticker) ON tf_Statistic.pairlistid = Pair.ID) ON tf_Statistic_PreviousDay.pairlistid = Pair.ID) ON Comment.SecurityID = Pair.SecurityA) LEFT JOIN Comment AS Comment_1 ON Pair.SecurityB = Comment_1.SecurityID
    WHERE (((Pair.Monitor)=True))
    ORDER BY Abs(tf_Statistic!Zscore) DESC;
    LVL 58

    Expert Comment

    No need for the query (sorry cap!).

    > "You tried to assign the null value to a variable that is not a variant data type"

    That is not a Jet error message, but a VB error message. It has nothing to do with your query. Even if your query did call a VB function, the message would not be displayed. It would result in #Error in the field, that's all.

    Try the following:
    * Switch to VB (Alt+F11)
    * choose from the menu: (Tools | Options), [General], "Break on all errors"
    * run your code again (e.g. press the button)
    * when you see the error message, choose [Debug]
    * look at the highlighted line (you can post it here if you like)
    * DON'T FORGET: set back to:
       (Tools | Options), [General], "Break on Unhandled errors"


    Author Comment

    Thanks - tried that, but now the db crashes ... no error message though... just the "Sorry, MS Access has encountered a problem and has to shut down"...
    LVL 58

    Accepted Solution


    Try to decompile your database.
    * make a backup
    * run a command-line (e.g. WinKey+R) built like this:
       "<path to MSACCESS.exe>" /decompile "<path to your database>"
    * close, reopen, compact
    * switch to VB, verify the (Debug | Compile <project name>) is grayed out.
    * if it wasn't: compile, compact, close again, reopen
    * try your code again.

    Good luck!
    LVL 119

    Assisted Solution

    by:Rey Obrero
    missed that one..

    Author Comment

    Thanks for the suggestions. Turns out the problem was the autonumber field in the table - I changed it to an integer, and the problem was resolved. Thanks again!

    Featured Post

    Top 6 Sources for Identifying Threat Actor TTPs

    Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

    Join & Write a Comment

    Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
    I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
    As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
    Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…

    730 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now