Function not running inside a Sub when a date field is blank

[cyberwolf0000 via AccessMonster.com]

I have a save record button on my form that does some basic validation. This
validation makes sure that the data entered is of the right type and that
certain fields contain data.

That said I have one function that calls another function. This is done
because the 2nd function is used in other areas aside from this function.

Here is the code of the first function:
Public Function ValidateForm(frm As Form) As Integer

Dim db As DAO.Database
Dim ctl As Control
Dim intErr As Integer
Dim strTable As String, strField As String
Dim fld As Field, o As Object

10 Set db = CurrentDb()
20 strTable = frm.RecordSource
30 For Each ctl In frm
40 Debug.Print ctl.Name
50 Select Case ctl.ControlType
Case acTextBox, acComboBox
60 strField = ctl.ControlSource
70 On Error Resume Next
80 Set o = db.TableDefs(strTable)
90 If Err.Number = 0 Then
' record source is a table.
100 On Error Resume Next
110 Set fld = o.Fields(strField)
120 Else
' not a table - must be a query.
130 On Error GoTo 0
140 Set o = db.QueryDefs(strTable)
150 With o.Fields(strField)
160 strTable = .SourceTable
170 strField = .SourceField
180 End With
190 Set fld = db.TableDefs(strTable).Fields(strField)
200 End If
210 If ctl.Tag = "Validate" And ctl.Visible = True Then
220 If ctl.Enabled = True Then
230 Select Case fld.Type
Case 10, 12
240 ValidateForm = ValidateForm + ValidateTextEnd(ctl.Controls
(0).Caption, _
ctl.Name, ctl.Value, frm.Form.Name)
250 Case 4, 7
260 ValidateForm = ValidateForm + ValidateNumEnd(ctl.Controls(0)
.Caption, _
ctl.Name, ctl.Value, frm.Form.Name)
270 Case 8
280 ValidateForm = ValidateForm + ValidateDateEnd(ctl.Controls
(0).Caption, _
ctl.Name, ctl.Value, frm.Form.Name)
290 End Select
300 End If

310 End If
320 End Select

330 Next ctl

End Function

Line 280 is the offending line. Basically the select case sees the field as
a date field and runs the Case 8 statement, but it hits this line, and
instead of running the function, it just continues to the next line of code.

The other 2 functions in the select statement run fine and validates the
fields. Is there something about date type fields that when blank would
cause this behavior?

TIA,

 

[OssieMac]

Without specifically testing your code, you should note that where there is
the remotest possibility of a variable/field being null, you should use the
Nz function.
Basically this function converts a null field to either a zero or a zero
length string. However, if the field is not a null, it leaves the value as
is. You embed the Nz function in the code with the fieldname in lieu of just
the field on it's own.

Nz(Fieldname,0) if it is a numeric field or
Nz(fieldname,"") if it is a text field.

Now a date field, while it might display as a date, it is actually a number
so you would use Nz(Datefieldname,0) and if the datefield is null, it returns
a zero and any code you use can compare/calculate a zero; (rather than null
which it can't do.) Likewise for a string field; you can compare to a zero
length field but not a null.

Hope I have explained it well enough for you to try and see if it fixes the
problem.

 

[cyberwolf0000 via AccessMonster.com]

Thanks OssieMac,

I wasn't thinking in those terms. I knew that Date were basically a serial
number representing a date/time. I put in the Nz function and it worked
perfectly.

Without specifically testing your code, you should note that where there is
the remotest possibility of a variable/field being null, you should use the
Nz function.
Basically this function converts a null field to either a zero or a zero
length string. However, if the field is not a null, it leaves the value as
is. You embed the Nz function in the code with the fieldname in lieu of just
the field on it's own.

Nz(Fieldname,0) if it is a numeric field or
Nz(fieldname,"") if it is a text field.

Now a date field, while it might display as a date, it is actually a number
so you would use Nz(Datefieldname,0) and if the datefield is null, it returns
a zero and any code you use can compare/calculate a zero; (rather than null
which it can't do.) Likewise for a string field; you can compare to a zero
length field but not a null.

Hope I have explained it well enough for you to try and see if it fixes the
problem.

I have a save record button on my form that does some basic validation. This
validation makes sure that the data entered is of the right type and that

[quoted text clipped - 69 lines]

TIA,

--
James B Gaylord
For the Wolf comes the strength of the Pack,
For the Pack comes the strength of the Wolf,
-R. Kipling
Office 2003 on Win XP SP2