• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1546
  • Last Modified:

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?

0
tomfolinsbee
Asked:
tomfolinsbee
  • 3
  • 2
  • 2
2 Solutions
 
Rey Obrero (Capricorn1)Commented:
post the sql of your append query
0
 
tomfolinsbeeAuthor Commented:
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;
0
 
harfangCommented:
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°)
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
tomfolinsbeeAuthor Commented:
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"...
0
 
harfangCommented:
Ouch.

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!
(°v°)
0
 
Rey Obrero (Capricorn1)Commented:
markus,
missed that one..
0
 
tomfolinsbeeAuthor Commented:
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!
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 3
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now