Weird Date Picker Query Problem

[Tim777]

My design has a Master form [frmSearch] containing two date pickers.
[Forms]![frmSearch]![StartDate]
[Forms]![frmSearch]![EndDate]

It also has a cmd button [cmdSearch] that executes a query [qryTicketSearch] based on the dates selected from the date pickers.

Between [Forms]![frmSearch]![StartDate] And [Forms]![frmSearch]![EndDate]

The results are displayed on a subform [frmTicketsSub] that is bound to the query.



The query is actually much more complex but this is the general gist of it. The query works fine when ran alone. The problem is when I execute the query from the form using the date pickers. It works but it omits any data that falls on the StartDate. For example; if I search for dates between 10/1/11 to 10/3/11, I get only values from 10/2/11 & 10/3/11. Data from 10/1/11 is excluded. If I move my StartDate back one date (i.e. 9/30/11) it works fine.
It doesn't do this when I execute the query alone and supply the dates manually.

Any ideas?

Thanks in advance!

-Tim

[pbaldy]

Let's verify what the picker is doing. After running that process, go to the VBA Immediate window and type this in:

?[Forms]![frmSearch]![StartDate]

and hit enter. It will tell us what's in that control.

[Tim777]

Let's verify what the picker is doing. After running that process, go to the VBA Immediate window and type this in:

?[Forms]![frmSearch]![StartDate]

and hit enter. It will tell us what's in that control.

I ran the query with the {StartDate] of 2/27/12 & [EndDate] of 02/29/12 using the date pickers

The results omitted my data from 02/27/12 but 02/28 & 02/29 were there.

I then went to the VBA immeditate window and entered the above. Here is the result:

Print [Forms]![frmSearch]![StartDate]
2/27/2012 2:24:21 PM

[pbaldy]

Well, it appears your date picker is adding a time for some reason. If your data only contains a date, it would technically be earlier than the date/time in the picker, which would explain why those records are omitted. Is it the built in date picker? I've never seen it add a time like that.

[Tim777]

Yea, it's the built in date picker. Is there a way to disable or exlcude the time portion of the fucntion?

[pbaldy]

I've never seen it add a time. Can you post the db here?

[Tim777]

Here is my code. I changed the format on the date picker to short date and reentered the values. There were a few that had a date and time. Others just had a time. Didn't make any difference, it still omits the StartDate record. Hope this is what you are looking for. Thanks a ton for your help!!!

Option Compare Database
Option Explicit
Dim mintYear As Integer
Dim mintMonth As Integer
Public Property Let Month(intMonth As Integer)
mintMonth = intMonth
End Property
Public Property Get Month() As Integer
Month = mintMonth
End Property
Public Property Let Year(intYear As Integer)
mintYear = intYear
End Property
Public Property Get Year() As Integer
Year = mintYear
End Property


frmSearch
Private Sub Form_Current()
Me.txtRepairNumb.Value = Forms!frmRepairs!txtRepairNumb
Me.DTPicker7 = Now()
End Sub


frmDayDetail

Option Compare Database
Private Sub PopulateEventsList(ctlDayBlock As Control)
On Error GoTo Err_PopulateEventsList
Dim strSQL2 As String

strSQL2 = "Select * From Data_TEMP Where Data_TEMP.[Start Date] = " & _
ctlDayBlock.Tag & " ORDER BY Data_TEMP.[Start Date], Data_TEMP.[CustLastName];"
lstEvents.RowSource = strSQL2
lblEventsOnDate.Caption = Format(ctlDayBlock.Tag, "m-dd-yyyy")

If DCount("*", "Data_TEMP", "[Start Date] = #" & CDate(ctlDayBlock.Tag) & "#") > 0 Then
lstEvents.Visible = True
lblEventsOnDate.Visible = True
Else
lstEvents.Visible = False
lblEventsOnDate.Visible = False
End If

Exit_PopulateEventsList:
Exit Sub

Err_PopulateEventsList:
MsgBox Err.Description, vbExclamation, "Error in PopulateEventsList()"
Resume Exit_PopulateEventsList
End Sub

frmCalendar

Option Compare Database
Option Explicit
Dim objCurrentDate As New objDateInfo 'will hold current year, month & date info
Dim intMonth As Integer
Dim intYear As Integer
Private Sub PopulateYearListBox()
'Sets the Year selection list box to a 25 year range, from 12 years before the current year,
'to 12 years after the current year ("current year" meaning the year currently displayed)
'This procedure has not been thoroughly tested and it will produce a runtime error if
'you go before BC or after Nov 9999
'To use it, call it at the end of the PopulateCalendar procedure
Dim intYear As Integer
Dim intYearCounter As Integer
Dim strRowSource As String
intYear = objCurrentDate.Year
Me.cboYear = intYear
For intYearCounter = (intYear - 12) To (intYear + 11) 'a 24 year range
strRowSource = strRowSource & LTrim(Str(intYearCounter)) & ";"
Next intYearCounter
strRowSource = strRowSource & LTrim(Str(intYearCounter)) 'the 25th year, no semi-colon
Me.cboYear.RowSource = strRowSource
If Me.cboYear = Null Then Me.cboYear = objCurrentDate.Year 'TOwens added 2/26/2010
End Sub
Private Sub OpenEventForm(ctlDayBlock As Control)
DoCmd.OpenForm "frmEvents", , , , , , ctlDayBlock.Tag
End Sub
Private Sub cboMonth_AfterUpdate()
On Error GoTo Err_cboMonth_AfterUpdate
Select Case Me![cboMonth]
Case "January"
objCurrentDate.Month = 1
Case "February"
objCurrentDate.Month = 2
Case "March"
objCurrentDate.Month = 3
Case "April"
objCurrentDate.Month = 4
Case "May"
objCurrentDate.Month = 5
Case "June"
objCurrentDate.Month = 6
Case "July"
objCurrentDate.Month = 7
Case "August"
objCurrentDate.Month = 8
Case "September"
objCurrentDate.Month = 9
Case "October"
objCurrentDate.Month = 10
Case "November"
objCurrentDate.Month = 11
Case "December"
objCurrentDate.Month = 12
Case Else
End Select
PopulateCalendar
Exit_cboMonth_AfterUpdate:
Exit Sub
Err_cboMonth_AfterUpdate:
MsgBox Err.Description, vbExclamation, "Error in cboMonth_AfterUpdate()"
Resume Exit_cboMonth_AfterUpdate
End Sub
Private Sub cboYear_AfterUpdate()
objCurrentDate.Year = Me![cboYear]
PopulateCalendar
End Sub
Private Sub cmdNextMonth_Click()
Me![cboYear] = Null
Me![cboMonth] = Null
objCurrentDate.Month = objCurrentDate.Month + 1
If objCurrentDate.Month = 13 Then
objCurrentDate.Month = 1
objCurrentDate.Year = objCurrentDate.Year + 1
End If
PopulateCalendar
End Sub
Private Sub cmdPreviousMonth_Click()
Me![cboYear] = Null
Me![cboMonth] = Null
objCurrentDate.Month = objCurrentDate.Month - 1
If objCurrentDate.Month = 0 Then
objCurrentDate.Month = 12
objCurrentDate.Year = objCurrentDate.Year - 1
End If
PopulateCalendar
End Sub

Private Sub Detail_DblClick(Cancel As Integer)
Call fPopulateTempTable
End Sub
Private Sub Form_Activate()
PopulateCalendar
End Sub
Private Sub Form_Open(Cancel As Integer)
On Error GoTo Err_Form_Open
Dim dtmTodaysDate
Dim strMsg As String
Dim intNumOfRecs As Integer
dtmTodaysDate = Now
objCurrentDate.Month = Month(dtmTodaysDate)
objCurrentDate.Year = Year(dtmTodaysDate)
If Not fPopulateTempTable() Then Exit Sub
Exit_Form_Open:
Exit Sub

Err_Form_Open:
MsgBox Err.Description, vbExclamation, "Error in Form_Open()"
Resume Exit_Form_Open
End Sub
Private Sub PopulateCalendar()
On Error GoTo Err_PopulateCalendar
Dim strFirstOfMonth As String, bytFirstWeekdayOfMonth As Byte, bytBlockCounter As Byte
Dim bytBlockDayOfMonth As Byte, lngBlockDate As Long, ctlDayBlock As Control
Dim bytDaysInMonth As Byte, bytEventDayOfMonth As Byte, lngFirstOfMonth As Long
Dim lngLastOfMonth As Long, lngFirstOfNextMonth As Long, lngLastOfPreviousMonth As Long
Dim lngEventDate As Long, bytBlankBlocksBefore As Byte, bytBlankBlocksAfter As Byte
Dim astrCalendarBlocks(1 To 42) As String, db As Database, rstEvents As Recordset
Dim strSelectEvents As String, strEvent As String
Dim lngSystemDate As Long 'CFB added 1-25-08
Dim ctlSystemDateBlock As Control, blnSystemDateIsShown As Boolean 'CFB added 1-25-08
Dim strSQL As String 'Added 4/16/2008
Dim blnRetVal, intNumOfRecs As Integer
lngSystemDate = Date 'CFB added 1-25-08 - TO changed value from False to True-2/26/10
intMonth = objCurrentDate.Month
intYear = objCurrentDate.Year
lstEvents.Visible = False
lblEventsOnDate.Visible = False
lblMonth.Caption = MonthAndYear(intMonth, intYear)
strFirstOfMonth = Str(intMonth) & "/1/" & Str(intYear)
'************************************************* ************************
'strFirstOfMonth = "1/" & Str(intMonth) & Str(intYear)
'************************************************* ************************
bytFirstWeekdayOfMonth = WeekDay(strFirstOfMonth)
lngFirstOfMonth = DateSerial(intYear, intMonth, 1)
lngFirstOfNextMonth = DateSerial(intYear, intMonth + 1, 1)
lngLastOfMonth = lngFirstOfNextMonth - 1
lngLastOfPreviousMonth = lngFirstOfMonth - 1
bytDaysInMonth = lngFirstOfNextMonth - lngFirstOfMonth
bytBlankBlocksBefore = bytFirstWeekdayOfMonth - 1
bytBlankBlocksAfter = 42 - (bytBlankBlocksBefore + bytDaysInMonth)

Set db = CurrentDb


strSQL = "Select Data_TEMP.Tech, Data_TEMP.[Start Date], Data_TEMP.[End Date], " & _
"Data_TEMP.[Start Time], Data_TEMP.[CustLastName] From Data_TEMP Where Data_TEMP.[Start Date] " & _
"Between " & lngFirstOfMonth & " And " & lngLastOfMonth & " ORDER BY Data_TEMP.[Start Date], " & _
"Data_TEMP.[Start Time], Data_TEMP.[CustLastName];"
Set rstEvents = db.OpenRecordset(strSQL) 'Added 4/16/2008
Do While Not rstEvents.EOF
strEvent = rstEvents![CustLastName] & vbNewLine & rstEvents![Start Time]
bytEventDayOfMonth = (rstEvents![Start Date] - lngLastOfPreviousMonth)
bytBlockCounter = bytEventDayOfMonth + bytBlankBlocksBefore
If astrCalendarBlocks(bytBlockCounter) <> "" Then
astrCalendarBlocks(bytBlockCounter) = _
astrCalendarBlocks(bytBlockCounter) & vbNewLine & strEvent
Else
astrCalendarBlocks(bytBlockCounter) = strEvent
End If
rstEvents.MoveNext
Loop

For bytBlockCounter = 1 To 42 'blank blocks at start of month
Select Case bytBlockCounter
Case Is < bytFirstWeekdayOfMonth
astrCalendarBlocks(bytBlockCounter) = ""
ReferenceABlock ctlDayBlock, bytBlockCounter
ctlDayBlock.BackColor = 12632256
ctlDayBlock = ""
ctlDayBlock.Enabled = False
ctlDayBlock.Tag = ""
Case Is > bytBlankBlocksBefore + bytDaysInMonth 'blank blocks at end of month
astrCalendarBlocks(bytBlockCounter) = ""
ReferenceABlock ctlDayBlock, bytBlockCounter
ctlDayBlock.BackColor = 12632256
ctlDayBlock = ""
ctlDayBlock.Enabled = False
ctlDayBlock.Tag = ""
If bytBlankBlocksAfter > 6 And bytBlockCounter > 35 Then
ctlDayBlock.Visible = False
End If
Case Else 'blocks that hold days of the month
bytBlockDayOfMonth = bytBlockCounter - bytBlankBlocksBefore
ReferenceABlock ctlDayBlock, bytBlockCounter
lngBlockDate = lngLastOfPreviousMonth + bytBlockDayOfMonth 'block's date
If bytBlockDayOfMonth < 10 Then
ctlDayBlock = Space(2) & bytBlockDayOfMonth & _
vbNewLine & astrCalendarBlocks(bytBlockCounter)
Else
ctlDayBlock = bytBlockDayOfMonth & _
vbNewLine & astrCalendarBlocks(bytBlockCounter)
End If

'If this block is the system date, change its color (CFB 1-25-08)
If lngBlockDate = lngSystemDate Then
'ctlDayBlock.BackColor = QBColor(13)
ctlDayBlock.BackColor = RGB(0, 0, 255)
ctlDayBlock.ForeColor = QBColor(15)
Set ctlSystemDateBlock = ctlDayBlock
blnSystemDateIsShown = True
Else
ctlDayBlock.BackColor = QBColor(15)
'ctlDayBlock.BackColor = 16777215
ctlDayBlock.ForeColor = 8388608 '====> Added by ADezii on 1/28/2008 (Date
End If 'Text was essentially invisible without it for
ctlDayBlock.Visible = True 'Block representing current day position)
ctlDayBlock.Enabled = True
ctlDayBlock.Tag = lngBlockDate
End Select
Next

'If the system date is in this month, show its events (CFB added 1-25-08)
If blnSystemDateIsShown Then
PopulateEventsList ctlSystemDateBlock
End If

Call PopulateYearListBox 'Added by ADezii on 1/28/2008 - suggested by CFB
Exit_PopulateCalendar:
Exit Sub
Err_PopulateCalendar:
MsgBox Err.Description, vbExclamation, "Error inPopulateCalendar()"
Resume Exit_PopulateCalendar
End Sub
Private Sub ReferenceABlock(ctlDayBlock As Control, bytIndex As Byte)
Set ctlDayBlock = Choose(bytIndex, _
txtDayBlock01, txtDayBlock02, txtDayBlock03, txtDayBlock04, txtDayBlock05, _
txtDayBlock06, txtDayBlock07, txtDayBlock08, txtDayBlock09, txtDayBlock10, _
txtDayBlock11, txtDayBlock12, txtDayBlock13, txtDayBlock14, txtDayBlock15, _
txtDayBlock16, txtDayBlock17, txtDayBlock18, txtDayBlock19, txtDayBlock20, _
txtDayBlock21, txtDayBlock22, txtDayBlock23, txtDayBlock24, txtDayBlock25, _
txtDayBlock26, txtDayBlock27, txtDayBlock28, txtDayBlock29, txtDayBlock30, _
txtDayBlock31, txtDayBlock32, txtDayBlock33, txtDayBlock34, txtDayBlock35, _
txtDayBlock36, txtDayBlock37, txtDayBlock38, txtDayBlock39, txtDayBlock40, _
txtDayBlock41, txtDayBlock42)
End Sub
Private Function MonthAndYear(intMonth As Integer, intYear As Integer) As String
Dim strMonth As String
strMonth = Choose(intMonth, _
"January", "February", "March", "April", "May", "June", _
"July", "August", "September", "October", "November", "December")
MonthAndYear = strMonth & " " & intYear
End Function
Private Sub Form_Timer()
Static lngCounter As Long
lngCounter = lngCounter + 1
Me.Caption = "Access Calendar (" & Format$(Now(), "dddd - mmmm dd, yyyy hh:mm:ss AM/PM") & ")"
If lngCounter Mod 2 = 0 Then
Me![lblQuickMonth].Visible = True
Me![lblQuickYear].Visible = True
Else
Me![lblQuickMonth].Visible = True
Me![lblQuickYear].Visible = True
End If
If lngCounter > 2147483000 Then lngCounter = 0
End Sub
Private Sub txtDayBlock01_DblClick(Cancel As Integer)
DoCmd.OpenForm "frmDayDetail", acNormal, , , acFormEdit, acDialog
End Sub
Private Sub txtDayBlock01_GotFocus()
PopulateEventsList Me.ActiveControl
End Sub
Private Sub txtDayBlock02_DblClick(Cancel As Integer)
DoCmd.OpenForm "frmDayDetail", acNormal, , , acFormEdit, acDialog
End Sub
Private Sub txtDayBlock02_GotFocus()
PopulateEventsList Me.ActiveControl
End Sub
Private Sub txtDayBlock03_DblClick(Cancel As Integer)
DoCmd.OpenForm "frmDayDetail", acNormal, , , acFormEdit, acDialog
End Sub
Private Sub txtDayBlock03_GotFocus()
PopulateEventsList Me.ActiveControl
End Sub
Private Sub txtDayBlock04_DblClick(Cancel As Integer)
DoCmd.OpenForm "frmDayDetail", acNormal, , , acFormEdit, acDialog
End Sub
Private Sub txtDayBlock04_GotFocus()
PopulateEventsList Me.ActiveControl
End Sub
Private Sub txtDayBlock05_DblClick(Cancel As Integer)
DoCmd.OpenForm "frmDayDetail", acNormal, , , acFormEdit, acDialog
End Sub
Private Sub txtDayBlock05_GotFocus()
PopulateEventsList Me.ActiveControl
End Sub
Private Sub txtDayBlock06_DblClick(Cancel As Integer)
DoCmd.OpenForm "frmDayDetail", acNormal, , , acFormEdit, acDialog
End Sub
Private Sub txtDayBlock06_GotFocus()
PopulateEventsList Me.ActiveControl
End Sub
Private Sub txtDayBlock07_DblClick(Cancel As Integer)
DoCmd.OpenForm "frmDayDetail", acNormal, , , acFormEdit, acDialog
End Sub
Private Sub txtDayBlock07_GotFocus()
PopulateEventsList Me.ActiveControl
End Sub
Private Sub txtDayBlock08_DblClick(Cancel As Integer)
DoCmd.OpenForm "frmDayDetail", acNormal, , , acFormEdit, acDialog
End Sub
Private Sub txtDayBlock08_GotFocus()
PopulateEventsList Me.ActiveControl
End Sub
Private Sub txtDayBlock09_DblClick(Cancel As Integer)
DoCmd.OpenForm "frmDayDetail", acNormal, , , acFormEdit, acDialog
End Sub
Private Sub txtDayBlock09_GotFocus()
PopulateEventsList Me.ActiveControl
End Sub
Private Sub txtDayBlock10_DblClick(Cancel As Integer)
DoCmd.OpenForm "frmDayDetail", acNormal, , , acFormEdit, acDialog
End Sub
Private Sub txtDayBlock10_GotFocus()
PopulateEventsList Me.ActiveControl
End Sub
Private Sub txtDayBlock11_DblClick(Cancel As Integer)
DoCmd.OpenForm "frmDayDetail", acNormal, , , acFormEdit, acDialog
End Sub
Private Sub txtDayBlock11_GotFocus()
PopulateEventsList Me.ActiveControl
End Sub
Private Sub txtDayBlock12_DblClick(Cancel As Integer)
DoCmd.OpenForm "frmDayDetail", acNormal, , , acFormEdit, acDialog
End Sub
Private Sub txtDayBlock12_GotFocus()
PopulateEventsList Me.ActiveControl
End Sub
Private Sub txtDayBlock13_DblClick(Cancel As Integer)
DoCmd.OpenForm "frmDayDetail", acNormal, , , acFormEdit, acDialog
End Sub
Private Sub txtDayBlock13_GotFocus()
PopulateEventsList Me.ActiveControl
End Sub
Private Sub txtDayBlock14_DblClick(Cancel As Integer)
DoCmd.OpenForm "frmDayDetail", acNormal, , , acFormEdit, acDialog
End Sub
Private Sub txtDayBlock14_GotFocus()
PopulateEventsList Me.ActiveControl
End Sub
Private Sub txtDayBlock15_DblClick(Cancel As Integer)
DoCmd.OpenForm "frmDayDetail", acNormal, , , acFormEdit, acDialog
End Sub
Private Sub txtDayBlock15_GotFocus()
PopulateEventsList Me.ActiveControl
End Sub
Private Sub txtDayBlock16_DblClick(Cancel As Integer)
DoCmd.OpenForm "frmDayDetail", acNormal, , , acFormEdit, acDialog
End Sub
Private Sub txtDayBlock16_GotFocus()
PopulateEventsList Me.ActiveControl
End Sub
Private Sub txtDayBlock17_DblClick(Cancel As Integer)
DoCmd.OpenForm "frmDayDetail", acNormal, , , acFormEdit, acDialog
End Sub
Private Sub txtDayBlock17_GotFocus()
PopulateEventsList Me.ActiveControl
End Sub
Private Sub txtDayBlock18_DblClick(Cancel As Integer)
DoCmd.OpenForm "frmDayDetail", acNormal, , , acFormEdit, acDialog
End Sub
Private Sub txtDayBlock18_GotFocus()
PopulateEventsList Me.ActiveControl
End Sub
Private Sub txtDayBlock19_DblClick(Cancel As Integer)
DoCmd.OpenForm "frmDayDetail", acNormal, , , acFormEdit, acDialog
End Sub
Private Sub txtDayBlock19_GotFocus()
PopulateEventsList Me.ActiveControl
End Sub
Private Sub txtDayBlock20_DblClick(Cancel As Integer)
DoCmd.OpenForm "frmDayDetail", acNormal, , , acFormEdit, acDialog
End Sub
Private Sub txtDayBlock20_GotFocus()
PopulateEventsList Me.ActiveControl
End Sub
Private Sub txtDayBlock21_DblClick(Cancel As Integer)
DoCmd.OpenForm "frmDayDetail", acNormal, , , acFormEdit, acDialog
End Sub
Private Sub txtDayBlock21_GotFocus()
PopulateEventsList Me.ActiveControl
End Sub
Private Sub txtDayBlock22_DblClick(Cancel As Integer)
DoCmd.OpenForm "frmDayDetail", acNormal, , , acFormEdit, acDialog
End Sub
Private Sub txtDayBlock22_GotFocus()
PopulateEventsList Me.ActiveControl
End Sub
Private Sub txtDayBlock23_DblClick(Cancel As Integer)
DoCmd.OpenForm "frmDayDetail", acNormal, , , acFormEdit, acDialog
End Sub
Private Sub txtDayBlock23_GotFocus()
PopulateEventsList Me.ActiveControl
End Sub
Private Sub txtDayBlock24_DblClick(Cancel As Integer)
DoCmd.OpenForm "frmDayDetail", acNormal, , , acFormEdit, acDialog
End Sub
Private Sub txtDayBlock24_GotFocus()
PopulateEventsList Me.ActiveControl
End Sub
Private Sub txtDayBlock25_DblClick(Cancel As Integer)
DoCmd.OpenForm "frmDayDetail", acNormal, , , acFormEdit, acDialog
End Sub
Private Sub txtDayBlock25_GotFocus()
PopulateEventsList Me.ActiveControl
End Sub
Private Sub txtDayBlock26_DblClick(Cancel As Integer)
DoCmd.OpenForm "frmDayDetail", acNormal, , , acFormEdit, acDialog
End Sub
Private Sub txtDayBlock26_GotFocus()
PopulateEventsList Me.ActiveControl
End Sub
Private Sub txtDayBlock27_DblClick(Cancel As Integer)
DoCmd.OpenForm "frmDayDetail", acNormal, , , acFormEdit, acDialog
End Sub
Private Sub txtDayBlock27_GotFocus()
PopulateEventsList Me.ActiveControl
End Sub
Private Sub txtDayBlock28_DblClick(Cancel As Integer)
DoCmd.OpenForm "frmDayDetail", acNormal, , , acFormEdit, acDialog
End Sub
Private Sub txtDayBlock28_GotFocus()
PopulateEventsList Me.ActiveControl
End Sub
Private Sub txtDayBlock29_DblClick(Cancel As Integer)
DoCmd.OpenForm "frmDayDetail", acNormal, , , acFormEdit, acDialog
End Sub
Private Sub txtDayBlock29_GotFocus()
PopulateEventsList Me.ActiveControl
End Sub
Private Sub txtDayBlock30_DblClick(Cancel As Integer)
DoCmd.OpenForm "frmDayDetail", acNormal, , , acFormEdit, acDialog
End Sub
Private Sub txtDayBlock30_GotFocus()
PopulateEventsList Me.ActiveControl
End Sub
Private Sub txtDayBlock31_DblClick(Cancel As Integer)
DoCmd.OpenForm "frmDayDetail", acNormal, , , acFormEdit, acDialog
End Sub
Private Sub txtDayBlock31_GotFocus()
PopulateEventsList Me.ActiveControl
End Sub
Private Sub txtDayBlock32_DblClick(Cancel As Integer)
DoCmd.OpenForm "frmDayDetail", acNormal, , , acFormEdit, acDialog
End Sub
Private Sub txtDayBlock32_GotFocus()
PopulateEventsList Me.ActiveControl
End Sub
Private Sub txtDayBlock33_DblClick(Cancel As Integer)
DoCmd.OpenForm "frmDayDetail", acNormal, , , acFormEdit, acDialog
End Sub
Private Sub txtDayBlock33_GotFocus()
PopulateEventsList Me.ActiveControl
End Sub
Private Sub txtDayBlock34_DblClick(Cancel As Integer)
DoCmd.OpenForm "frmDayDetail", acNormal, , , acFormEdit, acDialog
End Sub
Private Sub txtDayBlock34_GotFocus()
PopulateEventsList Me.ActiveControl
End Sub
Private Sub txtDayBlock35_DblClick(Cancel As Integer)
DoCmd.OpenForm "frmDayDetail", acNormal, , , acFormEdit, acDialog
End Sub
Private Sub txtDayBlock35_GotFocus()
PopulateEventsList Me.ActiveControl
End Sub
Private Sub txtDayBlock36_DblClick(Cancel As Integer)
DoCmd.OpenForm "frmDayDetail", acNormal, , , acFormEdit, acDialog
End Sub
Private Sub txtDayBlock36_GotFocus()
PopulateEventsList Me.ActiveControl
End Sub
Private Sub txtDayBlock37_DblClick(Cancel As Integer)
DoCmd.OpenForm "frmDayDetail", acNormal, , , acFormEdit, acDialog
End Sub
Private Sub txtDayBlock37_GotFocus()
PopulateEventsList Me.ActiveControl
End Sub
Private Sub txtDayBlock38_DblClick(Cancel As Integer)
DoCmd.OpenForm "frmDayDetail", acNormal, , , acFormEdit, acDialog
End Sub
Private Sub txtDayBlock38_GotFocus()
PopulateEventsList Me.ActiveControl
End Sub
Private Sub txtDayBlock39_DblClick(Cancel As Integer)
DoCmd.OpenForm "frmDayDetail", acNormal, , , acFormEdit, acDialog
End Sub
Private Sub txtDayBlock39_GotFocus()
PopulateEventsList Me.ActiveControl
End Sub
Private Sub txtDayBlock40_DblClick(Cancel As Integer)
DoCmd.OpenForm "frmDayDetail", acNormal, , , acFormEdit, acDialog
End Sub
Private Sub txtDayBlock40_GotFocus()
PopulateEventsList Me.ActiveControl
End Sub
Private Sub txtDayBlock41_DblClick(Cancel As Integer)
DoCmd.OpenForm "frmDayDetail", acNormal, , , acFormEdit, acDialog
End Sub
Private Sub txtDayBlock41_GotFocus()
PopulateEventsList Me.ActiveControl
End Sub
Private Sub txtDayBlock42_DblClick(Cancel As Integer)
DoCmd.OpenForm "frmDayDetail", acNormal, , , acFormEdit, acDialog
End Sub
Private Sub txtDayBlock42_GotFocus()
PopulateEventsList Me.ActiveControl
End Sub
Private Sub PopulateEventsList(ctlDayBlock As Control)
On Error GoTo Err_PopulateEventsList
Dim strSQL2 As String

strSQL2 = "Select * From Data_TEMP Where Data_TEMP.[Start Date] = " & _
ctlDayBlock.Tag & " ORDER BY Data_TEMP.[Start Date], Data_TEMP.[CustLastName];"
lstEvents.RowSource = strSQL2
lblEventsOnDate.Caption = Format(ctlDayBlock.Tag, "m-dd-yyyy")

If DCount("*", "Data_TEMP", "[Start Date] = #" & CDate(ctlDayBlock.Tag) & "#") > 0 Then
lstEvents.Visible = True
lblEventsOnDate.Visible = True
Else
lstEvents.Visible = False
lblEventsOnDate.Visible = False
End If

Exit_PopulateEventsList:
Exit Sub

Err_PopulateEventsList:
MsgBox Err.Description, vbExclamation, "Error in PopulateEventsList()"
Resume Exit_PopulateEventsList
End Sub
Private Sub cmdCloseForm_Click()
On Error GoTo Err_cmdCloseForm_Click
DoCmd.Quit
Exit_cmdCloseForm_Click:
Exit Sub
Err_cmdCloseForm_Click:
MsgBox Err.Description
Resume Exit_cmdCloseForm_Click

End Sub
Private Sub cmdSyncUp_Click()
On Error GoTo Err_cmdSyncUp_Click
If Not fPopulateTempTable() Then Exit Sub
PopulateCalendar
Exit_cmdSyncUp_Click:
Exit Sub
Err_cmdSyncUp_Click:
MsgBox Err.Description
Resume Exit_cmdSyncUp_Click
End Sub

[pbaldy]

That will take awhile to digest; there's no code associated with the built-in date picker, so that is something custom. I take it you can't post the db here, or the relevant parts of it?

[Tim777]

Sorry, I didn't realize I could post the entire db. Here it is.

[pbaldy]

Yeah, that's an ActiveX control, not the built-in picker. I wonder if you tried it now, would it be the current time (ie, is it adding the current time to the selected date)? I don't have that ActiveX installed, so it won't work for me. Let's try this workaround; change your query criteria to:

Between DateValue([Forms]![frmSearch]![StartDate]) And DateValue([Forms]![frmSearch]![EndDate])

And see if it works. DateValue() should strip out the time component.

[Tim777]

You da man!!! Works perfectly! Thank you soooo much!!!!

[pbaldy]

Happy to help, and welcome to the site!