Solved Date Range Error

[aftab1965]

I Need a Help.
Im trying to filter my form by placing two Unbound boxes ( DateFrom and DateTo) I applied following VBA codes on a Button (CmdSearch) but it did not work. it show a Yellow Highlight on my last line (DoCmd.AppllyFilter task )

Private Sub CmdSearch_Click()

Call search
End Sub

Sub search()
Dim StrCriteria, task As String

Me.Refresh

If IsNull(Me.DateFrom) Or IsNull(Me.DateTo) Then
    MsgBox "please enter the date range", vbInformation, " Date Range Required"
    Me.DateFrom.SetFocus
Else
   StrCriteria = "([Date_Due] >= #" & Me.DateFrom & "# And [Date_Due] <=#" & Me.DateTo & "#)"
   task = "Select *from Forecast Where (" & StrCriteria & ") order by [Date_Due]"
   DoCmd.ApplyFilter task
End If

End Sub

 

[theDBguy]

Hi. Try taking out everything before and including WHERE, and simply use the criteria.

 

[aftab1965]

Sorry, I failed to understand your point. Can you please please elaborate your reply by showing codes

 

[bob fitz]

Try:
StrCriteria = "(Format("[Date_Due]", "mm/dd/yyyy") >= #" & Format("Me.DateFrom", "mm/dd/yyyy") & "# And Format("[Date_Due]","mm/dd/yyyy") <=#" & Format("Me.DateTo","mm/dd/yyyy") & "#)"

 

[aftab1965]

Thanks, I'll try.

 

[Isaac]

Select *from Forecast Where (" & StrCriteria & ") order by [Date_Due]

Do you need a space after * ?

 

[aftab1965]

Try:
StrCriteria = "(Format("[Date_Due]", "mm/dd/yyyy") >= #" & Format("Me.DateFrom", "mm/dd/yyyy") & "# And Format("[Date_Due]","mm/dd/yyyy") <=#" & Format("Me.DateTo","mm/dd/yyyy") & "#)"

Sorry Bob! It doesn't work, has some issues with syntax as line goes RED

 

[Gasman]

I would have thought it would be along the lines of ?

DoCmd.ApplyFilter strCriteria

 

[Gasman]

Sorry Bob! It doesn't work, has some issues with syntax as line goes RED

Control names should not be enclosed in quotes?

Plus it appears it needs a comma?

DoCmd.ApplyFilter method (Access)

Office VBA reference topic

docs.microsoft.com

 

[aftab1965]

Here is my db attaced.
Sir, if you have time plz check into this. TIA

 

[bob fitz]

Does this work for you

 

[arnelgp]

this two are same:

1. DoCmd.ApplyFilter task

2. Me.Filter = task
Me.FilterOn = True

 

[aftab1965]

Thanks friends.
I'll check

 

[bob fitz]

Thanks friends.
I'll check

I've just noticed that you'll need to add Date_Due to the form's "Order By" setting

 

[aftab1965]

I've just noticed that you'll need to add Date_Due to the form's "Order By" setting

Dear Bob Fitz

Thanks for your time and taking interest.
I tried your attched db and added Date_Due to the form "Order By" Setting but unfortunately it does not work.
Attached is the image of error.

 

[bob fitz]

Dear Bob Fitz

Thanks for your time and taking interest.
I tried your attched db and added Date_Due to the form "Order By" Setting but unfortunately it does not work.
Attached is the image of error.

It worked for me but try this:

DoCmd.ApplyFilter task
'Me.Filter = task
'Me.FilterOn = True

 

[aftab1965]

Here is an interesting happening.
I changed my system date format to mm/dd/yyyy. Now it work.
But I'm using date format as DD. MM.YYYY in my region.
SO, what should I do now with my db.
Thanks for your time

 

[aftab1965]

I changed date formate in VBA, Form field and System to dd-mmm-yy and now is working.

It may not allow if any of above setting is different.
Thanks everyone and especially to Bob fitz