Dlookup & Date: Validation Check

wilderfan

Registered User.
Local time
Today, 10:38
Joined
Mar 3, 2008
Messages
172
I have a form with an unbound combo box that allows user to select from a list of existing client names.

The user then inputs the new name of the client into an unbound text box and then indicates the effective date of the name change.

I'm trying to introduce a validation check to make sure that the effective date falls AFTER the effective date of the client's most recent name change. But I'm experiencing some "glitches". (Nothing new there !)

Here is the curent coding. Any suggestions ?

Private Sub ubdEffectiveDate_BeforeUpdate(Cancel As Integer)
'Check the validity of the proposed new effective date
Dim dEffectiveDate As Date

dEffectiveDate = DLookup("EffectiveDate", "tblClientName", "ClientName = " & Chr(34) & Me.cboClientName & Chr(34)
If ubdEffectiveDate < dEffectiveDate Then
MsgBox "You must choose a later date."
Cancel = True
Me.ubdEffectiveDate.Undo
DoCmd.RunCommand acCmdDelete
End If
End Sub
 
You need to get the MAX of effective date to get the last one.

dEffectiveDate = DMax("EffectiveDate", "tblClientName", "ClientName = " & Chr(34) & Me.cboClientName & Chr(34))
 
Is it correct to say that Dlookup is better for looking up text fields, while Dmax (or Dmin) is more appropriate for date fields ?
 
Is it correct to say that Dlookup is better for looking up text fields, while Dmax (or Dmin) is more appropriate for date fields ?

Depends on what you are looking for. If you have a table with multiple records and you want the latest date you need DMAX. If you have a table with a single record for each ID then you can use DLookup, etc.
 
I tried DMax, but get the same error message that I got with Dlookup.

"Invalid use of Null".

If I was dealing with integers here, I think I would be tempted to try Nz in combination with Dlookup (or DMax). But since I'm trying to pull a date out of a table, I'm not sure that Nz would be the solution.
 
Invalid use of null means that it isn't returning a value because one doesn't exist for that criteria.

You can use the NZ function to stop that but in this case - you would need to be returning a default value - 0 would be 12/30/1899 which of course wouldn't be greater than the other date you are comparing.

So you can use:

dEffectiveDate = Nz(DMax("EffectiveDate", "tblClientName", "ClientName = " & Chr(34) & Me.cboClientName & Chr(34)),0)
 

Users who are viewing this thread

Back
Top Bottom