Solved Date Range Error (1 Viewer)

aftab1965

Registered User.
Local time
Today, 13:18
Joined
Jan 12, 2016
Messages
44
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, 02:18
Joined
Oct 29, 2018
Messages
11,198
Hi. Try taking out everything before and including WHERE, and simply use the criteria.
 

aftab1965

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

bob fitz

AWF VIP
Local time
Today, 10:18
Joined
May 23, 2011
Messages
3,972
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

Well-known member
Local time
Today, 02:18
Joined
Mar 14, 2017
Messages
1,513
Select *from Forecast Where (" & StrCriteria & ") order by [Date_Due]

Do you need a space after * ?
 

aftab1965

Registered User.
Local time
Today, 13:18
Joined
Jan 12, 2016
Messages
44
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, 10:18
Joined
Sep 21, 2011
Messages
6,356
I would have thought it would be along the lines of ?

Code:
DoCmd.ApplyFilter strCriteria
 

arnelgp

error reading drive A:
Local time
Today, 17:18
Joined
May 7, 2009
Messages
9,921
this two are same:

1. DoCmd.ApplyFilter task

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

aftab1965

Registered User.
Local time
Today, 13:18
Joined
Jan 12, 2016
Messages
44
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

bob fitz

AWF VIP
Local time
Today, 10:18
Joined
May 23, 2011
Messages
3,972
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, 13:18
Joined
Jan 12, 2016
Messages
44
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, 13:18
Joined
Jan 12, 2016
Messages
44
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 (Users: 0, Guests: 1)

Top Bottom