blank dates driving me batsh!t

[madEG]

Hello,

Ok, I had to have done this some time in the past... and yet I can't today, and I'm going to pull my hair out!

I have a form with a series of unbound text boxes where data is entered - including two non-required dates. If I use the date picker to choose a date, this works fine, but if I leave the date fields blank, I get a null conversion error.

        "', " & _
        Chr(35) & dtmStart & Chr(35) & _
        ", " & _
        Chr(35) & dtmEnd & Chr(35) & _
        ", '" & _

The above is the portion of the VALUES part of my INSERT statement... what do I need to do the values (or lack there of!) in the dtmStart and dtmEnd fields to have MS Access allow me to push back null/blank? I don't want to require these two fields.

The backend is sql server 2012 and the field's properties includes being datetime and set to allow null. I'm stumped.

What do I need to do to make this allow blank/null to be allowed?

The full query, if this helps:

Private Sub btnAddNewRecord_Click()
  If (Format(Trim(txtActivityName)) <> "") And (listboxGoalsObjectives.Value <> "") Then
        Dim strSQL As String

        strSQL = "INSERT INTO dbo_Activities (GoalObjectiveIDf, ActivityName, ActivityDesc, OrganizationalFunction, TopicalFocus, PlanningCycle, FundingSource, PeriodicDetail, KeyPerformanceIndicator, ProjectedCosts, ProjectedStaffHours, MetricType, StartDatetime, EndDatetime, PrimaryAudience) VALUES (" & _
        
[listboxGoalsObjectives].[Value] & ", '" & Replace(Format(Trim(txtActivityName)), "'", "''") & "', '" & Replace(Trim(Nz(txtActivityDesc, "")), "'", "''") & "', '" & _
        Replace(Format(Trim(txtOrgFunction)), "'", "''") & _
        "', '" & _
        Replace(Format(Trim(txtTopicalFocus)), "'", "''") & _
        "', '" & _
        Replace(Format(Trim(cboPlanningCycle)), "'", "''") & _
        "', '" & _
        Replace(Format(Trim(txtFundingSource)), "'", "''") & _
        "', '" & _
        Replace(Format(Trim(cboPeriodicDetail)), "'", "''") & _
        "', '" & _
        Replace(Format(Trim(txtKeyPerformanceIndicator)), "'", "''") & _
        "', '" & _
        Replace(Format(Trim(numProjectedCosts)), "'", "''") & _
        "', '" & _
        Replace(Format(Trim(numProjectedStaffHours)), "'", "''") & _
        "', '" & _
        Replace(Format(Trim(txtMetricType)), "'", "''") & _
        "', " & _
        Chr(35) & dtmStart & Chr(35) & _
        ", " & _
        Chr(35) & dtmEnd & Chr(35) & _
        ", '" & _
        Replace(Format(Trim(txtPrimaryAudience)), "'", "''") & "');"

        MsgBox ("strSQL: " & strSQL)
        'DoCmd.SetWarnings False
        DoCmd.RunSQL strSQL
        'DoCmd.SetWarnings True

        txtActivityName = ""
        txtActivityDesc = ""
        txtOrgFunction = ""
        txtTopicalFocus = ""
        cboPlanningCycle = ""
        txtFundingSource = ""
        cboPeriodicDetail = ""
        txtKeyPerformanceIndicator = ""
        numProjectedCosts = ""
        numProjectedStaffHours = ""
        txtMetricType = ""
        dtmStart = ""
        dtmEnd = ""
        txtPrimaryAudience = ""

        
        listboxActivities.Requery
    Else
        MsgBox ("Select an Objective, then Enter Activities info before pressing ADD.")
    End If

End Sub

Help!

Thanks,
-Matt G.

 

[CJ_London]

I think you'll have to check for null and exclude if it is null something like


iif(dtmstart is null, "", "#" & dtmStart & "#, " & _

 

[madEG]

CJ, Thanks... I'm closer?

        "', " & _
        IIf(dtmStart Is Null, "", Chr(35) & dtmStart & Chr(35)) & _
        ", " & _
        IIf(dtmEnd Is Null, "", Chr(35) & dtmEnd & Chr(35)) & _
        ", '" & _

...but now I get Runtime err 424, Object required.

When I debug, the whole strSQL sql statement is highlighted. Hovering over the dtmStart and dtmEnd shows dtmStart="" ...

Thoughts?

 

[MarkK]

As an aside, you might want to use a parameterized QueryDef to do your inserts, which saves you all the delimiting of your values, like . . .

[COLOR="Green"]'clarifies your SQL[/COLOR]
Const SQL As String = _
   "INSERT INTO tblYourTable " & _
      "( DateField, StringField, LongIntegerField, YesNoField ) " & _
   "VALUES " & _
      "( prm0, prm1, prm2, prm3 )"

Sub DoInsert
[COLOR="Green"]   'create temp QueryDef[/COLOR]
   With CurrentDb.CreateQueryDef("", SQL)
[COLOR="Green"]      'assign values to parameters, delimiters handled automatically[/COLOR]
      .Parameters("prm0") = Me.txtSomeDate
      .Parameters("prm1") = Me.txtSomeString
      .Parameters("prm2") = Me.cboSomeID
      .Parameters("prm3") = Me.chkBooleanValue
[COLOR="Green"]      'run the query[/COLOR]
      .Execute dbFailOnError
      .Close
   End With
End Sub

Hope this helps,

 

[CJ_London]

perhaps dtmstart is not null, but a zero length string.

Also, you need to include the comma in your iif statement

IIf(nz(dtmStart,"") ="", "", Chr(35) & dtmStart & Chr(35) & ", ") & _

 

[spikepl]

Note that parametrized entries like shown by MarkK also take care of things like apostrophes in names, or decimal separators like "," in decimal numbers!

 

[CJ_London]

don't forget dates need to be formatted us style - mm/dd/yyyy

 

[MarkK]

It's also easy to validate your data "inline" with this approach . . .

   ...
   With CurrentDb.CreateQueryDef("", SQL)
[COLOR="Green"]      'check your date is valid, or don't use it[/COLOR]
      If IsDate(Me.txtSomeDate) Then .Parameters("prm0") = Me.txtSomeDate
      .Parameters("prm1") = Me.txtSomeString
      .Parameters("prm2") = Me.cboSomeID
      ...

Or call a function that always returns a useable value . . .

   ...
   With CurrentDb.CreateQueryDef("", SQL)
      .Parameters("prm0") = GetValidDate(Me.txtSomeDate)
      .Parameters("prm1") = Me.txtSomeString
      ...

Private Function GetValidDate(txt as TextBox) As Date
[COLOR="Green"]   'do complex checks or changes to date value here[/COLOR]
End Function

And if you are using date variables, date formats don't matter.

 

[madEG]

Ok, so now I'm here...

        "', " & _
        IIf(Nz(dtmStart, "") = "", "", Chr(35) & dtmStart & Chr(35)) & _
        ", " & _
        IIf(Nz(dtmEnd, "") = "", "", Chr(35) & dtmEnd & Chr(35)) & _
        ", '" & _

and when I have blank dates using the above I get this for a string check to before insert (attached Capture1.jpg)

Then Runtime err 3134, syntax err in INSERT INTO statement.

When I do have dates in I see this (capture2.jpg) and the entry goes in just fine.

Geez - I'm stumped...

 

[madEG]

...and also MarkK, thanks for the tip re the above and QueryDef... That is a new thing for me. I'll give it some serious looking over when I make a new app (sadly, self taught here) - which should be in a few weeks - if I can just finish this one

 

[madEG]

In the end, I ended up having to check if the dates are blank, and if so, not include them in the insert query. This feels lame, and of course if someone has date info, and then removes these dates on purpose, then using this "method" will fail to remove the dates when the record is updated. But alas... I can't figure anything better out.

If anyone can push me in the right direction on how to not get a conversion error when updating a record with a datetime field with a blank, that would be great! Thanks

If (Format(dtmStart) = "" Or Format(dtmStart) = "") Then
            strSQL = "INSERT INTO dbo_Activities (GoalObjectiveIDf, ActivityName, ActivityDesc, OrganizationalFunction, TopicalFocus, PlanningCycle, FundingSource, PeriodicDetail, KeyPerformanceIndicator, ProjectedCosts, ProjectedStaffHours, MetricType, PrimaryAudience) VALUES (" & _
            
[listboxGoalsObjectives].[Value] & ", '" & Replace(Format(Trim(txtActivityName)), "'", "''") & "', '" & Replace(Trim(Nz(txtActivityDesc, "")), "'", "''") & "', '" & _
            Replace(Format(Trim(txtOrgFunction)), "'", "''") & _
            "', '" & _
            Replace(Format(Trim(txtTopicalFocus)), "'", "''") & _
            "', '" & _
            Replace(Format(Trim(cboPlanningCycle)), "'", "''") & _
            "', '" & _
            Replace(Format(Trim(txtFundingSource)), "'", "''") & _
            "', '" & _
            Replace(Format(Trim(cboPeriodicDetail)), "'", "''") & _
            "', '" & _
            Replace(Format(Trim(txtKeyPerformanceIndicator)), "'", "''") & _
            "', '" & _
            Replace(Format(Trim(numProjectedCosts)), "'", "''") & _
            "', '" & _
            Replace(Format(Trim(numProjectedStaffHours)), "'", "''") & _
            "', '" & _
            Replace(Format(Trim(txtMetricType)), "'", "''") & _
            "', '" & _
            Replace(Format(Trim(txtPrimaryAudience)), "'", "''") & "');"
        Else
            strSQL = "INSERT INTO dbo_Activities (GoalObjectiveIDf, ActivityName, ActivityDesc, OrganizationalFunction, TopicalFocus, PlanningCycle, FundingSource, PeriodicDetail, KeyPerformanceIndicator, ProjectedCosts, ProjectedStaffHours, MetricType, StartDatetime, EndDatetime, PrimaryAudience) VALUES (" & _
            
[listboxGoalsObjectives].[Value] & ", '" & Replace(Format(Trim(txtActivityName)), "'", "''") & "', '" & Replace(Trim(Nz(txtActivityDesc, "")), "'", "''") & "', '" & _
            Replace(Format(Trim(txtOrgFunction)), "'", "''") & _
            "', '" & _
            Replace(Format(Trim(txtTopicalFocus)), "'", "''") & _
            "', '" & _
            Replace(Format(Trim(cboPlanningCycle)), "'", "''") & _
            "', '" & _
            Replace(Format(Trim(txtFundingSource)), "'", "''") & _
            "', '" & _
            Replace(Format(Trim(cboPeriodicDetail)), "'", "''") & _
            "', '" & _
            Replace(Format(Trim(txtKeyPerformanceIndicator)), "'", "''") & _
            "', '" & _
            Replace(Format(Trim(numProjectedCosts)), "'", "''") & _
            "', '" & _
            Replace(Format(Trim(numProjectedStaffHours)), "'", "''") & _
            "', '" & _
            Replace(Format(Trim(txtMetricType)), "'", "''") & _
            "', " & _
            Chr(35) & dtmStart & Chr(35) & _
            ", " & _
            Chr(35) & dtmEnd & Chr(35) & _
            ", '" & _
            Replace(Format(Trim(txtPrimaryAudience)), "'", "''") & "');"
        End If

 

[MarkK]

how to not get a conversion error when updating a record with a datetime field with a blank, that would be great! Thanks

This is solved with code examples in post #8.

 

[vbaInet]

Going by MarkK's suggestion in post #8, if it's easier, you could manually create the query and add those parameters to the Parameters list defining their data types.
This would be my preferred option.