I am attempting to find the maximum date in a form datasheet field (Date), grouped by a combo box (Combo34). I am now receiving an error message 94 - Invalid use of null. I am not fluent with VB yet, can someone look at my code below, and help me handle null values. For null values I would like to just have the textbox display "Null", but return the maximum date if it exists.

Dim DateX As Date
Dim Msg

Private Sub Text54_Click()
On Error GoTo ErrorHandler
DateX = DMax("Date", "tblWellMaintenanceLogbook", "[Combo34] =" _
& Forms!frmWellMaintenance!Combo34)

ErrorHandlerExit:
Exit Sub
ErrorHandler:
If Err.Number = 2001 Then
Resume Next
Else
MsgBox "Error No: " & Err.Number & "; Description: " & Err.Description
Resume ErrorHandlerExit
End If
End Sub

You are getting a null on your DMAX and need to use the NZ function to handle nulls. If you shouldn't be getting a null then your criteria may not be correct (which it isn't):

1. You should not have a field named DATE as that is an Access reserved word. Because it is, you need to put it in Square Brackets []

2. You don't use [Combo34]= as your criteria- you use a FIELD in your table and then you can say [FieldNameHere]=" & Me.Combo34

3. If your [FieldNameHere] is not a number or date then you need brackets around the data:

[FieldNameHere]=" & Chr(34) & Forms!frmWellMaintenance.Combo34 & Chr(34)

You are getting a null on your DMAX and need to use the NZ function to handle nulls. If you shouldn't be getting a null then your criteria may not be correct (which it isn't):

1. You should not have a field named DATE as that is an Access reserved word. Because it is, you need to put it in Square Brackets []

2. You don't use [Combo34]= as your criteria- you use a FIELD in your table and then you can say [FieldNameHere]=" & Me.Combo34

3. If your [FieldNameHere] is not a number or date then you need brackets around the data:

[FieldNameHere]=" & Chr(34) & Forms!frmWellMaintenance.Combo34 & Chr(34)
Thank you Mr Larson. Another question, then must I somehow reference the table that Combo34 is derived from, before setting it equal to [FieldNameHere]? It is not in the same table as the Date field, and I am working from a form and subform.

Thank you Mr Larson. Another question, then must I somehow reference the table that Combo34 is derived from, before setting it equal to [FieldNameHere]? It is not in the same table as the Date field, and I am working from a form and subform.

It is the same table as the date field, yes.

It is the same table as the date field, yes.
No, Sir. The Date field is from the table tblWellMaintenanceLogbook, as referenced as the domain in the DMax function. The Combo34 field is derived from another table, "tbl_WellID". The Combo34 is an unbound control in a main form, while the Date field is within a subform, though it is referenced to the table in the DMax function. I guess an easier method may be to change the Combo34 criteria reference to the same table as the Date field, as it does contain the same field.

Thanks

When you use a Domain Aggregate the parts have to all be in the same domain. So, if you are looking for a DATE in tblWellMaintenanceLogbook then your criteria must be trying to match another field in tblWellMaintenanceLogbook (usually the primary key) to get that date value.

You can also come up with a query which pulls data together from two or more tables and then use that as the domain as well.

When you use a Domain Aggregate the parts have to all be in the same domain. So, if you are looking for a DATE in tblWellMaintenanceLogbook then your criteria must be trying to match another field in tblWellMaintenanceLogbook (usually the primary key) to get that date value.

You can also come up with a query which pulls data together from two or more tables and then use that as the domain as well.

I have adapted the script to use the WELL_ID as the criteria, which is another field within the same table as the Date, and set the WELL_ID = Combo34 (see below). It works now. Thanks a lot!

Dim DateX As Date
Dim Msg

Private Sub Text54_Click()
On Error GoTo ErrorHandler
DateX = DMax("[Date]", "tblWellMaintenanceLogbook", "[WELL_ID]=" & Chr(34) & Forms!frmWellMaintenance.Combo34 & Chr(34))
Text54 = DateDiff("d", DateX, Now)
ErrorHandlerExit:
Exit Sub
ErrorHandler:
MsgBox "Error No: " & Err.Number & "; Description: " & Err.Description
Resume ErrorHandlerExit
End Sub

Now I would just like to figure out how to have the value always display in the textbox on the form. Currently I have copy/pasted the code in the function for the "After Update" combo box property, so that when a selection is made in the combo box it will update the appropriate value in the textbox. The only problem with this is that when opening the form, the textbox value is empty until a combo box selection is made.

Mr Larson, thank you very much! Your suggestion was a lot of help.

You can set the control source of the one text box to this:

= DMax("[Date]", "tblWellMaintenanceLogbook", "[WELL_ID]=" & Chr(34) & Forms!frmWellMaintenance.Combo34 & Chr(34))

Notice, though, I had also added another line to the script:

Text54 = DateDiff("d", DateX, Now)

to also return the DateDiff in the textbox. Do you think it is possible to combine this with your suggestion, in the text box's control source property?