Odd Behavior of a Date Field

[Rob G]

FMP6: I have a file with many records, each with a value in a date field. The dates are all entered numerically (eg. 1/23/2005) though they are not set to display that way. When I do a search for some of them, I get no found records... even if in browse mode I copy the contents of the date field and then paste it back in find mode... still no results. Anyone ever come across this before?

[aaa]

Try change "Use sysytem format" option.

[bikergeek]

I use a simple date search system consisting of three calculation fields for each date field, and a script which runs when you click in the date field in find mode. The script presents a dialog referencing the three calculation fields, and you enter a year, a month [1-12], and/or a day [1-31] to find all records which match the entered numbers.

 

This method is far more powerful than searching using the date field directly.

 

year.c = Year ( datefield )

month.c = Month ( datefield )

day.c = Day ( datefield )

 

If [ Get ( WindowMode ) = 1 ]

Show Custom Dialog [ Title: "Find Records by Year, Month and/or Day"; Message: " •Enter a year to return all records for that year.¶ •Enter the number of a month to return all records for that month in all years.¶ •Enter the number of a day of the month to return all records for that day of the month in all months and in all years.¶ •Entering more than one of Year, Month or Day will only return records for which those criteria overlap.¶ •To cancel these search criteria, simply leave the fields below blank."; Buttons: "OK", "Cancel"; Input #1: DATABASE::year.c, "Year"; Input #2: DATABASE::month.c, "Month [1-12]"; Input #3: DATABASE::day.c, "Day [1-31]" ]

Go to Field [ DATABASE::datefield ] [ Select/perform ]

Else

Go to Field [ DATABASE::datefield ] [ Select/perform ]

End If

 

The script does not perform the search, so that the user can enter additional criteria. Also, the script ends with the text entry point in the clicked-on date field for consistency with the expected behavior, especially when not in Find mode.

[Rob G]

Turned out to be a bit of strangeness caused by importing the records from another file with "keep formatting" selected. The import caused the dates to be entered as "Fri, Jan 1/2005" (text) even though it was a date field. I discovered this and changed the field to a text field, then performed a series of scripts to convert all records back to the 01/01/2005 format... then changed the field back to a date field. While everything looked OK (including the date formatting for the field recognizing the data and presenting the correct formatted verions) the find process did not work any more.

I solved it by running a script that did this: TexttoDate(DatetoText(Date)). Now all is good again... twisted!!