Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 351
  • Last Modified:

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
0
MissB618
Asked:
MissB618
1 Solution
 
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
 
Rey Obrero (Capricorn1)Commented:
if your ( DATE field ) is DateTime data type

Between cdate([Forms]![frmWklyDialog]![StartDate]) And cdate([Forms]![frmWklyDialog]![StartDate])+7
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now