3 Replies Latest reply on May 29, 2012 9:43 PM by philmodjunk

    Searching (between) dates entered into a text field



      Searching (between) dates entered into a text field


       Hi I have a database that users are entering dates DD/MM/YYYY into one of the text fields. Is there any way to search this text fieldusing find mode to retrieve records between two entered dates: Eg. All records between 1/1/1880 and 31/12/1890 Keeping in mind it's a text field.  Also, changing the field to a date field is unfortunately not an option.

        • 1. Re: Searching (between) dates entered into a text field

          You'll need to tell us why it is "not an option". There's a reason for using date fields for dates instead of text and performing find for a specified date or all dates in a specified range is one of those reasons.

          I suspect that we can come up with a restructured approach that puts those dates into date fields, but still gives you what you need in that text field. (Another really big reason for not entering dates into a text field is that it becomes possible to enter impossible dates such as 31/02/2012.)

          • 2. Re: Searching (between) dates entered into a text field
            The option to use a text feild is that there are some invalid and incomplete dates being entered. The database keeps Research, some research records dating back to the 1700s and 1800s and date formats and info vary. Dates have been entered as '1886' only, or 'May 1886', '5/1886', '1st August 1785', ' even 'Not Known'. Each user has their own date format preference in line with thier research requirements. The other reason is there are a large number of current users of the database, with records totaling over 3000. Too late to change?
            • 3. Re: Searching (between) dates entered into a text field

              Not too late to change but there is a need to describe what type of searches of this data you want to do.

              If these are two separate text fields, a calculation field can be defined that translates any valid, but incomplete dates into an actual date. The large number of date format preferences will make this very difficult to produce dates that work for all users.

              I strongly recommend you transition to an inteface that requires users to enter valid data in a consistent format.

              One example would be three text fields where the users enter month, day and year in separate text fields. A year would be required, but month and day fields could be left blank.