Search by date cause issue in VBA code

[siamandm]

Hello All,
i have this code which was working fine before adding the last line to search by date

Code: Select all

Dim sql As String

sql = "SELECT qrySearch.* FROM qrySearch " _
& " Where FirstName Like '*" & Me.txtSearch & "*'" _
& " OR SecondName Like '*" & Me.txtSearch & "*'" _
& " OR ThirdName Like '*" & Me.txtSearch & "*'" _
& " OR FirstName_Mother Like '*" & Me.txtSearch & "*'" _
'& " OR Assistant_Date Like #" & Format(Me.txtSearch, "mm/dd/yyyy") & "#"

Me.sbf_SearchData.Form.RecordSource = sql
Me.sbf_SearchData.Form.Requery

what could be the issue please

Regards

[HansV]

This will cause an error if the user enters anything other than a date in txtSearch.
If you want to be able to search for part of a date, use

Code: Select all

...
'& " OR Format(Assistant_Date, ""dd/mm/yyyy"") Like '" & Me.txtSearch & "'"
...

Replace dd/mm/yyyy with your preferred date format.

[siamandm]

thanks a lot for your quick reply

its working but i notice something , if there is a date like 4/05/2020 in my query when i put 4/5/2020 its not working when i put 4/05/2020 then there is a result !
why is that please

Regards

[siamandm]

i have changed the format for the Date service to dd/m/yyyy now is working if this is the right way to solve this issue

thanks a lot.