VB Error when inserting NULL values in loop (1 Viewer)

[Georgemel]

Hi everyone I have a problem with my VB code not running when inserting triple state button value containing NULL values in the script bellow:
The check boxes are all triple state with default value of NULL but can contain a yes or no when required. This loop work fine when the value is yes or no but NULL results in an error. Is there any way for me to get NULL values inserted in my form without the error.
The error I get is Run-time error '3134' Syntax error in INSERT INTO statement.

Dim intweek As Integer
Dim dte As Date
Dim dteLOOP As Date
If Me.Combo59.ListIndex = -1 Then
ElseIf Nz(Me!ID, 0) = 0 Then
MsgBox "Cannot generate date when there is no record in main form"
Exit Sub
Else
intweek = Val(Trim(Replace(Me.Combo59, "Week", ""))) - 1
Me.txtWeek = intweek
If DCount("1", "[Loco Checklist]", "WeekNo=" & intweek & " And ID=" & Me.ID & " And [Loco Number]=" & Chr(34) & Me![Equipment No] & Chr(34)) > 0 Then
'do nothing, records for this week already been generated
Else
'get the first sunday for the week number
dte = WeekFirstDay(DateAdd("ww", intweek, DateSerial(Year(Now()), 1, 1)), vbSunday)
For dteLOOP = dte To dte + 7
CurrentDb.Execute "Insert Into [Winch] (" & _
"[Tip Barricade],[Safety Belt],[Grizzley],[Hammer and Hook],[ID], [Equipment],[DateS], [WeekNo]) " & _
"select " & _
Me![Check84] & "," & _
Me![Check84] & "," & _
Me![Check84] & "," & _
Me![Check84] & "," & _
Me!ID & "," & _
Chr(34) & Me![Text54].Value & Chr(34) & "," & _
"#" & Format(dteLOOP, "mm/dd/yyyy") & "#," & _
intweek
Next
Me![Winch].Form.Requery

End If
End If

 

[vba_php]

george,

I know this is not a useless post, so here's what you can do first.....I'm sure the experts will answer you at some point, but maybe start here?

take a look at the 2nd listing here: https://www.google.com/search?q=ms+access+vba+concat+null+in+sql. right now you are concatenating Me![Check84] into the sql string just like one would for an integer or long value. so I guess a boolean requires the same method, because you say the script works when the box is not null. to check on this property specifically, I looked at MS's page on it: https://docs.microsoft.com/en-us/office/vba/api/access.checkbox.triplestate, but didn't get anything there really. the guy that answers the question asker in that 2nd listing link I pointed to you above (the UtterAccess one), suggests using NZ() to solve the problem. maybe try that? he also tells them to consider the function:

isnull()

and even though his words deal with form controls through the interface, his solution will work for you too, because both of his functions he's talking about are available in vba as well. I would assume one of these would work in your situation too.

 

[Gasman]

Build the INSERT string into a variable and then Debug.Print that variable.
That should show you what is wrong.? If the value is Null, I would have thought you would have to skip those as the fields will have NULL when the record is created?

 

[GinaWhipp]

Hmm, just build your INSERT string to exclude NULL.

Side note, I would give your controls meaningful names so next month when you need to change something you know which control you are referring to and don't have to go find Check84 and Text54.