Date Lookup

[msii]

Help,
I need to create a date lookup field on one of my forms to access order information for a specific date range ie. (12/15/02 - 01/05/03) where the first date is in one text box and the other date is in a second text box to create the date range then once the date range is entered to run it against a query that I have already created. I am thinking this is called date parameter but I'm not sure if it is or how to do it.

Thanks,
MSII

 

[Schof]

Here's one approach you could take...

Create a new form called Calendar and add the calendar control (Insert->ActiveX Control) to it, naming it calendarCtl. In the form's class module define four global variables:

Calendar Form
Option Compare Database
Option Explicit

Public frm As String
Public ctl As String
Public startDate As Date
Public endDate As Date

In the main form add a button, called SELECT_CALENDAR_DATE_Button that opens up the Calendar form and sets the variables:

Main Form
Private Sub SELECT_CALENDAR_DATE_Button_Click()
Dim frmName As String

frmName = "Calendar"
DoCmd.openForm frmName
Forms(frmName).frm = Me.Form.name
Forms(frmName).ctl = "queryDate" ' or whatever the control name is that stores the date for which you want to run the query
Forms(frmName).startDate = Me.startDate ' or whatever the control name is that stores the start date
Forms(frmName).endDate = Me.endDate ' or whatever the control name is that stores the end date
End Sub

Then when the user double-clicks on one of the dates in the Calendar form, the selection would be validated according to your date range and if valid would set the value in the control of the calling form...

Calendar Form
Private Sub calendarCtl_DblClick()
If Me.calendarCtl >= startDate And Me.calendarCtl <= endDate Then
Forms(frm).ctl = Me.calendarCtl
DoCmd.Close acForm, Me.Form.name, acSaveNo
Else
msgBox &quot;You must pick a date between &quot; & startDate & &quot; and &quot; & endDate & &quot;.&quot;, vbOKOnly, &quot;Invalid Date Selected&quot;
End If
End Sub

You could then use the Calendar form to perform date range validations for any number of controls in any number of forms!

 

[SBendBuckeye]

Access should correctly handle your textbox dates for you. Just remember that you have to surround them with pound signs # for it to recognize them as dates (eg Where MyStartDate > #DateValue# and, etc.

Hope this helps!