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

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
 
J

Jeff Boyce

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

Jaycee

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
 
J

Jaycee

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)
 
J

Jeff Boyce

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)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

"You tried to assign the Null value to a variable that is not a... 3
You tried to assign the Null value to a variable that is not a Var 2
You tried to assign the Null value to a variable that is not a Variant Data Type 2
Getting "you tried to assign the null value to a variable that is not a variant data type" error wit 11
MySQL "You tried to assign the Null value to a variable that is not a Variant data type. (Error 3162 1
ERROR - You tried to assign the NULL value to a variabe that is not a Variant data type 1

Top