Zero length field

[Bryan Hughes]

Hello,

I have a temp table that is created from code to use for printing a report.
It works great except for a field used for middle inital. If a middle
initial is not entered into the field a Zero Length error pops up. How can
I make this fiel allow zero length from code?

Here is the code I use to create the table

Dim blnTest As Boolean

strTableName = "tblUSys_Case_File_Main_Rpt_Temp"

blnTest = fExistTable(strTableName)
If blnTest = True Then
CurrentDb.TableDefs.Delete strTableName
End If

CurrentDb.TableDefs.Refresh

strTempDatabase = Left(CurrentDb.Name, Len(CurrentDb.Name) - 4) &
"_temp.mdb"

Set dbsTemp = DBEngine.Workspaces(0).OpenDatabase(strTempDatabase)

' Create the temp table
Set tdfNew = dbsTemp.CreateTableDef(strTableName)
Set fld = tdfNew.CreateField("USCIDS", dbLong)
'Set field autonumber properities
fld.Required = True
fld.Attributes = dbAutoIncrField
tdfNew.Fields.Append fld

With tdfNew
.Fields.Append .CreateField("MFIDS", dbLong)
.Fields.Append .CreateField("CFID", dbText, 255)
.Fields.Append .CreateField("CFAD", dbDate)
.Fields.Append .CreateField("FID", dbLong)
.Fields.Append .CreateField("CF_FN", dbText, 150)
.Fields.Append .CreateField("CF_LN", dbText, 150)
.Fields.Append .CreateField("CF_MI", dbText, 150) 'Middle Inital
.Fields.Append .CreateField("CF_DOB", dbDate)
.Fields.Append .CreateField("CF_IOD", dbDate)
.Fields.Append .CreateField("IDSAC", dbLong)
.Fields.Append .CreateField("AC_FN", dbText, 150)
.Fields.Append .CreateField("AC_LN", dbText, 150)
.Fields.Append .CreateField("AC_SA", dbText, 255)
.Fields.Append .CreateField("AC_MA", dbText, 255)
.Fields.Append .CreateField("AC_City", dbText, 150)
.Fields.Append .CreateField("AC_State", dbText, 10)
.Fields.Append .CreateField("AC_ZipCode", dbText, 25)
.Fields.Append .CreateField("AC_PH1", dbText, 25)
.Fields.Append .CreateField("AC_PH2", dbText, 25)
.Fields.Append .CreateField("AC_OCM", dbText, 50)
.Fields.Append .CreateField("AC_OCMID", dbInteger)
.Fields.Append .CreateField("AC_OCMT", dbText, 100)
.Fields.Append .CreateField("IDSPC", dbLong)
.Fields.Append .CreateField("PC_FN", dbText, 150)
.Fields.Append .CreateField("PC_LN", dbText, 150)
.Fields.Append .CreateField("PC_SA", dbText, 255)
.Fields.Append .CreateField("PC_MA", dbText, 255)
.Fields.Append .CreateField("PC_City", dbText, 150)
.Fields.Append .CreateField("PC_State", dbText, 10)
.Fields.Append .CreateField("PC_ZipCode", dbText, 25)
.Fields.Append .CreateField("PC_PH1", dbText, 25)
.Fields.Append .CreateField("PC_PH2", dbText, 25)
.Fields.Append .CreateField("PC_OCM", dbText, 50)
.Fields.Append .CreateField("PC_OCMID", dbInteger)
.Fields.Append .CreateField("PC_OCMT", dbText, 100)
.Fields.Append .CreateField("SI", dbInteger)
.Fields.Append .CreateField("SIT", dbText, 100)
.Fields.Append .CreateField("FMS", dbInteger)
.Fields.Append .CreateField("IL", dbInteger)
.Fields.Append .CreateField("EAHI", dbCurrency)
.Fields.Append .CreateField("ECIDS", dbLong)
.Fields.Append .CreateField("EC_FN", dbText, 150)
.Fields.Append .CreateField("EC_LN", dbText, 150)
.Fields.Append .CreateField("EC_PH1", dbText, 25)
.Fields.Append .CreateField("EC_Relationship", dbText, 150)
dbsTemp.TableDefs.Append tdfNew
End With

dbsTemp.TableDefs.Refresh

' Link to the Import tables in the temp MDB
Set tdfLinked = CurrentDb.CreateTableDef(strTableName)
tdfLinked.Connect = ";DATABASE=" & strTempDatabase
tdfLinked.SourceTableName = strTableName
CurrentDb.TableDefs.Append tdfLinked



CurrentDb.TableDefs.Refresh

RefreshDatabaseWindow
dbsTemp.Close
Set fld = Nothing
Set tdfNew = Nothing
Set tdfLinked = Nothing
Set dbsTemp = Nothing

 

[Jeff Boyce]

Bryan

Is your situation not amenable to using a query as a report source, rather
than a temp table?

Jeff Boyce
<Access MVP>