null in records from vba insert

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Below is my insert statement from a module of VBA code that I'm using to
create a spouse record in our qualification table. We have noticed and
verified that if the field INTERNAL_REMOVE_REASON does not have data some
nonprintable (we don't see any character in the column) character is being
inserted. We have verified this by querying QUALIFICATION for that field
where it is in not null. The variable (sINTERNAL_REMOVE_REASON) is being
initialized as such below before populating with data from the spouse record.
sINTERNAL_REMOVE_REASON = ""
Does anyone know why this inserts a nonprintable character into a field like
this?
I'm using this statement below to get any data from the spouse record -
sINTERNAL_REMOVE_REASON = IIf(IsNull(rst4(15)), "", rst4(15)).

sSQL = "INSERT INTO QUALIFICATION (ID, FAMILIAR_DONOR_NAME,GROUP_ID,
PRES_MGO_REP,ASSOCIATED_GIVING_ENTITIES, RANKING,CIRCLE_SOCIETY, END_REPS,
MAJOR_PROSPECT, PLANNED_GIVING, TRUSTEE, TRUSTEE_ON_LEAVE, TRUSTEE_EMERITUS,
PRESIDENT_EMERITUS, QUALIFICATION_PURPOSE, INTERNAL_REMOVE_REASON,
REMOVE_JAN_Z_AUGUST_REVIEW, REMOVE_FROM_LIST_2_KAYLA, REMOVE_FROM_LIST_JAN_Z,
REMOVE_FROM_LIST_1, MGO_FIRST_REVIEW, REMOVE_FROM_LIST_AUGUST_2006)" _
& "VALUES ('" & sSPOUSE_ID & "','" & sNEWFAMDONORNAME & "','" & sGROUPID
& "','" & sPRES_MGO_REP & "','" & sASSOCIATEDGIVINGENTITIES & "','" &
sRANKING & "','" & sCIRCLE_SOCIETY & "', '" & sEND_REPS & "', '" &
sMAJOR_PROSPECT & "', '" & sPLANNED_GIVING & "', '" & sTRUSTEE & "', '" &
sTRUSTEE_ON_LEAVE & "', '" & sTRUSTEE_EMERITUS & "', '" & sPRESIDENT_EMERITUS
& "', '" & sQUALIFICATION_PURPOSE & "', '" & sINTERNAL_REMOVE_REASON & "', '"
& sREMOVE_JAN_Z_AUGUST_REVIEW & "', '" & sREMOVE_FROM_LIST_2_KAYLA & "', '" &
sREMOVE_FROM_LIST_JAN_Z & "', '" & sREMOVE_FROM_LIST_1 & "', '" &
sMGO_FIRST_REVIEW & "', '" & sREMOVE_FROM_LIST_AUGUST_2006 & "')"
dbs.Execute sSQL

TIA,
 
If you are initializing the variable Like this:
sINTERNAL_REMOVE_REASON = ""

Then the "unprintable character" is an empty string. This is, it is the
same as vbNullString.
Now, don't let that name confuse you there is a difference between an empty
string and Null. When it is initialized to "", you will not see anything,
because there is nothing to see.
If you try IsNull(vbNullString) or IsNull(""), it will return False.
 
Back
Top