Apostrophe and Null Values

[starclassic]

Hello everyone,

I have this SQL Statement:
'Add record to first table contact to print the First Contact Letter
DoCmd.RunSQL "INSERT INTO tblFirstContact (LName,Address,Address1,Address2,Address3,City,Province,PostalCode," _
& "Year,Make,Model,Vin,AFOCode,AgentCode,AgentName,PolicyNumber,Message,SystemCalculateAmountOwing)" _
& "Values ('" & FX(rst![LName]) & "', '" & FX(rst![Address]) & "', '" & rst![Address1] & "', '" & rst![Address2] & "'," _
& "'" & rst![Address3] & "', '" & rst![City] & "','" & rst![Province] & "', '" & rst![PostalCode] & "', " _
& "'" & rst![Year] & "','" & rst![Make] & "', '" & rst![Model] & "', '" & rst![Vin] & "','" & rst![AFOCode] & "'," _
& "'" & rst![AgentCode] & "','" & FX(rst![AgentName]) & "','" & rst![PolicyNumber] & "','" & LetterMessage & "'," _
& "'" & rst![SystemCalculateAmountOwing] & "')"



'Function to deal with (') apostrophe in the string
Public Function FX(strInput As String, Optional strQuoteChar As String = "'") As String
On Error GoTo ErrorHandler
Dim lsWork As String

FX = strInput

lsWork = strInput
If Len(lsWork) > 0 Then

Do While InStr(lsWork, strQuoteChar) > 0

lsWork = Replace(lsWork, strQuoteChar, Chr$(&HFF))
Loop

Do While InStr(lsWork, Chr$(&HFF)) > 0
lsWork = Replace(lsWork, Chr$(&HFF), strQuoteChar & strQuoteChar)

Loop
End If
FX = lsWork
FX_Done:
On Error Resume Next
Exit Function

ErrorHandler:
Dim lngErrNum As Long: Dim strErrDesc As String: lngErrNum = Err.Number:

strErrDesc = Err.Description
MsgBox "Err#" & Err.Number & vbCrLf & vbCrLf & "Desc: " & 30# & Err.Description, vbExclamation, "FixQuotes"

Resume FX_Done

Resume ' for debug
End Function


FX is a function to deal with (') Apostrophe and now my problems is that some of the Fieldname like [Address] values are Null. Gives a error invalid value "Null"
Any ideas of function to deal with null values.

 

[MajP]

NZ null to zero function
FX(NZ(rst![AgentName],""))

 

[TheAceMan1]

How are ya starclassic . . .

If your fields have apostrophe's in the data, the following should allow you to keep them:

[blue]   Dim SQL As String, DQ As String
   
   DQ = """"
   SQL = "INSERT INTO tblFirstContact (LName, " & _
                                      "Address, " & _
                                      "Address1, " & _
                                      "Address2, " & _
                                      "Address3, " & _
                                      "City, " & _
                                      "Province, " & _
                                      "PostalCode, " & _
                                      "Year, " & _
                                      "Make, " & _
                                      "Model, " & _
                                      "Vin, " & _
                                      "AFOCode, " & _
                                      "AgentCode, " & _
                                      "AgentName, " & _
                                      "PolicyNumber, " & _
                                      "Message, " & _
                                      "SystemCalculateAmountOwing) "
                                      
   SQL = SQL & "Values (" & DQ & rst![LName] & DQ & ", " & _
                            DQ & rst![Address] & DQ & ", " & _
                            DQ & rst![Address1] & DQ & ", " & _
                            DQ & rst![Address2] & DQ & ", " & _
                            DQ & rst![Address3] & DQ & ", " & _
                            DQ & rst![City] & DQ & ", " & _
                            DQ & rst![Province] & DQ & ", " & _
                            DQ & rst![PostalCode] & DQ & ", " & _
                            DQ & rst![Year] & DQ & ", " & _
                            DQ & rst![Make] & DQ & ", " & _
                            DQ & rst![Model] & DQ & ", " & _
                            DQ & rst![Vin] & DQ & ", " & _
                            DQ & rst![AFOCode] & DQ & ", " & _
                            DQ & rst![AgentCode] & DQ & ", " & _
                            DQ & rst![AgentName] & DQ & ", " & _
                            DQ & rst![PolicyNumber] & DQ & ", " & _
                            DQ & LetterMessage & DQ & ", " & _
                            DQ & rst![SystemCalculateAmountOwing] & DQ & ")"[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997