I've created a simple access database and everything has been working fine, until the new year (2004).
I have a simple form, where you enter two dates, and then it uses a report to display any results between those dates.
The problem is that if my date range falls in 2004, it does not return ANY results. If the date range is in 2003, it works perfectly.
(for example, 12/01/03 to 12/31/03 WORKS, but 1/1/04 to 1/30/04 does NOT).
Here is my code on the form:
Option Compare Database
Private Sub Command0_Click()
On Error GoTo Err_Command0_Click
Dim stDocName As String
Dim strWhere As String
strWhere = strWhere & " AND satDown=true "
If Len(Me.fromDate & "") > 0 And Len(Me.toDate & "") > 0 Then
strWhere = strWhere & " AND dateofreferral Between #" & Me.fromDate & "# AND #" & Me.toDate & "#"
MsgBox ("Between " & Me.fromDate & " and " & Me.toDate)
strWhere = strWhere & " AND dateofreferral Between #" & Date & "# AND #" & Date & "#"
stDocName = "Sits Report"
If Len(strWhere & "") = 0 Then
DoCmd.OpenReport stDocName, acViewPreview
DoCmd.OpenReport stDocName, acViewPreview, WhereCondition:=Mid(strWhere, 6)
open your form in design view, double click on input/text box used to collect 'From' 'To'dates. When properties pop up go to 'Format' tab. Put the cursor in the top line and click on down arrow on the right. Select 'long/short date'.
This will enable a user to just put '2 15' in the input/text box and after you go to the next control access will automatically change it to '02-15-2004'.
Also make sure that table fields that hold dates match the input/text boxs' settings.