Need Between.. And to work with criteria from dialog box

I am putting together a report that will list calls received for a one week period.  I created a dialog box so that the user can input the start date.  In my query in the DATE field I tried to put the following criteria, but its not working.

Between [Forms]![frmWklyDialog]![StartDate] And [Forms]![frmWklyDialog]![StartDate]+7

I need it to pull records dated between whatever they enter in for the start date and the start date plus seven days.

Thanks
MissB618Asked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Rey Obrero (Capricorn1)Connect With a Mentor Commented:
if your ( DATE field ) is DateTime data type

Between cdate([Forms]![frmWklyDialog]![StartDate]) And cdate([Forms]![frmWklyDialog]![StartDate])+7
0
 
rheitzmanCommented:
You can use a Where clause on the OpenReport method. The sample below adds one month. You can use "ww" for a week or "d" for days. See help for DateAdd().
Private Sub Command0_Click()
On Error GoTo Err_Command0_Click
    Dim stDocName As String
    Dim stWhere As String
 
    stDocName = "Report1"
    stWhere = "Last_Update_Date BETWEEN #" & Me.txtStartDate _
        & "# AND #" & DateAdd("m", 1, Me.txtStartDate) & "#"
    DoCmd.OpenReport stDocName, acPreview, , stWhere
 
Exit_Command0_Click:
    Exit Sub
 
Err_Command0_Click:
    MsgBox err.Description
    Resume Exit_Command0_Click
End Sub

Open in new window

0
 
GRayLCommented:
In a control source in a dialog box, you cannot enter a query.  Please be very specific about what you are tryin to do.
0
 
dannywarehamCommented:
I would change the code only slightly to ensure that you get date entries (cos if you don't your query will fail):
Private Sub Command0_Click()
On Error GoTo Err_Command0_Click
    Dim stDocName As String
    Dim stWhere As String
    Dim myStartDate as String
 
myStartDate = nz(Me.txtStartDate,"Empty")
 
If Not IsDate(myStartDate) then
    Msgbox "You must enter a date for the start",vbokonly,"Error"
    Me.txtStartDate.Setfocus
End if
 
    stDocName = "Report1"
    stWhere = "Last_Update_Date BETWEEN #" & format(myStartDate,"dd-mmm-yyyy") & "# AND #" & DateAdd("m", 1, format(myStartDate,"dd-mmm-yyyy")) & "#"
    DoCmd.OpenReport stDocName, acPreview, , stWhere
 
Exit_Command0_Click:
    Exit Sub
 
Err_Command0_Click:
    MsgBox err.Description
    Resume Exit_Command0_Click
End Sub

Open in new window

0
All Courses

From novice to tech pro — start learning today.