insert null into date fields

[si_the_geek]

What code are you currently using?

What is the value of the apt variables (eg: QuoteReq_char) when it runs, and what is their data type?

[holly]

The code is MSAccess 97 frontend and the backend is sqlserver2000.

the 'QuoteReq_char' variable is a string in the rountine and a datetime in sql.

Thanks

[FunkyDexter]

I believe the problem is that a null in your code equates to blank, but that's not the same as a null in sql server. This:-

CONVERT(datetime,'', 103)

will get converted to the base date by sql server and that's 01/01/1900. Instead of passing in a blank you want to pass in an actual null. Change this:-

SQL = SQL & ", '" & Null & "'"

to this:-

SQL = SQL & ", Null"

That way SQL server will be process a null rather than a blank and will insert a null into the field. Do that for any other bit of code that's trying to put a null into the field too.

I am concerned that this:-

SQL = SQL & ", '" & Null & "'"

should give you a string of:-

,''

but you seem to be saying it's actually giving you:-

CONVERT(datetime,'', 103)

and I cant see where the Convert is coming from. Am I missing something?

[holly]

Hi FunkyDexter,
thanks for replying..I have not tried you solution yet but I thought it may
be beneficial if I posted the whole routine...

Private Function CreateQMSRecord(rs As ADODB.Recordset) As Boolean
    Dim rsSet As ADODB.Recordset
    Dim rsScheme As ADODB.Recordset
    Dim rsRisk As ADODB.Recordset
    Dim RiskID_int As Long
    Dim CustID_int As Long
    Dim lngCountKey As Long
    Dim SQL_str As String
    Dim DevExec As String, BName As String
    Dim RenewalDate As String
    Dim ReceivedDate As String
    Dim Logged_datetime As String
    Dim ProductCode_char As String
    Dim Carrier_char As String
    Dim BrkComm_per As Integer
    Dim BDComm_per As Integer
    Dim NewQHandler As String
    Dim QmsQuoteID As Long
    Dim Feedback_char As String
    Dim Feedback_note As String
    Dim QuoteReq_char As String
    Dim TargetPrem_money As Single
    Dim TargetInsurer_char As String
    Dim ToCarrier_char As String
    Dim CarrierQuote_char As String
    Dim ToBroker_char As String
    Dim GrossIncIPT_money As Single
    Dim InsurerPlaced_char As String
    Dim ProductType_char As String
    'Dim Carrier_char As String
    Dim Source_char As String
    Dim Currency_char As String
    Dim PremPlaced_money As Single
    'Dim BDEQuotes()
    Dim RSNewQuoteID As ADODB.Recordset
    
    Set RSNewQuoteID = New ADODB.Recordset
    SQL_str = "SELECT Max([QMSQuoteID_int]) AS maxlogID from tabqms_quotes"
    RSNewQuoteID.Open SQL_str, gBass, adOpenDynamic, adLockPessimistic
    If Not RSNewQuoteID.BOF Then
        QmsQuoteID = RSNewQuoteID!maxlogID + 1
        Set RSNewQuoteID = Nothing
    End If
    
    'NewQHandler = rs!CompletedBy_Varchar
    Logged_datetime = rs!Logged_datetime
    
    Set rsSet = New ADODB.Recordset 'create new object
    SQL = "Insert into tabQMS_Quotes(AC_No, Status, Contact_No, Risk_No, RiskName_char, Renewal_date, Recieved_date, "
    SQL = SQL & "QuoteReference_char, DevExec_Char, ProductCode_char, ProductType_char, BrkComm_per, BDComm_per, "
    SQL = SQL & "QuotedPremium_cur, Currency_char, InceptionDate_date, PolicyEndDate_date, PolicyNo_char, Handler_char, "
    SQL = SQL & "QuoteHandler_Char, Terms_Note, RiskTransfer_char, creditTerms_char, Handling_Office, "
    SQL = SQL & "QuoteLogID_int, QABQuoteID_int, QMSQuoteID_Int, Live_Bit, LastLoggedDateTime_Date, FeedbackStatus_char,"
    SQL = SQL & " QuoteReq_char, Feedback_note, TargetPremium_cur, TargetInsurer_char, "
    SQL = SQL & " InsurerPlaced_char, Carrier_char, CarrierSource_char, PremiumPlaced_cur, TransactionType_Char)"
            
    If Len("" & rs!ac_no_varchar) > 0 Then
        SQL_str = "SELECT BDA_Main.A_C_No, BDA_Main.Name, BDA_Main.CommlExec FROM BDA_Main "
        SQL_str = SQL_str & " WHERE BDA_Main.A_C_No = " & rs!ac_no_varchar
        Set rsRisk = New ADODB.Recordset
        rsRisk.Open SQL_str, gLocal, adOpenStatic, adLockPessimistic
        
        If Not rsRisk.EOF Then
            If "" & rsRisk!CommlExec <> "" Then
            '    CheckBrokerDetails RSQMS5!A_C_NO
                DevExec = rsRisk!CommlExec
                BName = rsRisk!Name
            End If
        End If
    End If
    
    'check whether quoteid = tab_qms_riskdetails
    RiskID_int = CheckRiskDetails(rs!InsuredLastName_varchar, rs!QuoteID_int)
    CustID_int = CheckBrokerDetails(vbNullString & rs!ac_no_varchar)
    
    
    SQL = SQL & " VALUES ("
    SQL = SQL & "" & Val("" & gAccNo_str) & ""
    SQL = SQL & ",'" & rs!Status_varchar & "'"
    SQL = SQL & "," & CustID_int
    SQL = SQL & "," & RiskID_int
    SQL = SQL & ",'" & MakeStrSQLAcceptable(rs!InsuredLastName_varchar) & "'"
            
    RenewalDate = Format(rs!PolicyStartDate_DateTime, "dd mmmm yyyy")
    ReceivedDate = Format(Logged_datetime, "dd mmmm yyyy")
    
    SQL = SQL & ", CONVERT(datetime,'" & RenewalDate & "', 103)"
    SQL = SQL & ", CONVERT(datetime,'" & ReceivedDate & "', 103)"
    If IsNumeric(rs!PolID_varchar) Then
        SQL = SQL & ",'" & IIf("" & GetQuoteReferenceFromPrev(rs!QuoteID_int) <> "", GetQuoteReferenceFromPrev(rs!QuoteID_int), rs!PolID_varchar) & "'"
    Else
        SQL = SQL & ",'" & rs!PolID_varchar & "'"
    End If
    SQL = SQL & ", '" & DevExec & "'"
    
    'code for getting from Tony's tables when they reflect properly
    'SQL = "SELECT DISTINCT dbo.Tonyg_SchemeShop_Products.ProductPrefix_varchar, dbo.Tonyg_SchemeShop_Products.CommissionAmount_Decimal,"
    'SQL SQL & " dbo.Tonyg_SchemeShop_Products.BrokerDirectCommissionAmount_Decimal, dbo.Tonyg_SchemeShop_Products.ProductID_int,"
    'SQL = SQL & " dbo.Tonyg_SchemeShop_Products.CommissionType_varchar FROM dbo.Tonyg_SchemeShop_LogQuoteCalcs INNER JOIN"
    'SQL = SQL & " dbo.Tonyg_SchemeShop_Products ON dbo.Tonyg_SchemeShop_LogQuoteCalcs.ProductID_int = dbo.Tonyg_SchemeShop_Products.ProductID_int"
    'SQL = SQL & " WHERE (dbo.Tonyg_SchemeShop_LogQuoteCalcs.QuoteID_int = " & RS!QuoteID_int & ")"
    
    'For now - use the tabQMS_DefaultProductComms table
    SQL_str = "SELECT * FROM tabQMS_DefaultProductComms WHERE SchemeID = " & rs!SchemeID_int
    Set rsScheme = New ADODB.Recordset
    rsScheme.Open SQL_str, gBass, adOpenStatic, adLockOptimistic
    
    If Not rsScheme.BOF Then
        
        ProductCode_char = rsScheme!ProductCode_char
        ProductType_char = GetProductType(ProductCode_char)
        Carrier_char = rsScheme!Carrier_char
        If Val("" & rsScheme!BrokerComm_num) > 0 Then BrkComm_per = rsScheme!BrokerComm_num
        If Val("" & rsScheme!BDComm_num) > 0 Then BDComm_per = rsScheme!BDComm_num
    Else
        ProductType_char = ""
        ProductCode_char = ""
        Carrier_char = ""
        BrkComm_per = 0
        BDComm_per = 0
    End If
    
    SQL = SQL & ",'" & trim(ProductCode_char) & "'"
    SQL = SQL & ",'" & ProductType_char & "'"
    SQL = SQL & ",'" & BrkComm_per & "'"
    SQL = SQL & ",'" & BDComm_per & "'"
    SQL = SQL & "," & rs!GrossPremium_money
    SQL = SQL & ",'GBP'"
    
    SQL = SQL & ", CONVERT(datetime,'" & Format(rs!PolicyStartDate_DateTime, "dd/mm/yyyy hh:mm:ss") & "',103)"
    SQL = SQL & ", CONVERT(datetime,'" & Format(rs!PolicyEndDate_DateTime, "dd/mm/yyyy hh:mm:ss") & "', 103)"

    SQL = SQL & ",'" & rs!PolID_varchar & "'"
    SQL = SQL & ",'" & MakeStrSQLAcceptable(rs!CompletedBy_Varchar) & "'"
    SQL = SQL & ",'" & MakeStrSQLAcceptable(rs!CompletedBy_Varchar) & "'"
    SQL = SQL & ",'" & MakeStrSQLAcceptable(rs!UserNotes_varchar) & "'"
    SQL = SQL & ",'YES'"
    SQL = SQL & ",'30 Days'"
    SQL = SQL & ",'BD'"
    SQL = SQL & "," & rs!QuoteLogID_Int
    SQL = SQL & "," & rs!QuoteID_int
    SQL = SQL & "," & QmsQuoteID
    
    SQL = SQL & ",-1"
    SQL = SQL & ", CONVERT(datetime,'" & Format(Now, "dd/mm/yyyy hh:mm:ss") & "',103)"
    SQL = SQL & ",'" & MakeStrSQLAcceptable(Feedback_char) & "'"
    SQL = SQL & ",'" & MakeStrSQLAcceptable(Feedback_note) & "'"
    'SQL = SQL & ",'" & MakeStrSQLAcceptable(QuoteReq_char) & "'"
    If Val("" & QuoteReq_char) = 0 Then
          SQL = SQL & ", '" & Null & "'"
    Else
        SQL = SQL & ", CONVERT(datetime,'" & Format(QuoteReq_char, "MM-DD-YYYY") & "', 103)"
    End If
    SQL = SQL & "," & TargetPrem_money
    SQL = SQL & ",'" & MakeStrSQLAcceptable(TargetInsurer_char) & "'"
    If Val("" & ToCarrier_char) = 0 Then
        SQL = SQL & ", '" & Null & "'"
    Else
         SQL = SQL & ", CONVERT(datetime,'" & Format(ToCarrier_char, "dd mmmm yyyy") & "', 103)"
    End If
    'If CarrierQuote_char = "" Then
    '     SQL = SQL & "," & vbNull
    'Else
    'SQL = SQL & ", CONVERT(datetime,'" & Format(CarrierQuote_char, "dd mmmm yyyy") & "', 103)"
    'End If
    'If ToBroker_char = "" Then
    '     SQL = SQL & "," & vbNull
    'Else
   ' SQL = SQL & ", CONVERT(datetime,'" & Format(ToBroker_char, "dd mmmm yyyy") & "', 103)"
    'End If
    SQL = SQL & ",'" & MakeStrSQLAcceptable(InsurerPlaced_char) & "'"
    SQL = SQL & ",'" & MakeStrSQLAcceptable(Carrier_char) & "'"
    SQL = SQL & ",'" & MakeStrSQLAcceptable(Source_char) & "'"
    SQL = SQL & "," & PremPlaced_money
    SQL = SQL & ",'" & rs!TransactionType_varChar & "'"
    
    SQL = SQL & ") SELECT @@IDENTITY"
    
    Set rsSet = gBass.Execute(SQL)

Thanks for your help !!

[FunkyDexter]

Taking a quick look, I can't see anywhere where you give quoteReq_char a value so it will always be blank.

Assuming you are giving it a value, then changing this:-

If Val("" & QuoteReq_char) = 0 Then
          SQL = SQL & ", '" & Null & "'"    Else
        SQL = SQL & ", CONVERT(datetime,'" & Format(QuoteReq_char, "MM-DD-YYYY") & "', 103)"
    End If

to

If Val("" & QuoteReq_char) = 0 Then
          SQL = SQL & ", Null "
    Else
        SQL = SQL & ", CONVERT(datetime,'" & Format(QuoteReq_char, "MM-DD-YYYY") & "', 103)"
    End If

you'll also need to do something similar with ToCarrier_char. The point is you're building up a string. If you build a vb null (which is an empty string) into it you'll just get a blank so your string will look something like 'SomeVal', '', 'SomeOtherVal'. But if you pass "NULL" as a string then the string will read 'SomeVal', NULL, 'SomeOtherVal' and that's what sql server wants. Does that make sense?


I'm not familiar with this:-
If Val("" & QuoteReq_char) = 0
to check for an empty string but I think it should work. I'd suggest:-
If QuoteReq_char = ""
or
If len(QuoteReq_char) = 0
are more readable though.