Date Time Field set to Null

[andybeanland]

Hello,

I've got a problem with a date field that I just can't resolve - hopefully you'll be able to help.

I have a series of text boxes (10 in total) which are for inserting start and end times for children attending a nursery for the different days of the week. The children don't attend every day so some of the text boxes will be left blank. How do I get Access to insert a null into a date time field?

This is the coding I have:

Dim MonS As Variant
Dim MonE As Variant

MonAtt = [Forms]!frm_Attending.Monday_Attending
MonS = [Forms]!frm_Attending.Monday_Start
MonE = [Forms]!frm_Attending.Monday_End

SQLMonday = "INSERT INTO tbl_Monday (Attending, StartTime, EndTime) VALUES ('" & MonAtt & "', '" & MonS & "', '" & MonE & "')"

DoCmd.RunSQL SQLMonday

The text boxes also have the input mask 00:00;0;_

When leaving an text box empty the error message I am getting is:

Access Can't Append All The Records In the Append Query.
Access Set 1 Field to Null due to a type conversion failure.

If the text boxes are populated the SQL is executed correctly and inserts the information into the tables.

Thanks for your help,

Andy

 

[PHV]

You may try this:
SQLMonday = "INSERT INTO tbl_Monday (Attending, StartTime, EndTime) VALUES ('" & MonAtt & "', " & IIf(IsDate(MonS), "#" & MonS & "#", "Null") & ", " & IIf(IsDate(MonE), "#" & MonE & "#", "Null" & ")"

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[andybeanland]

Thanks PH, but I just tried the code you pasted in and it just gives me a "Compile Error. Expected: List seperator or )"

I've checked the correct number of brackets etc, but wasn't really sure what this was doing so didn't know what to check...

 

[PHV]

Sorry for the typo:
SQLMonday = "INSERT INTO tbl_Monday (Attending, StartTime, EndTime) VALUES ('" & MonAtt & "', " & IIf(IsDate(MonS), "#" & MonS & "#", "Null") & ", " & IIf(IsDate(MonE), "#" & MonE & "#", "Null") & ")"

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[andybeanland]

Thanks, that works perfectly!