In range date month/year

[thr33xx]

Hello,

I have a database which users input multiple date records which contain month/year. In addition, there are two specific dates which are entered into the database, a start and end date (which are month/day/year).

All records that the user enters (month/year) must fall between the range of the start and end date (month/day/year).

How exactly would I enforce such a verification?

'Checks to ensure recuiting period is within enrollment start and expected enrollment complete
If DLookup("[Study_ID]", "dbo_Metrics", "[Study_ID] = '" & Me.Study_ID & "'AND [Report_Per_Mo] = " & Me.Add_Report_Per_Mo & " AND [Report_Per_Yr] = " & Me.Add_Report_Per_Yr & "") < St_Dt_Metrics OR > Dt_Exp_Enroll_Complete Then
        MsgBox "The recruiting period entered does not fall between the start date of enrollment metrics and date expected enrollment complete.", vbCritical + vbOKOnly + vbDefaultButton1, "Warning!"
    Else
        End If

 

[vbaInet]

How about using the DCount() function?

 

[thr33xx]

Sorry, I don't see how I would make use of the DCount function, as I am not trying to get a count, but I am trying to enforce a validation and make sure that the date added (month/year) is between the start date (month/day/year) and end date (month/day/year). The problem I am having is how to go about the differences in date formats.

Is there a way to convert the month/year to month/(FIRST DAY OF MONTH)/year?

 

[vbaInet]

You put it in the Before Update event of the control and set the Cancel argument of the Before Update sub to True if DCount() < 0. In the criteria for the date field, use BETWEEN.

 

[thr33xx]

Alright, I think I understand. However, how do I get the Dcount to work if I am using Dateserial which is pulling values from fields on the form? My understanding with Dcount is as follows,

DCount ( expression, domain, [criteria] )

However, I am not using a domain/table. Is there a way to not use the domain/table since I am calling the value using dateserial which is drawing values from fields on the form?

'Checks to ensure recuiting period is within enrollment start and expected enrollment complete dates

If DCount("[DateSerial([Add_Report_Per_Yr], [Add_Report_Per_Mo], 1)]","[COLOR="Red"][B][SIZE="5"][WhatdoIinserthere?][/SIZE][/B][/COLOR]","[Study_ID] = '" & Me.Study_ID & "' Between #" & [St_Dt_Metrics] & "# _
And #" & [Dt_Exp_Enroll_Complete] & "#") <= 0 Then
     Cancel = True
Else
     End If

 

[vbaInet]

Then just use DateSerial() with DateDiff() and check the difference between the dates.