Issue with inserting nulls

[Rowen]

I'm not quite sure if this is the right section of the forum but I'm having some trouble inserting data into SQLServer from Access. I keep getting an 80040e14 error, Incorrect Syntax near ','.

My code is the following

rs1.Open "Select * from tblCaretakerComments", cnFrom, adOpenStatic, adLockOptimistic
While Not rs1.EOF
strInsertCommand = 
"Insert into dbo.tblCaretakerComments (Whattodo, Location, CommentDate, StairID, " & _
"Nextto, FlatNo, AdditionalNotes, SupervisorID, Floor) " & _
 "Values ('" & rs1("WhattoDo") & "', '" & rs1("Location") & "', Convert(datetime, '" & rs1("CommentDate") & "', 103) " & _
 ", " & rs1("StairID") & ", '" & rs1("Nextto") & "', " & rs1("FlatNo") & ", '" & rs1("AdditionalNotes") & "' " & _
 ", " & rs1("SupervisorID") & ", '" & rs1("Floor") & "')"
 
Debug.Print strInsertCommand
 
cnTo.Execute (strInsertCommand)
rs1.MoveNext
Wend
rs1.Close
Set rs1 = Nothing

Using a debug statement I think the issue is because the FlatNo column doesn't always have a value in it, and so there is a , , section in the code because this is a number column.

Insert into dbo.tblCaretakerComments (Whattodo, Location, CommentDate, StairID, Nextto, FlatNo, AdditionalNotes, SupervisorID, Floor) 
Values ('', 'blah', Convert(datetime, '11/05/2009', 103) , 4, '', 1, '' , 1, '')
 
Insert into dbo.tblCaretakerComments (Whattodo, Location, CommentDate, StairID, Nextto, FlatNo, AdditionalNotes, SupervisorID, Floor) 
Values ('', 'extra', Convert(datetime, '11/05/2009', 103) , 4, '', , '' , 1, '')

So my question is how do I do an insert if I have a null value in a number column? Secondly some of the text fields will have a null value as well, how do I insert these as a null? As I think the '' changes a null to a value of some kind?

 

[DCrake]

Try wrapping Nz() around the intended value to convert a null value into 0, where 0 means no flat number.

David

 

[Rowen]

I've tried putting the NZ() function in two ways, the first like

" & NZ(rs1("FlatNo")) & "

which gives me the same error I was getting, the second

NZ(" & rs1("FlatNo") & ")

I get the error that 'NZ' is not a recognised built in function name.

Am I putting the NZ in the right place?

 

[DCrake]

The correct syntax is

Nz(Rs("Flatno"),0)

Q: is the flat number a string or a number if latter you need to put it in quotes.

David

 

[Rowen]

It's ok, I figured out that it needed a 0, probably just as you were posting. I think it's still a bit too early in the morning. Thanks for the help.