Link to home
Start Free TrialLog in
Avatar of zimmer9
zimmer9Flag for United States of America

asked on

In Access 2003, how would you handle placing a NULL value into any SQL Server table field if any input date fields has no value?

I am developing an Access 2003 VBA application. I use the following routine to import a txt file into a SQL Server table.

How would you modify the following code to place a NULL value into any SQL Server table date field if one of the input date fields is null?

If isFileExist("U:\Gim2_extract.txt.csv") Then
       Kill "U:\Gim2_extract.txt.csv"
   End If
   Set xlObj = CreateObject("excel.application")
    'xlObj.Workbooks.Open ("\\v\region\na\appl\ctrls\reg\data\etl\GWMUDL\prod\SrcFiles\psrpt_gwm_udl.out")
    xlObj.Workbooks.Open ("U:\Gim2_extract.txt")
    xlObj.ActiveWorkbook.SaveAs "U:\Gim2_extract.txt.csv", FileFormat:=6, CreateBackup:=False
    xlObj.ActiveWorkbook.Saved = True
   Set xlObj = Nothing

rs.Open "tblPWMGIM2_extract", CurrentProject.Connection, adOpenKeyset, adLockOptimistic
Ctr = 0
Open "U:\Gim2_extract.txt.csv" For Input As #1
Do While Not EOF(1)
Line Input #1, s
If Len(s) > 0 Then
    s = Replace(s, Chr(34), "")
    vArr = Split(s, "|")
    With rs
        !CL_ID = Trim(vArr(0))
        !C_TAPS_ACCOUNT = Trim(vArr(1))
        !C_LNAME = Trim(vArr(2))
        !C_NAME_ADDR_TYPE = Trim(vArr(3))
        !C_ADDRESS1 = Trim(vArr(4))
        !C_ADDRESS2 = Trim(vArr(5))
        !C_ADDRESS3 = Trim(vArr(6))
        !C_ADDRESS4 = Trim(vArr(7))
        !City = Trim(vArr(8))
        !State = Trim(vArr(9))
        !Zip = Trim(vArr(10))
        !Country = Trim(vArr(11))
        !B_BNAME = Trim(vArr(12))
        !B_WHO = Trim(vArr(13))
        !B_BROKER_ID = Trim(vArr(14))
        !C_ON_CLIENT_LINK = Trim(vArr(15))
        !C_TAX_ID = Trim(vArr(16))
        !C_OP_DATE = Trim(vArr(17))
        !C_TERM_DATE = Trim(vArr(18))
        !C_MARKET_VALUE = Trim(vArr(19))
        !C_MAX_ACTIVITY_DATE = Trim(vArr(20))
        !C_CHANGE_OF_ADDR = Trim(vArr(21))
        !C_ADD_COUNTRY = Trim(vArr(22))
    End With
    Ctr = Ctr + 1
End If


Close #1
Avatar of Bill Ross
Bill Ross
Flag of United States of America image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial