asked on

What is wrong with my append query?

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?

Rey Obrero (Capricorn1)

post the sql of your append query

tomfolinsbee

ASKER

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;

Markus Fischer

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"

Cheers!
(°v°)

tomfolinsbee

ASKER

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"...

ASKER CERTIFIED SOLUTION

Markus Fischer

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Rey Obrero (Capricorn1)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

tomfolinsbee

ASKER

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!