Date Validation Code

[FranD]

I have have two forms - frmLogBatch and frmCloseBatch. The user enters a DateReceived on the form frmLogBatch and then later enters a DateClosed on the frmCloseBatch. I have a command button on the frmLogBatch which launches the frmCloseBatch and links the two forms via the Control# field (primary key in both underlying tables). What I'm trying to do is write code which prevents users from enterring a DateClosed which preceeds the DateReceieved. Here's what I put in the frmClosed Before Update event property:

If DateClosed < DLookUp ("[DateReceived]","LogBatch","[Control#]="&[Forms]![frmLogBatch]![[Control#]) Then
.......

The error message I'm getting is "Data Type Mismatch". I don't understand why, since both fields are a date/time data type.

Can anyone help.....

Thanks - Fran

 

[Pat Hartman]

Try putting pound signs (#) around the date field:
If DateClosed < DLookUp ("[DateReceived]","LogBatch","[Control#]= #"&[Forms]![frmLogBatch]![[Control#] & "#") Then

 

[FranD]

Thanks Pat

I'm still getting an error message, but this time its a syntax error. The Control# is not a date field, the DateClosed and Date Received are the date fields. So I tried to put the pound signs around thos fields and that didn't work either.

Any other suggestions.

Thanks again.

 

[Pat Hartman]

I don't use DLookup() because it is usually the worst way to look up data so I'm just guessing. Perhaps it returns a varient that needs to be converted to a date. Try:
If DateClosed < CDate(DLookUp ("[DateReceived]","LogBatch","[Control#]="&[Forms]![frmLogBatch]![[Control#])) Then

If that doesn't work, you'll need to do some debugging so dim a variable and put the returned value there. Put a stop on the code and look at what is being returned by the function.

dim testvar as varient
testvar = DLookUp ("[DateReceived]","LogBatch","[Control#]="&[Forms]![frmLogBatch]![[Control#])