Search from date + 7 days

[LSTC018]

Hi,


Ive managed to get this query shown below to work (lots of help from people on this site) it gives me results of record between two dates from a 'Search Button'. I'm now trying to use the same query set up, to get records for 7 days from the date I input into OrderDateFrom on the Form using a second 'Search Button'. I've highlighted in RED what I have changed for the second query See below,


Cheers



Private Sub Command12_Click()
' search button
Call search
End Sub
Sub search()
Dim strCriteria, task As String

Me.Refresh
If IsNull(Me.OrderDateFrom) Or IsNull(Me.OrderDateTo) Then
MsgBox "Please enter the date range", vbInformation, "Date Range Required"
Me.OrderDateFrom.SetFocus

Else
strCriteria = "([Order Date] between #" & Format(Me.OrderDateFrom, "mm/dd/yyyy") & "# And #" & Format(Me.OrderDateTo, "mm/dd/yyyy") & "#)"
task = "Select * from Table1 where (" & strCriteria & ") order by [Order Date]"
DoCmd.ApplyFilter task

End If

End Sub




Sub search1()
Dim strCriteria, task As String

Me.Refresh
If IsNull(Me.OrderDateFrom) Then
MsgBox "Please enter the date range", vbInformation, "Date Range Required"
Me.OrderDateFrom.SetFocus

Else
strCriteria = "([Order Date] >Date() #" & Format(Me.OrderDateFrom, "mm/dd/yyyy") & "# And ""([Order Date] <=Date()+7"
task = "Select * from Table1 where (" & strCriteria & ") order by [Order Date]"
DoCmd.ApplyFilter task

End If

End Sub

 

[Gasman]

As you are just using Date I believe you can just use

strCriteria = "[Order Date] BETWEEN Date() and Date() + 7"

 

[MajP]

I believe Gasman's will work since it is not a literal.
But if not
"[Order Date] BETWEEN " & Format(Me.orderDateFrom, "\#mm/dd/yyyy\#") & " AND " & Format(Date + 7, "\#mm/dd/yyyy\#")

 

[LSTC018]

Hi,


This takes me through the criteria but shows no results (Sorry). When I debug it shows :-


: strCriteria : "([Order Date] >Date() #01/09/2019# And "([Order Date] <=Date()+7#" : Variant/String


: task : "Select * from Table1 where (([Order Date] >Date() #01/09/2019# And "([Order Date] <=Date()+7#) order by [Order Date]" : String


Cheers

 

[Gasman]

Re reading your post, and if the first piece of code worked

strCriteria = "([Order Date] between #" & Format(Me.OrderDateFrom, "mm/dd/yyyy") & "# And #" & Format(Me.OrderDateFrom +7, "mm/dd/yyyy") & "#)"

 

[Gasman]

You have not used what MajP posted?

Hi,


This takes me through the criteria but shows no results (Sorry). When I debug it shows :-


: strCriteria : "([Order Date] >Date() #01/09/2019# And "([Order Date] <=Date()+7#" : Variant/String


: task : "Select * from Table1 where (([Order Date] >Date() #01/09/2019# And "([Order Date] <=Date()+7#) order by [Order Date]" : String


Cheers

 

[plog]

Debug 101: Opening parenthesis total should equal closing parenthesis totl

Debug 102: Quote marks should come in pairs.

 

[LSTC018]

Sorted thanks Gasman. Also, much appreciated input and help from everyone,

Cheers