Search between two dates (1 Viewer)

Hi,


Im using a form with two input boxes 'OrderDateFrom' & 'OrderDateTo' then I press search button with this code below, can anyone see any issues with it as it doesn't work ???


Cheers

______________________________________________________________



Option Compare Database

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] >= #" & Me.OrderDateFrom & "# and [Order Date] <= #" & Me.OrderDateTo & "#)"
task = "Select * from Table1 where (" & strcriteria & ") order by [Order Date)"
DoCmd.ApplyFilter task

End If

End Sub




Cheers

No code needed,
make a query that reads the form ,like:

select * from table where [Order date] between forms!myForm!OrderDateFrom and forms!myForm!OrderDateTo


then the button click does:
docmd.openquery "qsMyDateQuery"

First, define "it doesn't work" for us. Error message? Unexpected output? House catches on fire?

Second, find out what exactly is in the task variable. Don't assume you know whats in there by looking at your code. Find out exactly what is in there:

Debug.print

Please tell us what this really means.

as it doesn't work

Click to expand...

Error message, number?

Also, you should use code tags. That is, highlight your code, then click the #(hash) in the header area.
Indent your code for readability.

This Dim strcriteria, task As String doesn't do what you think.
With Access/VBA you must explicitly Dim variables, otherwise they default to Variant.

you can say
Dim strcriteria as String, task As String or
Dim strcriteria as string
Dim task As String

I'd say check the format of your date criteria. in SQL it generally needs to be mm/dd/yyyy format

Also for a filter, I'd expect you to just use the criteria not the SELECT statement as well.?

This link appears to confirm that.?

https://community.spiceworks.com/topic/85200-docmd-applyfilter-multiple-sort-criteria

I'd also use BETWEEN rather that >= & <=, but that is just me.

Thank you all for your help - I had placed parentheses not a bracket on the - order by [order date]


Cheers