Can someone see where I am going wrong in my Let() function. I have a find function that is working nicely, except for dates, so I have added in a catch to make sure that the date is formatted correctly. Users can use either a "." or "/" to space the dates, and either enter 17 or 2017 for the year. (Side Note: my date settings are set to dd/mm/yyyy as I am in South Africa)
Statement in Data Viewer:
$query = "28.11.2017" ;
$query = Substitute ( $query ; ["." ; ¶ ] ; [ "/" ; ¶ ] ) ;
~day = GetValue ( $query ; 1 ) ;
~month = GetValue ( $query ; 2 ) ;
~year = GetValue ( $query ; 3 ) ;
~year = Right ( ~year ; 2 ) ;
~year = "20" & ~year
"GetAsDate: " &GetAsDate ( ~day & "/" & ~month & "/" & ~year ) & ¶ &
"Date: " & Date ( ~month ; ~day ; ~year ) & ¶ &
"Day: " & ~day & ¶ &
"Month: " & ~month & ¶ &
"Year: " & ~year
I am playing around with the function in data viewer to see what results I get, but things don't seem to add up. If I change the $query to "8.11.2017" then the GetAsDate() works fine, until I go to day 13, then the ? comes back. I added the GetAsDate() to see if I could manipulate the date to work, but the main confusion is the Date() function. I was under the impression that FM would give me back the date as Date( 11 ; 28 ; 2017 ) but because my machine is set to dd/mm/yyyy I would see 28/11/2017 but I am getting 11/28/2017 back which is not correct.
In searching for answers, I came across this post, https://community.filemaker.com/message/615912?commentID=615912#comment-615912 and I tried both Johan and Tom's and they worked perfectly, and they also used the Date (month ; day ; year) like I did, but I kept getting incorrect dates.
UPDATE: I have to add, I decided to just run my script to see what the results would be, and even thou in data viewer the date would show as mm/dd/yyyy, when the value was entered into the field it went in as dd/mm/yyyy. So my find request worked, but it was just strange that in data viewer it was showing incorrectly.