Solved Date Range Error (1 Viewer)

aftab1965

Registered User.
Local time
Today, 11:53
Joined
Jan 12, 2016
Messages
48
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 )
Code:
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

I’m here to help
Staff member
Local time
Today, 00:53
Joined
Oct 29, 2018
Messages
21,358
Hi. Try taking out everything before and including WHERE, and simply use the criteria.
 

aftab1965

Registered User.
Local time
Today, 11:53
Joined
Jan 12, 2016
Messages
48
Sorry, I failed to understand your point. Can you please please elaborate your reply by showing codes
 

bob fitz

AWF VIP
Local time
Today, 07:53
Joined
May 23, 2011
Messages
4,717
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") & "#)"
 

Isaac

Lifelong Learner
Local time
Today, 00:53
Joined
Mar 14, 2017
Messages
8,738
Select *from Forecast Where (" & StrCriteria & ") order by [Date_Due]

Do you need a space after * ?
 

aftab1965

Registered User.
Local time
Today, 11:53
Joined
Jan 12, 2016
Messages
48
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

Enthusiastic Amateur
Local time
Today, 07:53
Joined
Sep 21, 2011
Messages
14,050
I would have thought it would be along the lines of ?

Code:
DoCmd.ApplyFilter strCriteria
 

aftab1965

Registered User.
Local time
Today, 11:53
Joined
Jan 12, 2016
Messages
48
Here is my db attaced.
Sir, if you have time plz check into this. TIA
 

Attachments

  • DateRange.zip
    96.4 KB · Views: 270

bob fitz

AWF VIP
Local time
Today, 07:53
Joined
May 23, 2011
Messages
4,717
Does this work for you
 

Attachments

  • DateRangeBob02.accdb
    856 KB · Views: 265

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 15:53
Joined
May 7, 2009
Messages
19,169
this two are same:

1. DoCmd.ApplyFilter task

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

aftab1965

Registered User.
Local time
Today, 11:53
Joined
Jan 12, 2016
Messages
48
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.
 

Attachments

  • ERROR.jpg
    ERROR.jpg
    346.2 KB · Views: 261

bob fitz

AWF VIP
Local time
Today, 07:53
Joined
May 23, 2011
Messages
4,717
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

Registered User.
Local time
Today, 11:53
Joined
Jan 12, 2016
Messages
48
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

Registered User.
Local time
Today, 11:53
Joined
Jan 12, 2016
Messages
48
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
 

Users who are viewing this thread

Top Bottom