cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
wmews
Level: Powered On

Using date from Date Picker to lookup a record

I would like to use Date Picker to select a date and then use that date to pull a record from my Excel sheet.  I am having all kinds of problems trying to get it to pull off the date.  I can manually modify my lookup function to pull on a different field in the record but for this app I need to use date.  I am either getting a mismatch error (date vs text) or it's not finding a record.  I think when I have the date/text issue resolved, the problem is that the format of the date field from datepicker doesn't match the date format in my file.  I'm getting really frustrated trying to match them up and get it to work.

 

Help!???

1 ACCEPTED SOLUTION

Accepted Solutions
Meneghino
Level 10

Re: Using date from Date Picker to lookup a record

OK, that is because "1/2/2017" is indeed a text and not a date.  This means that WorkDate is a also a text.

This is fine, so just substitute the lookup to compare texts as I mentioned in my previous post:

 

LookUp(Schedule,WorkDate=Text(DatePicker1.SelectedDate, "m/d/yyyy")

You can test this by placing a text box and setting its Text property to

Text(DatePicker1.SelectedDate, "m/d/yyyy")

And seeing how this changes when you change the date pricker date.

 

I don't really like this because the format d/m/yyyy is not as robust as yyyy-mm-dd but I guess it is closer to what is used in your locale.

View solution in original post

4 REPLIES 4
Meneghino
Level 10

Re: Using date from Date Picker to lookup a record

Hi, dates are a bit messy because or formatting and because of time zones.

In regards to time zones, for example see here.

 

Frankly the best way, if you need to compare two dates (i.e. without times) is to turn them both to text in the form "yyyy-mm-dd" and then compare.  This way you resolve the issue of timezones and immediately see any problems.  For example, say I have two dates (that are really date variables) Date1 and Date2, then you can do this to compare them

 

Text(Date1, "yyyy-mm-dd") = Text(Date2, "yyyy-mm-dd")

 

And you can use this epression in your filters.

 

To see if this works the way it is intented with a data source that has a date column, then create a text box and set its property to

Text(First(MyDataSource).MyDateColumn, "yyyy-mm-dd")

This way you see if dates are a day off because of tie zones, or if what you thought was a date column is actually text or something else.

 

I hope this helps

Meneghino
Level 10

Re: Using date from Date Picker to lookup a record

PS If you use a date picker you can also use the DateTimeZone property, which is set to local by default.  However, you can get tied in knots if you do this.

wmews
Level: Powered On

Re: Using date from Date Picker to lookup a record

Thanks for the quick reply.  I set up 2 text fields and display both the data from my Excel file and the value from Date Picker.  They are a match from what I can tell (same format and value).  The problem is when I try to do the lookup.  If I do my lookup like the following....

 

LookUp(Schedule,WorkDate="1/2/2017")

 

it pulls the record in and is fine.  If I try to change it to use the variable like this....

 

LookUp(Schedule,WorkDate=DatePicker1.SelectedDate)

 

it tells me that they values being compared are a mismatch (left is text and right is date.  I have tried changing the format in my Excel file to be date and the error goes away but it doesn't match anything.  I also noticed that if I change the format to Date in my Excel file, nothing shows up in my test text field where I'm trying to compare the file to the date picker value.

 

 

Meneghino
Level 10

Re: Using date from Date Picker to lookup a record

OK, that is because "1/2/2017" is indeed a text and not a date.  This means that WorkDate is a also a text.

This is fine, so just substitute the lookup to compare texts as I mentioned in my previous post:

 

LookUp(Schedule,WorkDate=Text(DatePicker1.SelectedDate, "m/d/yyyy")

You can test this by placing a text box and setting its Text property to

Text(DatePicker1.SelectedDate, "m/d/yyyy")

And seeing how this changes when you change the date pricker date.

 

I don't really like this because the format d/m/yyyy is not as robust as yyyy-mm-dd but I guess it is closer to what is used in your locale.

View solution in original post

Helpful resources

Announcements
thirdimage

Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

sixthImage

Power Platform World Tour

Find out where you can attend!

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

Top Kudoed Authors (Last 30 Days)
Users online (4,961)