DatePicker filter not selecting any records for my gallery form

[jwf_2020]

Hello,

I have a Filter form which I want to use to select records for a gallery from.

My gallery form has the following items property

SortByColumns(Search(Filter('[dbo].[View_Tasks]', ProjID = drpProject.Selected.ProjID And StartDate >= dteFilterStartDate ), TextSearchBox1.Text, "Memo"), "Memo", If(SortDescending1, Descending, Ascending))

The first part of the filter works regarding the selected project which the users selects from a drop down list of projects and I have data showing in the gallery. However the date filter does not. This part below.

" And StartDate >= dteFilterStartDate "

There is no error but no records are returned

The datepicker field is dteFilterStartDate and StartDate is field name in the datasource.   What am I doin

[v-siky-msft]

Hi @jwf_2020 ,

Do you mean dteFilterStartDate  is a Date picker control?

If you want to call the selected date from date picker, you should use DatePicker1.SelectedDate.

Please try this:

SortByColumns(Search(Filter('[dbo].[View_Tasks]', ProjID = drpProject.Selected.ProjID And StartDate >= dteFilterStartDate.SelectedDate ), TextSearchBox1.Text, "Memo"), "Memo", If(SortDescending1, Descending, Ascending))

 Best regards,

Sik

If my post is helpful for you, please click on “Accept as Solution” to help other members find it more quickly.

[jwf_2020]

Thanks Sik,

That syntax generated an error in the formula so it does not work.

Also what is the best way to have a user enter a valid date? The date picker does not let you enter the date manually. Is there a better control for this?

Best Regards,

John

[v-siky-msft · Accepted solution]

Hi @jwf_2020,

Can you show the error information?

If you want to enter the date manually, just use textinput box and Date function to get that.

Create three text input boxes for users to enter Year, Month and Day, then use Date function to combine a date.

Hope this helps.

Best regards,

Sik

If my post is helpful for you, please click on “Accept as Solution” to help other members find it more quickly.

[jwf_2020]

I created an excel data source and used the formula with dtfilterStartDate >= datepicker1.selecteddate and that worked but it would NOT work for my sql data source which is what I really it to do.  So for most people this is the correct solution.

Do you know of any issues that require a different solution for sql server dates?

Thanks for all your help!