you tried to assign the null value to a variable that is not avariant data type - MsAccess 2007

  • Thread starter Thread starter Jaycee
  • Start date Start date
J

Jaycee

INSERT INTO WINB_LOYMESSAGES_VER ( LM_MSG_CNT, LM_MSG_NUMBER,
LM_MSG_TITLE,
LM_MSG_STATUS, LM_MSG_PRIORITY, LM_MSG_ADDED, LM_MSG_UPDATED,
LM_CRI_CVA_MIN,
LM_CRI_CVA_MAX, LM_CRI_CL_FS_MIN, LM_CRI_CL_FS_6MO, LM_CRI_AUTO_CL,
LM_CRI_TELEFRND_MIN,
LM_CRI_MTHS_SRV_MIN, LM_CRI_MTHS_SRV_MAX, LM_CRI_INTNL_MIN,
LM_CRI_INTNL_MAX,
LM_CRI_INT_GRP, LM_CRI_LEC_GRP, LM_CRI_STATE_GRP, LM_CRI_PRI_OCP_GRP,
LM_CRI_INTL_OCP_GRP, LM_CRI_CC_OCP_GRP, LM_CRI_FAN, LM_CRI_INTERNET,
LM_CRI_WACC,
LM_CRI_PHONE_ME, LM_CRI_TOLLFREE, LM_CRI_ZIP_GRP, LM_CRI_MIN_ANIS,
LM_CRI_MAX_ANIS,
LM_CRI_WIRELESS, LM_CRI_PREV_WIRELESS, LM_CRI_INTRALATA,
LM_CRI_INTRA_PROFIT,
LM_CRI_AUTO_ROUNDUP, LM_CRI_RU_12MO_MIN, LM_CRI_ACCT_GRP,
LM_CRI_PRIV_OPT,
LM_CRI_CSI__1, LM_CRI_CSI__2, LM_CRI_CSI__3, LM_CRI_CR_CLS__1,
LM_CRI_CR_CLS__2,
LM_CRI_CR_CLS__3, LM_CRI_CR_CLS__4, LM_CRI_CR_CLS__5,
LM_CRI_CR_CLS__6, LM_CRI_WALD,
LM_CRI_LOCAL, LM_CRI_ACCT_GRP_CTL, LM_CRI_RECYCLE_MOS, LM_MSG_TEXT__1,
LM_MSG_TEXT__2,
LM_MSG_TEXT__3, LM_MSG_TEXT__4, LM_MSG_TEXT__5, LM_MSG_TEXT__6,
LM_MSG_TEXT__7,
LM_MSG_TEXT__8, LM_MSG_TEXT__9, LM_MSG_TEXT__10, LM_MSG_TEXT__11,
LM_MSG_TEXT__12,
LM_MSG_TEXT__13, LM_MSG_TEXT__14, LM_MSG_TEXT__15, LM_MSG_TEXT__16,
LM_CRI_WAW_DAYS_MIN, LM_CRI_WAW_DAYS_MAX )
SELECT LM_CRITERIA.LMC_MSG_CNT, LM_CRITERIA.LMC_MSG_NO,
LM_CRITERIA.LMC_MSG_TITLE,
LM_CRITERIA.LMC_MSG_STATUS, LM_CRITERIA.LMC_MSG_PRIORITY,
Format(LM_CRITERIA.LMC_MSG_DATE_ADDED,"yyyymmdd") AS DateAdded,
Format(LM_CRITERIA.LMC_MSG_LAST_UPDATED,"yyyymmdd") AS LastUpdated,
LM_CRITERIA.LMC_CRI_CVA_MIN, LM_CRITERIA.LMC_CRI_CVA_MAX,
LM_CRITERIA.LMC_CRI_HIST_CL_FS_MIN, LM_CRITERIA.LMC_CRI_6MO_CL_FS,
LM_CRITERIA.LMC_CRI_AUTO_LTRS, LM_CRITERIA.LMC_CRI_TELEFRND_MIN,
LM_CRITERIA.LMC_CRI_MTHS_ON_SERVICE_MIN,
LM_CRITERIA.LMC_CRI_MTHS_ON_SERVICE_MAX,
LM_CRITERIA.LMC_CRI_6MO_AVG_INTL_USE_MIN,
LM_CRITERIA.LMC_CRI_6MO_AVG_INTL_USE_MAX,
LM_CRITERIA.LMC_CRI_INT_CODE_GRP, LM_CRITERIA.LMC_CRI_LEC_GRP,
LM_CRITERIA.LMC_CRI_STATE_GRP, LM_CRITERIA.LMC_CRI_PRI_OCP_GRP,
LM_CRITERIA.LMC_CRI_INTL_OCP_GRP, LM_CRITERIA.LMC_CRI_CC_OCP_GRP,
LM_CRITERIA.LMC_CRI_FAN, LM_CRITERIA.LMC_CRI_INTERNET,
LM_CRITERIA.LMC_CRI_WACC,
LM_CRITERIA.LMC_CRI_PHONEME, LM_CRITERIA.LMC_CRI_TOLLFREE,
LM_CRITERIA.LMC_ZIP_CODE_GRP,
LM_CRITERIA.LMC_MIN_ANIS, LM_CRITERIA.LMC_MAX_ANIS,
LM_CRITERIA.LMC_CRI_WIRELESS,
LM_CRITERIA.LMC_CRI_PREV_WIRELESS, LM_CRITERIA.LMC_CRI_INTRALATA,
LM_CRITERIA.LMC_CRI_INTRA_PROFIT, LM_CRITERIA.LMC_CRI_AUTO_ROUNDUP,
LM_CRITERIA.LMC_CRI_RU_12MO_MIN, LM_CRITERIA.LMC_CRI_ACCT_GRP,
LM_CRITERIA.LMC_CRI_PRIV_OPT, IIf([LMC_CRI_CSI_01]='-1','01',' ') AS
Expr7, IIf([LMC_CRI_CSI_02]='-
1','02',' ') AS Expr8, IIf([LMC_CRI_CSI_03]='-1','03',' ') AS Expr9,
IIf([LMC_CRI_CR_CLS_A]='-1','A',' ') AS
Expr1, IIf([LMC_CRI_CR_CLS_B]='-1','B',' ') AS Expr2, IIf
([LMC_CRI_CR_CLS_C]='-1','C',' ') AS Expr3,
IIf([LMC_CRI_CR_CLS_D]='-1','D',' ') AS Expr4, IIf([LMC_CRI_CR_CLS_P]
='-1','P',' ') AS Expr5,
IIf([LMC_CRI_CR_CLS_X]='-1','X',' ') AS Expr6,
LM_CRITERIA.LMC_CRI_WALD,
LM_CRITERIA.LMC_CRI_LOCAL, LM_CRITERIA.LMC_CRI_ACCT_GRP_CTRL,
LM_CRITERIA.LMC_CRI_RECYCLE_MOS, LM_TEXT_Crosstab.[1],
LM_TEXT_Crosstab.[2],
LM_TEXT_Crosstab.[3], LM_TEXT_Crosstab.[4], LM_TEXT_Crosstab.[5],
LM_TEXT_Crosstab.[6],
LM_TEXT_Crosstab.[7], LM_TEXT_Crosstab.[8], LM_TEXT_Crosstab.[9],
LM_TEXT_Crosstab.[10],
LM_TEXT_Crosstab.[11], LM_TEXT_Crosstab.[12], LM_TEXT_Crosstab.[13],
LM_TEXT_Crosstab.[14],
LM_TEXT_Crosstab.[15], LM_TEXT_Crosstab.[16],
LM_CRITERIA.LMC_CRI_WTEN_MIN,
LM_CRITERIA.LMC_CRI_WTEN_MAX
FROM LM_CRITERIA LEFT JOIN LM_TEXT_Crosstab ON LM_CRITERIA.LMC_MSG_NO
=
LM_TEXT_Crosstab.LMC_MSG_NO;

It gives me a "you tried to assign the null value to a variable that
is not a variant data type" error.

The macro stops with a 2950 error
 
The implication is that (at least one of) your variable is not typed as a
variant but that your SQL statement is attempting to stuff a "Null" into it.
Which of those are variables?

Regards

Jeff Boyce
Microsoft Access MVP

Jaycee said:
INSERT INTO WINB_LOYMESSAGES_VER ( LM_MSG_CNT, LM_MSG_NUMBER,
LM_MSG_TITLE,
LM_MSG_STATUS, LM_MSG_PRIORITY, LM_MSG_ADDED, LM_MSG_UPDATED,
LM_CRI_CVA_MIN,
LM_CRI_CVA_MAX, LM_CRI_CL_FS_MIN, LM_CRI_CL_FS_6MO, LM_CRI_AUTO_CL,
LM_CRI_TELEFRND_MIN,
LM_CRI_MTHS_SRV_MIN, LM_CRI_MTHS_SRV_MAX, LM_CRI_INTNL_MIN,
LM_CRI_INTNL_MAX,
LM_CRI_INT_GRP, LM_CRI_LEC_GRP, LM_CRI_STATE_GRP, LM_CRI_PRI_OCP_GRP,
LM_CRI_INTL_OCP_GRP, LM_CRI_CC_OCP_GRP, LM_CRI_FAN, LM_CRI_INTERNET,
LM_CRI_WACC,
LM_CRI_PHONE_ME, LM_CRI_TOLLFREE, LM_CRI_ZIP_GRP, LM_CRI_MIN_ANIS,
LM_CRI_MAX_ANIS,
LM_CRI_WIRELESS, LM_CRI_PREV_WIRELESS, LM_CRI_INTRALATA,
LM_CRI_INTRA_PROFIT,
LM_CRI_AUTO_ROUNDUP, LM_CRI_RU_12MO_MIN, LM_CRI_ACCT_GRP,
LM_CRI_PRIV_OPT,
LM_CRI_CSI__1, LM_CRI_CSI__2, LM_CRI_CSI__3, LM_CRI_CR_CLS__1,
LM_CRI_CR_CLS__2,
LM_CRI_CR_CLS__3, LM_CRI_CR_CLS__4, LM_CRI_CR_CLS__5,
LM_CRI_CR_CLS__6, LM_CRI_WALD,
LM_CRI_LOCAL, LM_CRI_ACCT_GRP_CTL, LM_CRI_RECYCLE_MOS, LM_MSG_TEXT__1,
LM_MSG_TEXT__2,
LM_MSG_TEXT__3, LM_MSG_TEXT__4, LM_MSG_TEXT__5, LM_MSG_TEXT__6,
LM_MSG_TEXT__7,
LM_MSG_TEXT__8, LM_MSG_TEXT__9, LM_MSG_TEXT__10, LM_MSG_TEXT__11,
LM_MSG_TEXT__12,
LM_MSG_TEXT__13, LM_MSG_TEXT__14, LM_MSG_TEXT__15, LM_MSG_TEXT__16,
LM_CRI_WAW_DAYS_MIN, LM_CRI_WAW_DAYS_MAX )
SELECT LM_CRITERIA.LMC_MSG_CNT, LM_CRITERIA.LMC_MSG_NO,
LM_CRITERIA.LMC_MSG_TITLE,
LM_CRITERIA.LMC_MSG_STATUS, LM_CRITERIA.LMC_MSG_PRIORITY,
Format(LM_CRITERIA.LMC_MSG_DATE_ADDED,"yyyymmdd") AS DateAdded,
Format(LM_CRITERIA.LMC_MSG_LAST_UPDATED,"yyyymmdd") AS LastUpdated,
LM_CRITERIA.LMC_CRI_CVA_MIN, LM_CRITERIA.LMC_CRI_CVA_MAX,
LM_CRITERIA.LMC_CRI_HIST_CL_FS_MIN, LM_CRITERIA.LMC_CRI_6MO_CL_FS,
LM_CRITERIA.LMC_CRI_AUTO_LTRS, LM_CRITERIA.LMC_CRI_TELEFRND_MIN,
LM_CRITERIA.LMC_CRI_MTHS_ON_SERVICE_MIN,
LM_CRITERIA.LMC_CRI_MTHS_ON_SERVICE_MAX,
LM_CRITERIA.LMC_CRI_6MO_AVG_INTL_USE_MIN,
LM_CRITERIA.LMC_CRI_6MO_AVG_INTL_USE_MAX,
LM_CRITERIA.LMC_CRI_INT_CODE_GRP, LM_CRITERIA.LMC_CRI_LEC_GRP,
LM_CRITERIA.LMC_CRI_STATE_GRP, LM_CRITERIA.LMC_CRI_PRI_OCP_GRP,
LM_CRITERIA.LMC_CRI_INTL_OCP_GRP, LM_CRITERIA.LMC_CRI_CC_OCP_GRP,
LM_CRITERIA.LMC_CRI_FAN, LM_CRITERIA.LMC_CRI_INTERNET,
LM_CRITERIA.LMC_CRI_WACC,
LM_CRITERIA.LMC_CRI_PHONEME, LM_CRITERIA.LMC_CRI_TOLLFREE,
LM_CRITERIA.LMC_ZIP_CODE_GRP,
LM_CRITERIA.LMC_MIN_ANIS, LM_CRITERIA.LMC_MAX_ANIS,
LM_CRITERIA.LMC_CRI_WIRELESS,
LM_CRITERIA.LMC_CRI_PREV_WIRELESS, LM_CRITERIA.LMC_CRI_INTRALATA,
LM_CRITERIA.LMC_CRI_INTRA_PROFIT, LM_CRITERIA.LMC_CRI_AUTO_ROUNDUP,
LM_CRITERIA.LMC_CRI_RU_12MO_MIN, LM_CRITERIA.LMC_CRI_ACCT_GRP,
LM_CRITERIA.LMC_CRI_PRIV_OPT, IIf([LMC_CRI_CSI_01]='-1','01',' ') AS
Expr7, IIf([LMC_CRI_CSI_02]='-
1','02',' ') AS Expr8, IIf([LMC_CRI_CSI_03]='-1','03',' ') AS Expr9,
IIf([LMC_CRI_CR_CLS_A]='-1','A',' ') AS
Expr1, IIf([LMC_CRI_CR_CLS_B]='-1','B',' ') AS Expr2, IIf
([LMC_CRI_CR_CLS_C]='-1','C',' ') AS Expr3,
IIf([LMC_CRI_CR_CLS_D]='-1','D',' ') AS Expr4, IIf([LMC_CRI_CR_CLS_P]
='-1','P',' ') AS Expr5,
IIf([LMC_CRI_CR_CLS_X]='-1','X',' ') AS Expr6,
LM_CRITERIA.LMC_CRI_WALD,
LM_CRITERIA.LMC_CRI_LOCAL, LM_CRITERIA.LMC_CRI_ACCT_GRP_CTRL,
LM_CRITERIA.LMC_CRI_RECYCLE_MOS, LM_TEXT_Crosstab.[1],
LM_TEXT_Crosstab.[2],
LM_TEXT_Crosstab.[3], LM_TEXT_Crosstab.[4], LM_TEXT_Crosstab.[5],
LM_TEXT_Crosstab.[6],
LM_TEXT_Crosstab.[7], LM_TEXT_Crosstab.[8], LM_TEXT_Crosstab.[9],
LM_TEXT_Crosstab.[10],
LM_TEXT_Crosstab.[11], LM_TEXT_Crosstab.[12], LM_TEXT_Crosstab.[13],
LM_TEXT_Crosstab.[14],
LM_TEXT_Crosstab.[15], LM_TEXT_Crosstab.[16],
LM_CRITERIA.LMC_CRI_WTEN_MIN,
LM_CRITERIA.LMC_CRI_WTEN_MAX
FROM LM_CRITERIA LEFT JOIN LM_TEXT_Crosstab ON LM_CRITERIA.LMC_MSG_NO
=
LM_TEXT_Crosstab.LMC_MSG_NO;

It gives me a "you tried to assign the null value to a variable that
is not a variant data type" error.

The macro stops with a 2950 error
Click to expand...
 
The implication is that (at least one of) your variable is not typed as a
variant but that your SQL statement is attempting to stuff a "Null" into it.
Which of those are variables?

Regards

Jeff Boyce
Microsoft Access MVP


INSERT INTO WINB_LOYMESSAGES_VER ( LM_MSG_CNT, LM_MSG_NUMBER,
LM_MSG_TITLE,
LM_MSG_STATUS, LM_MSG_PRIORITY, LM_MSG_ADDED, LM_MSG_UPDATED,
LM_CRI_CVA_MIN,
LM_CRI_CVA_MAX, LM_CRI_CL_FS_MIN, LM_CRI_CL_FS_6MO, LM_CRI_AUTO_CL,
LM_CRI_TELEFRND_MIN,
LM_CRI_MTHS_SRV_MIN, LM_CRI_MTHS_SRV_MAX, LM_CRI_INTNL_MIN,
LM_CRI_INTNL_MAX,
LM_CRI_INT_GRP, LM_CRI_LEC_GRP, LM_CRI_STATE_GRP, LM_CRI_PRI_OCP_GRP,
LM_CRI_INTL_OCP_GRP, LM_CRI_CC_OCP_GRP, LM_CRI_FAN, LM_CRI_INTERNET,
LM_CRI_WACC,
LM_CRI_PHONE_ME, LM_CRI_TOLLFREE, LM_CRI_ZIP_GRP, LM_CRI_MIN_ANIS,
LM_CRI_MAX_ANIS,
LM_CRI_WIRELESS, LM_CRI_PREV_WIRELESS, LM_CRI_INTRALATA,
LM_CRI_INTRA_PROFIT,
LM_CRI_AUTO_ROUNDUP, LM_CRI_RU_12MO_MIN, LM_CRI_ACCT_GRP,
LM_CRI_PRIV_OPT,
LM_CRI_CSI__1, LM_CRI_CSI__2, LM_CRI_CSI__3, LM_CRI_CR_CLS__1,
LM_CRI_CR_CLS__2,
LM_CRI_CR_CLS__3, LM_CRI_CR_CLS__4, LM_CRI_CR_CLS__5,
LM_CRI_CR_CLS__6, LM_CRI_WALD,
LM_CRI_LOCAL, LM_CRI_ACCT_GRP_CTL, LM_CRI_RECYCLE_MOS, LM_MSG_TEXT__1,
LM_MSG_TEXT__2,
LM_MSG_TEXT__3, LM_MSG_TEXT__4, LM_MSG_TEXT__5, LM_MSG_TEXT__6,
LM_MSG_TEXT__7,
LM_MSG_TEXT__8, LM_MSG_TEXT__9, LM_MSG_TEXT__10, LM_MSG_TEXT__11,
LM_MSG_TEXT__12,
LM_MSG_TEXT__13, LM_MSG_TEXT__14, LM_MSG_TEXT__15, LM_MSG_TEXT__16,
LM_CRI_WAW_DAYS_MIN, LM_CRI_WAW_DAYS_MAX )
SELECT LM_CRITERIA.LMC_MSG_CNT, LM_CRITERIA.LMC_MSG_NO,
LM_CRITERIA.LMC_MSG_TITLE,
LM_CRITERIA.LMC_MSG_STATUS, LM_CRITERIA.LMC_MSG_PRIORITY,
Format(LM_CRITERIA.LMC_MSG_DATE_ADDED,"yyyymmdd") AS DateAdded,
Format(LM_CRITERIA.LMC_MSG_LAST_UPDATED,"yyyymmdd") AS LastUpdated,
LM_CRITERIA.LMC_CRI_CVA_MIN, LM_CRITERIA.LMC_CRI_CVA_MAX,
LM_CRITERIA.LMC_CRI_HIST_CL_FS_MIN, LM_CRITERIA.LMC_CRI_6MO_CL_FS,
LM_CRITERIA.LMC_CRI_AUTO_LTRS, LM_CRITERIA.LMC_CRI_TELEFRND_MIN,
LM_CRITERIA.LMC_CRI_MTHS_ON_SERVICE_MIN,
LM_CRITERIA.LMC_CRI_MTHS_ON_SERVICE_MAX,
LM_CRITERIA.LMC_CRI_6MO_AVG_INTL_USE_MIN,
LM_CRITERIA.LMC_CRI_6MO_AVG_INTL_USE_MAX,
LM_CRITERIA.LMC_CRI_INT_CODE_GRP, LM_CRITERIA.LMC_CRI_LEC_GRP,
LM_CRITERIA.LMC_CRI_STATE_GRP, LM_CRITERIA.LMC_CRI_PRI_OCP_GRP,
LM_CRITERIA.LMC_CRI_INTL_OCP_GRP, LM_CRITERIA.LMC_CRI_CC_OCP_GRP,
LM_CRITERIA.LMC_CRI_FAN, LM_CRITERIA.LMC_CRI_INTERNET,
LM_CRITERIA.LMC_CRI_WACC,
LM_CRITERIA.LMC_CRI_PHONEME, LM_CRITERIA.LMC_CRI_TOLLFREE,
LM_CRITERIA.LMC_ZIP_CODE_GRP,
LM_CRITERIA.LMC_MIN_ANIS, LM_CRITERIA.LMC_MAX_ANIS,
LM_CRITERIA.LMC_CRI_WIRELESS,
LM_CRITERIA.LMC_CRI_PREV_WIRELESS, LM_CRITERIA.LMC_CRI_INTRALATA,
LM_CRITERIA.LMC_CRI_INTRA_PROFIT, LM_CRITERIA.LMC_CRI_AUTO_ROUNDUP,
LM_CRITERIA.LMC_CRI_RU_12MO_MIN, LM_CRITERIA.LMC_CRI_ACCT_GRP,
LM_CRITERIA.LMC_CRI_PRIV_OPT, IIf([LMC_CRI_CSI_01]='-1','01',' ') AS
Expr7, IIf([LMC_CRI_CSI_02]='-
1','02',' ') AS Expr8, IIf([LMC_CRI_CSI_03]='-1','03',' ') AS Expr9,
IIf([LMC_CRI_CR_CLS_A]='-1','A',' ') AS
Expr1, IIf([LMC_CRI_CR_CLS_B]='-1','B',' ') AS Expr2, IIf
([LMC_CRI_CR_CLS_C]='-1','C',' ') AS Expr3,
IIf([LMC_CRI_CR_CLS_D]='-1','D',' ') AS Expr4, IIf([LMC_CRI_CR_CLS_P]
='-1','P',' ') AS Expr5,
IIf([LMC_CRI_CR_CLS_X]='-1','X',' ') AS Expr6,
LM_CRITERIA.LMC_CRI_WALD,
LM_CRITERIA.LMC_CRI_LOCAL, LM_CRITERIA.LMC_CRI_ACCT_GRP_CTRL,
LM_CRITERIA.LMC_CRI_RECYCLE_MOS, LM_TEXT_Crosstab.[1],
LM_TEXT_Crosstab.[2],
LM_TEXT_Crosstab.[3], LM_TEXT_Crosstab.[4], LM_TEXT_Crosstab.[5],
LM_TEXT_Crosstab.[6],
LM_TEXT_Crosstab.[7], LM_TEXT_Crosstab.[8], LM_TEXT_Crosstab.[9],
LM_TEXT_Crosstab.[10],
LM_TEXT_Crosstab.[11], LM_TEXT_Crosstab.[12], LM_TEXT_Crosstab.[13],
LM_TEXT_Crosstab.[14],
LM_TEXT_Crosstab.[15], LM_TEXT_Crosstab.[16],
LM_CRITERIA.LMC_CRI_WTEN_MIN,
LM_CRITERIA.LMC_CRI_WTEN_MAX
FROM LM_CRITERIA LEFT JOIN LM_TEXT_Crosstab ON LM_CRITERIA.LMC_MSG_NO
=
LM_TEXT_Crosstab.LMC_MSG_NO;
Click to expand...
It gives me a "you tried to assign the null value to a variable that
is not a variant data type" error.
Click to expand...
The macro stops with a 2950 error
Click to expand...
Click to expand...


Tried to insert one record and it did not give the error but the the
data was not there
 
The implication is that (at least one of) your variable is not typed asa
variant but that your SQL statement is attempting to stuff a "Null" into it.
Which of those are variables?

Jeff Boyce
Microsoft Access MVP
Click to expand...
news:05c3d980-75c4-422f-a278-6a749b5d8149@f10g2000vbf.googlegroups.com....
Click to expand...
INSERT INTO WINB_LOYMESSAGES_VER ( LM_MSG_CNT, LM_MSG_NUMBER,
LM_MSG_TITLE,
LM_MSG_STATUS, LM_MSG_PRIORITY, LM_MSG_ADDED, LM_MSG_UPDATED,
LM_CRI_CVA_MIN,
LM_CRI_CVA_MAX, LM_CRI_CL_FS_MIN, LM_CRI_CL_FS_6MO, LM_CRI_AUTO_CL,
LM_CRI_TELEFRND_MIN,
LM_CRI_MTHS_SRV_MIN, LM_CRI_MTHS_SRV_MAX, LM_CRI_INTNL_MIN,
LM_CRI_INTNL_MAX,
LM_CRI_INT_GRP, LM_CRI_LEC_GRP, LM_CRI_STATE_GRP, LM_CRI_PRI_OCP_GRP,
LM_CRI_INTL_OCP_GRP, LM_CRI_CC_OCP_GRP, LM_CRI_FAN, LM_CRI_INTERNET,
LM_CRI_WACC,
LM_CRI_PHONE_ME, LM_CRI_TOLLFREE, LM_CRI_ZIP_GRP, LM_CRI_MIN_ANIS,
LM_CRI_MAX_ANIS,
LM_CRI_WIRELESS, LM_CRI_PREV_WIRELESS, LM_CRI_INTRALATA,
LM_CRI_INTRA_PROFIT,
LM_CRI_AUTO_ROUNDUP, LM_CRI_RU_12MO_MIN, LM_CRI_ACCT_GRP,
LM_CRI_PRIV_OPT,
LM_CRI_CSI__1, LM_CRI_CSI__2, LM_CRI_CSI__3, LM_CRI_CR_CLS__1,
LM_CRI_CR_CLS__2,
LM_CRI_CR_CLS__3, LM_CRI_CR_CLS__4, LM_CRI_CR_CLS__5,
LM_CRI_CR_CLS__6, LM_CRI_WALD,
LM_CRI_LOCAL, LM_CRI_ACCT_GRP_CTL, LM_CRI_RECYCLE_MOS, LM_MSG_TEXT__1,
LM_MSG_TEXT__2,
LM_MSG_TEXT__3, LM_MSG_TEXT__4, LM_MSG_TEXT__5, LM_MSG_TEXT__6,
LM_MSG_TEXT__7,
LM_MSG_TEXT__8, LM_MSG_TEXT__9, LM_MSG_TEXT__10, LM_MSG_TEXT__11,
LM_MSG_TEXT__12,
LM_MSG_TEXT__13, LM_MSG_TEXT__14, LM_MSG_TEXT__15, LM_MSG_TEXT__16,
LM_CRI_WAW_DAYS_MIN, LM_CRI_WAW_DAYS_MAX )
SELECT LM_CRITERIA.LMC_MSG_CNT, LM_CRITERIA.LMC_MSG_NO,
LM_CRITERIA.LMC_MSG_TITLE,
LM_CRITERIA.LMC_MSG_STATUS, LM_CRITERIA.LMC_MSG_PRIORITY,
Format(LM_CRITERIA.LMC_MSG_DATE_ADDED,"yyyymmdd") AS DateAdded,
Format(LM_CRITERIA.LMC_MSG_LAST_UPDATED,"yyyymmdd") AS LastUpdated,
LM_CRITERIA.LMC_CRI_CVA_MIN, LM_CRITERIA.LMC_CRI_CVA_MAX,
LM_CRITERIA.LMC_CRI_HIST_CL_FS_MIN, LM_CRITERIA.LMC_CRI_6MO_CL_FS,
LM_CRITERIA.LMC_CRI_AUTO_LTRS, LM_CRITERIA.LMC_CRI_TELEFRND_MIN,
LM_CRITERIA.LMC_CRI_MTHS_ON_SERVICE_MIN,
LM_CRITERIA.LMC_CRI_MTHS_ON_SERVICE_MAX,
LM_CRITERIA.LMC_CRI_6MO_AVG_INTL_USE_MIN,
LM_CRITERIA.LMC_CRI_6MO_AVG_INTL_USE_MAX,
LM_CRITERIA.LMC_CRI_INT_CODE_GRP, LM_CRITERIA.LMC_CRI_LEC_GRP,
LM_CRITERIA.LMC_CRI_STATE_GRP, LM_CRITERIA.LMC_CRI_PRI_OCP_GRP,
LM_CRITERIA.LMC_CRI_INTL_OCP_GRP, LM_CRITERIA.LMC_CRI_CC_OCP_GRP,
LM_CRITERIA.LMC_CRI_FAN, LM_CRITERIA.LMC_CRI_INTERNET,
LM_CRITERIA.LMC_CRI_WACC,
LM_CRITERIA.LMC_CRI_PHONEME, LM_CRITERIA.LMC_CRI_TOLLFREE,
LM_CRITERIA.LMC_ZIP_CODE_GRP,
LM_CRITERIA.LMC_MIN_ANIS, LM_CRITERIA.LMC_MAX_ANIS,
LM_CRITERIA.LMC_CRI_WIRELESS,
LM_CRITERIA.LMC_CRI_PREV_WIRELESS, LM_CRITERIA.LMC_CRI_INTRALATA,
LM_CRITERIA.LMC_CRI_INTRA_PROFIT, LM_CRITERIA.LMC_CRI_AUTO_ROUNDUP,
LM_CRITERIA.LMC_CRI_RU_12MO_MIN, LM_CRITERIA.LMC_CRI_ACCT_GRP,
LM_CRITERIA.LMC_CRI_PRIV_OPT, IIf([LMC_CRI_CSI_01]='-1','01',' ') AS
Expr7, IIf([LMC_CRI_CSI_02]='-
1','02',' ') AS Expr8, IIf([LMC_CRI_CSI_03]='-1','03',' ') AS Expr9,
IIf([LMC_CRI_CR_CLS_A]='-1','A',' ') AS
Expr1, IIf([LMC_CRI_CR_CLS_B]='-1','B',' ') AS Expr2, IIf
([LMC_CRI_CR_CLS_C]='-1','C',' ') AS Expr3,
IIf([LMC_CRI_CR_CLS_D]='-1','D',' ') AS Expr4, IIf([LMC_CRI_CR_CLS_P]
='-1','P',' ') AS Expr5,
IIf([LMC_CRI_CR_CLS_X]='-1','X',' ') AS Expr6,
LM_CRITERIA.LMC_CRI_WALD,
LM_CRITERIA.LMC_CRI_LOCAL, LM_CRITERIA.LMC_CRI_ACCT_GRP_CTRL,
LM_CRITERIA.LMC_CRI_RECYCLE_MOS, LM_TEXT_Crosstab.[1],
LM_TEXT_Crosstab.[2],
LM_TEXT_Crosstab.[3], LM_TEXT_Crosstab.[4], LM_TEXT_Crosstab.[5],
LM_TEXT_Crosstab.[6],
LM_TEXT_Crosstab.[7], LM_TEXT_Crosstab.[8], LM_TEXT_Crosstab.[9],
LM_TEXT_Crosstab.[10],
LM_TEXT_Crosstab.[11], LM_TEXT_Crosstab.[12], LM_TEXT_Crosstab.[13],
LM_TEXT_Crosstab.[14],
LM_TEXT_Crosstab.[15], LM_TEXT_Crosstab.[16],
LM_CRITERIA.LMC_CRI_WTEN_MIN,
LM_CRITERIA.LMC_CRI_WTEN_MAX
FROM LM_CRITERIA LEFT JOIN LM_TEXT_Crosstab ON LM_CRITERIA.LMC_MSG_NO
=
LM_TEXT_Crosstab.LMC_MSG_NO;
It gives me a "you tried to assign the null value to a variable that
is not a variant data type" error.
The macro stops with a 2950 error
Click to expand...
Click to expand...

Tried to insert one record and it did not give the error but the the
data was not there
Click to expand...

- Is there a viewer or identifier that will let me know where the
issue is seems most likely the crosstab (not sure)
 
I still can't tell which of your SQL statement items represents your
variables.

Where are you using this SQL ... in a query or in code? Is Option Explicit
turned on for your code modules? Are all variables used in your SQL
statement "Dim"d before use? Are any of those "Dim"d as Variant?

More info, please...

Regards

Jeff Boyce
Microsoft Access MVP

The implication is that (at least one of) your variable is not typed as
a
variant but that your SQL statement is attempting to stuff a "Null" into
it.
Which of those are variables?

Jeff Boyce
Microsoft Access MVP
Click to expand...
news:05c3d980-75c4-422f-a278-6a749b5d8149@f10g2000vbf.googlegroups.com...
Click to expand...
INSERT INTO WINB_LOYMESSAGES_VER ( LM_MSG_CNT, LM_MSG_NUMBER,
LM_MSG_TITLE,
LM_MSG_STATUS, LM_MSG_PRIORITY, LM_MSG_ADDED, LM_MSG_UPDATED,
LM_CRI_CVA_MIN,
LM_CRI_CVA_MAX, LM_CRI_CL_FS_MIN, LM_CRI_CL_FS_6MO, LM_CRI_AUTO_CL,
LM_CRI_TELEFRND_MIN,
LM_CRI_MTHS_SRV_MIN, LM_CRI_MTHS_SRV_MAX, LM_CRI_INTNL_MIN,
LM_CRI_INTNL_MAX,
LM_CRI_INT_GRP, LM_CRI_LEC_GRP, LM_CRI_STATE_GRP, LM_CRI_PRI_OCP_GRP,
LM_CRI_INTL_OCP_GRP, LM_CRI_CC_OCP_GRP, LM_CRI_FAN, LM_CRI_INTERNET,
LM_CRI_WACC,
LM_CRI_PHONE_ME, LM_CRI_TOLLFREE, LM_CRI_ZIP_GRP, LM_CRI_MIN_ANIS,
LM_CRI_MAX_ANIS,
LM_CRI_WIRELESS, LM_CRI_PREV_WIRELESS, LM_CRI_INTRALATA,
LM_CRI_INTRA_PROFIT,
LM_CRI_AUTO_ROUNDUP, LM_CRI_RU_12MO_MIN, LM_CRI_ACCT_GRP,
LM_CRI_PRIV_OPT,
LM_CRI_CSI__1, LM_CRI_CSI__2, LM_CRI_CSI__3, LM_CRI_CR_CLS__1,
LM_CRI_CR_CLS__2,
LM_CRI_CR_CLS__3, LM_CRI_CR_CLS__4, LM_CRI_CR_CLS__5,
LM_CRI_CR_CLS__6, LM_CRI_WALD,
LM_CRI_LOCAL, LM_CRI_ACCT_GRP_CTL, LM_CRI_RECYCLE_MOS, LM_MSG_TEXT__1,
LM_MSG_TEXT__2,
LM_MSG_TEXT__3, LM_MSG_TEXT__4, LM_MSG_TEXT__5, LM_MSG_TEXT__6,
LM_MSG_TEXT__7,
LM_MSG_TEXT__8, LM_MSG_TEXT__9, LM_MSG_TEXT__10, LM_MSG_TEXT__11,
LM_MSG_TEXT__12,
LM_MSG_TEXT__13, LM_MSG_TEXT__14, LM_MSG_TEXT__15, LM_MSG_TEXT__16,
LM_CRI_WAW_DAYS_MIN, LM_CRI_WAW_DAYS_MAX )
SELECT LM_CRITERIA.LMC_MSG_CNT, LM_CRITERIA.LMC_MSG_NO,
LM_CRITERIA.LMC_MSG_TITLE,
LM_CRITERIA.LMC_MSG_STATUS, LM_CRITERIA.LMC_MSG_PRIORITY,
Format(LM_CRITERIA.LMC_MSG_DATE_ADDED,"yyyymmdd") AS DateAdded,
Format(LM_CRITERIA.LMC_MSG_LAST_UPDATED,"yyyymmdd") AS LastUpdated,
LM_CRITERIA.LMC_CRI_CVA_MIN, LM_CRITERIA.LMC_CRI_CVA_MAX,
LM_CRITERIA.LMC_CRI_HIST_CL_FS_MIN, LM_CRITERIA.LMC_CRI_6MO_CL_FS,
LM_CRITERIA.LMC_CRI_AUTO_LTRS, LM_CRITERIA.LMC_CRI_TELEFRND_MIN,
LM_CRITERIA.LMC_CRI_MTHS_ON_SERVICE_MIN,
LM_CRITERIA.LMC_CRI_MTHS_ON_SERVICE_MAX,
LM_CRITERIA.LMC_CRI_6MO_AVG_INTL_USE_MIN,
LM_CRITERIA.LMC_CRI_6MO_AVG_INTL_USE_MAX,
LM_CRITERIA.LMC_CRI_INT_CODE_GRP, LM_CRITERIA.LMC_CRI_LEC_GRP,
LM_CRITERIA.LMC_CRI_STATE_GRP, LM_CRITERIA.LMC_CRI_PRI_OCP_GRP,
LM_CRITERIA.LMC_CRI_INTL_OCP_GRP, LM_CRITERIA.LMC_CRI_CC_OCP_GRP,
LM_CRITERIA.LMC_CRI_FAN, LM_CRITERIA.LMC_CRI_INTERNET,
LM_CRITERIA.LMC_CRI_WACC,
LM_CRITERIA.LMC_CRI_PHONEME, LM_CRITERIA.LMC_CRI_TOLLFREE,
LM_CRITERIA.LMC_ZIP_CODE_GRP,
LM_CRITERIA.LMC_MIN_ANIS, LM_CRITERIA.LMC_MAX_ANIS,
LM_CRITERIA.LMC_CRI_WIRELESS,
LM_CRITERIA.LMC_CRI_PREV_WIRELESS, LM_CRITERIA.LMC_CRI_INTRALATA,
LM_CRITERIA.LMC_CRI_INTRA_PROFIT, LM_CRITERIA.LMC_CRI_AUTO_ROUNDUP,
LM_CRITERIA.LMC_CRI_RU_12MO_MIN, LM_CRITERIA.LMC_CRI_ACCT_GRP,
LM_CRITERIA.LMC_CRI_PRIV_OPT, IIf([LMC_CRI_CSI_01]='-1','01',' ') AS
Expr7, IIf([LMC_CRI_CSI_02]='-
1','02',' ') AS Expr8, IIf([LMC_CRI_CSI_03]='-1','03',' ') AS Expr9,
IIf([LMC_CRI_CR_CLS_A]='-1','A',' ') AS
Expr1, IIf([LMC_CRI_CR_CLS_B]='-1','B',' ') AS Expr2, IIf
([LMC_CRI_CR_CLS_C]='-1','C',' ') AS Expr3,
IIf([LMC_CRI_CR_CLS_D]='-1','D',' ') AS Expr4, IIf([LMC_CRI_CR_CLS_P]
='-1','P',' ') AS Expr5,
IIf([LMC_CRI_CR_CLS_X]='-1','X',' ') AS Expr6,
LM_CRITERIA.LMC_CRI_WALD,
LM_CRITERIA.LMC_CRI_LOCAL, LM_CRITERIA.LMC_CRI_ACCT_GRP_CTRL,
LM_CRITERIA.LMC_CRI_RECYCLE_MOS, LM_TEXT_Crosstab.[1],
LM_TEXT_Crosstab.[2],
LM_TEXT_Crosstab.[3], LM_TEXT_Crosstab.[4], LM_TEXT_Crosstab.[5],
LM_TEXT_Crosstab.[6],
LM_TEXT_Crosstab.[7], LM_TEXT_Crosstab.[8], LM_TEXT_Crosstab.[9],
LM_TEXT_Crosstab.[10],
LM_TEXT_Crosstab.[11], LM_TEXT_Crosstab.[12], LM_TEXT_Crosstab.[13],
LM_TEXT_Crosstab.[14],
LM_TEXT_Crosstab.[15], LM_TEXT_Crosstab.[16],
LM_CRITERIA.LMC_CRI_WTEN_MIN,
LM_CRITERIA.LMC_CRI_WTEN_MAX
FROM LM_CRITERIA LEFT JOIN LM_TEXT_Crosstab ON LM_CRITERIA.LMC_MSG_NO
=
LM_TEXT_Crosstab.LMC_MSG_NO;
It gives me a "you tried to assign the null value to a variable that
is not a variant data type" error.
The macro stops with a 2950 error
Click to expand...
Click to expand...

Tried to insert one record and it did not give the error but the the
data was not there
Click to expand...

- Is there a viewer or identifier that will let me know where the
issue is seems most likely the crosstab (not sure)
 
Back
Top