Using date from Date Picker to lookup a record

[Anonymous]

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!???

[Meneghino]

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]

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.

[Anonymous]

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 · Accepted solution]

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.