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
    xlObj.Quit
   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
        .AddNew
        !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))
        .Update
    End With
    Ctr = Ctr + 1
End If

Loop

Close #1
rs.Close
ASKER CERTIFIED SOLUTION
Avatar of Bill Ross
Bill Ross
Flag of United States of America image

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