INSERT into + null

Hi

I'm attempting to insert multiple records from one table into another
based on rows selected in a multiple list box. This all seems to be
working OK until I get to a field where the value is null. What seems
to happen then is the entire record isn't inserted into the new table
ie it fails. The field called [Initials] is 95% of the time empty but
occassionally has been filled in, so I think I need something in the
code that covers both eventualities. Grateful for any help as I've
trawled through the group (and others) and can't quite seem to crack
it. Below is the offending code!

thanks

David

Private Sub cmdAddMems_Click()

DoCmd.SetWarnings False


Dim lbl1ID As Variant


For Each lbl1ID In lstStudents.ItemsSelected


DoCmd.RunSQL "INSERT into tblGroupMembers ([Student ID], [Group
Code ID], [Surname], [First Name], [Class Year],[Initials])" _
& "VALUES (""" & lstStudents.Column(0, lbl1ID) _
& """,""" & [Group Code ID] _
& """, """ & lstStudents.Column(1, lbl1ID) _
& """, """ & lstStudents.Column(2, lbl1ID) _
& """, """ & lstStudents.Column(6, lbl1ID) _
& """, """ & lstStudents.Column(3, lbl1ID) & """)"

Next

DoCmd.SetWarnings True

[lstMembers].Requery


End Sub

Try:
Try using the IIF() function.

IIF(IsNull(Initials),"",Initials)

On 26 Mar 2007 13:52:20 -0700, "deejayquai" <deejayquai@gmail.com>
wrote:

It looks like you were short a field - there were 6 fields listed in the
Insert Into part, but I could only see 5 fields in the values list. I
added txtInitials as the 6th field. Try your code this way:

DoCmd.RunSQL "INSERT into tblGroupMembers ([Student ID], [Group Code
ID], [Surname], [First Name], [Class Year],[Initials])" _
& "Select " & lstStudents.Column(0) & ",'" & lstStudents.Column(1) & "',
'" & lstStudents.Column(2) & "', '" & lstStudents.Column(6) & "', " &
lstStudents.Column(3) & ", '" & txtInitials & "'"

I don't know your data types but in this example, I assume that
StudentID is a number so no single quote delimiters. Then I assume
ClassYear is also an integer so no single quote delimeters. I took
everything else to be text, which I delimit with single quotes.


Rich

*** Sent via Developersdex http://www.developersdex.com ***

On Mar 26, 1:52 pm, "deejayquai" <deejayq...@gmail.comwrote:DJ:

Take a look at the Nz function in Help. It converts Nulls to
alternate values for you. So, if you just want it to put an empty
string instead of a Null, you'd change the line that inserts into the
[Initials] field to this:

Nz(lstStudents.Column(3, lbl1ID),"")

HTH,
Jana