Insert Into - Select Statements - SQL HELP! NULL (1 Viewer)

[Shaunk23]

I am encountering an error when trying to run the below... I get a "Invalid use of null" It appears if ANY of the fields are empty this wont work. This is one of a few like it that copies related records under a duplicate quote button... Is there a way in the below to allow null values and just insert nothing if blank??


If DCount("[QUOTEID]", "Subquote", "QUOTEID=" & IdToCopy & "") > 0 Then

SubQuoteSQL = "INSERT INTO [SubQuote]" & _
" ( QuoteID , TYPEOFQUOTE , Quantity," & _
" CargoType,CargoDescription,QuoteLen, QuoteWid, QuoteHgt, QuoteWgt, " & _
" SubNotes,Datecreated, QuoteCuft,Exclude)" & _
" SELECT " & LongNewID & " As QuoteID, TYPEOFQUOTE, Quantity, CargoType, CargoDescription, " & _
" QuoteLen, QuoteWid, QuoteHgt, QuoteWgt, SubNotes," & "#" & TodayDate & "#" & " as Datecreated, QuoteCuft,Exclude" & _
" FROM SubQuote " & _
" WHERE (QuoteID = " & IdToCopy & ");"

'Debug.Print SubQuoteSQL
DB.Execute (SubQuoteSQL), dbFailOnError
Else
'Do Nothing
End If

 

[michaeljryan78]

You may want to wrap the vaues that may contain nulls using the NZ function. NZ(YOURFIELD,"")

 

[Shaunk23]

Hi Michael - I tried that. You would only put that in the "SELECT" Part of the statement correct? Not the insert section?

 

[michaeljryan78]

No, I would try the insert, it tests for null, then places the ""

 

[Shaunk23]

Nope - Now getting SYNTAX in INSERT INTO

' Checks to ensure the related records exist for subquote
If DCount("[QUOTEID]", "Subquote", "QUOTEID=" & IdToCopy & "") > 0 Then

SubQuoteSQL = "INSERT INTO [SubQuote]" & _
" ( QuoteID , TYPEOFQUOTE , Nz(Quantity,"")," & _
" Nz(CargoType,""),Nz(CargoDescription,""),Nz(QuoteLen,""), Nz(QuoteWid,""), Nz(QuoteHgt,""), Nz(QuoteWgt,""), " & _
" Nz(SubNotes,""),Datecreated, Nz(QuoteCuft,""),Nz(Exclude,""))" & _
" SELECT " & LongNewID & " As QuoteID, TYPEOFQUOTE, Quantity, CargoType, CargoDescription, " & _
" QuoteLen, QuoteWid, QuoteHgt, QuoteWgt, SubNotes, " & "#" & TodayDate & "#" & " as Datecreated,QuoteCuft,Exclude" & _
" FROM SubQuote " & _
" WHERE (QuoteID = " & IdToCopy & ");"

'Debug.Print SubQuoteSQL
DB.Execute (SubQuoteSQL), dbFailOnError
Else
'Do Nothing
End If

 

[Shaunk23]

Please assist on this!!!

' Checks to ensure the related records exist for subquote
If DCount("[QUOTEID]", "Subquote", "QUOTEID=" & IdToCopy & "") > 0 Then

SubQuoteSQL = "INSERT INTO [SubQuote]" & _
" ( QuoteID , TYPEOFQUOTE , Nz(Quantity,"")," & _
" Nz(CargoType,""),Nz(CargoDescription,""),Nz(QuoteLen,""), Nz(QuoteWid,""), Nz(QuoteHgt,""), Nz(QuoteWgt,""), " & _
" Nz(SubNotes,""),Datecreated, Nz(QuoteCuft,""),Exclude)" & _
" SELECT " & LongNewID & " As QuoteID, TYPEOFQUOTE, Quantity, CargoType, CargoDescription, " & _
" QuoteLen, QuoteWid, QuoteHgt, QuoteWgt, SubNotes, " & "#" & TodayDate & "#" & " as Datecreated,QuoteCuft,Exclude" & _
" FROM SubQuote " & _
" WHERE (QuoteID = " & IdToCopy & ");"

'Debug.Print SubQuoteSQL
DB.Execute (SubQuoteSQL), dbFailOnError
Else
'Do Nothing
End If



Not everyshipment will have length with and hiegh.. getting null error. How do i allow null on this.