We help IT Professionals succeed at work.

Filter Date on Split Form

Kamlesh Jain
Kamlesh Jain asked
on
98 Views
Last Modified: 2017-03-29
Can anybody help whats wrong with this code ?

It considers date format sometime as DD/MM/YY and sometime MM/DD/YY... i am bit confused.

Private Sub cmdSearchDatewise_Click()

Dim strCriteria, task As String

Me.Refresh
    If IsNull(Me.PoDateFrom) Or IsNull(Me.PODateTo) Then
    MsgBox "Please Enter PO Date from ", vbInformation, "Date Range Required"
    Me.PoDateFrom.SetFocus
   
Else
    strCriteria = "([Lift_date] >= #" & Me.PoDateFrom & "# And [Lift_date] <= #" & Me.PODateTo & "#)"
     task = "select * from tbllifting where (" & strCriteria & ") order by [Lift_date]"
 
    DoCmd.ApplyFilter task
   
   
End If

End Sub

e.g. :
When i put 20-03-2017 to 23-03-17 as date range, it shows proper filtered records. Fine.
But when i put 01-03-2017 to 23-03-17 as date range (PS : want details from 1st March to 23 March), it shows records from 3rd Jan 17 onwards.

Can anybody help Please ?

Thanks
Comment
Watch Question

Ryan ChongSoftware Tead Lead / Business Analyst / System Analyst / Data Engineer / Applications Consultant
CERTIFIED EXPERT

Commented:
for date comparison in SQL, always use format such as MM/DD/YYYY or YYYY/MM/DD

so if you got text value such as 01-03-2017 (1 Mar 2017), you need to format it to "03-01-2017"
Kamlesh JainManager

Author

Commented:
thanks for the response.
So what shall i change ? Date format in database ? or on the form ? or shall i use format() function in VBA code ?
To be frank, we , in india are used to DD/MM/YYYY format only so preferably, i would not like to change user format.

Line of suggession shall be awaited.
Thanks
Ryan ChongSoftware Tead Lead / Business Analyst / System Analyst / Data Engineer / Applications Consultant
CERTIFIED EXPERT

Commented:
So what shall i change ? Date format in database ? or on the form ? or shall i use format() function in VBA code ?
definitely not the Date Format in database, it got no effect to the SQL results.

on the form, you still can keep the format as dd/mm/yyyy.

yea, try use Format to format your text field value from dd/mm/yyyy to mm/dd/yyyy in your SQL statement will do.
Ryan ChongSoftware Tead Lead / Business Analyst / System Analyst / Data Engineer / Applications Consultant
CERTIFIED EXPERT

Commented:
emmm, sorry... if for exaomple, Me.PoDateFrom is a text field, you need to convert it instead of using format function, like:

strCriteria = "([Lift_date] >= #" & FormatDate(Me.PoDateFrom) & "# And [Lift_date] <= #" & FormatDate(Me.PODateTo) & "#)"

Open in new window


and then add this function:

Function FormatDate(ByVal v As String) As String
    If Len(v) = 10 Then
        FormatDate = Mid(v, 4, 2) & Mid(v, 3, 1) & Left(v, 2) & Mid(v, 3, 1) & Right(v, 4)
    Else
        FormatDate = ""
    End If
End Function

Open in new window


try:

FormatDate("01/03/2017")
and
FormatDate("23/03/2017")
John TsioumprisSoftware & Systems Engineer
CERTIFIED EXPERT
Distinguished Expert 2019

Commented:
Format(TheDateYouWant,"mm/dd/yyyy")

Open in new window

CERTIFIED EXPERT
Most Valuable Expert 2015
Distinguished Expert 2018
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
Kamlesh JainManager

Author

Commented:
Big Thank you Sir... Its working fine.. Thanks entire team for their time and efforts.  :)
Ryan ChongSoftware Tead Lead / Business Analyst / System Analyst / Data Engineer / Applications Consultant
CERTIFIED EXPERT

Commented:
well done.
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.