Does Base allow for any ambiguity in a form's "Date" field?

[LeipzigBaseUser]

Hello -- I’m compiling notes from archival work, and I’m using Base to classify each individual document that I read. I’m having a problem with the “Date” field in my database’s form.

Some documents have complete dates (e.g., July 19, 1945), others do not. Sometimes I can place the specific date within a range of several days (“July 19-24, 1945”), other times just the month and year (July 1945).

The problem is this: I’ve chosen the “standard long” format for the date in my form: “Thursday, July 19, 1945.” But the machine isn’t letting me type approximate dates such as “July 19-24, 1945” or “July 1945.” If I do, it defaults to the date of the document that happens to have RecordID (the primary key) no. 1.

I could reclassify the field as text, but I’m concerned that several months from now, when I’m done entering data and will use queries to read and analyze it, labeling the field as text will limit the ways that I can organize and classify the data.

How can I set up this field so that it has the cataloging advantages of a “date” but with the flexibility of “text”?

I’d be grateful for someone’s guidance. Thank you!

[Villeroy]

The way how the characters are translated to a date depends on the locale setting in the language options and the setting for 2-digit year numbers in the general options.
The output format can be set locale independent in the form control properties.
With a US locale a sequence of 3 numbers "1-2-30" gives January 2nd 1930, most other locales give 1st of February 1930. With 30 as the base of the 2-digit year, year number 29 gives a future date in 2029.
Likewise you have to type the month before the day under the US locale and the other way round with most other locales.
In European locale context it has to be "1 Feb 30" rather than "Feb 1 30".
In a spreadsheet cell you can leave out the year number and get a date in the current year. The date control seems to be more restrictive. It insists on all 3 parts:
- a day number (1 to max. 31)
- a month number (1 to 12), short name or full name (case sensitively as it seems, "feb" or "august" do not work in German nor English)
- a year number with 4 digits or 2 digits or one digit (3 digits seem to be invalid)

Invlaid input falls back to the last entered valid input, it seems.

When using date control I use to type 3 numbers for day/month/year on the num-pad of my keyboard with / or - as separator. Space and dot work as well.

I do not understand the "Strict format" option in the date field properties. It makes no difference for me. The following is obviously not true since I can type dates with month names.

If the strict format function is activated (Yes), only the allowed characters are accepted. For example, in a date field, only numbers or date delimiters are accepted; all alphabet entries typed with your keyboard are ignored.

You could try 3 fields for 3 integer numbers. A required year number and 2 others with default value 1. Time spans have to be represented by 2 values. Text is not an option when you want to extract any information.

[Piatkow]

I have yet to come across a DBMS that is fully ISO8601(international standard for date and time) compliant, this isn't an issue specific to Open Office.

I think that what you need is separate start and end dates, where a single date will have the same date as start and end.

[LeipzigBaseUser]

Thanks for these responses!

[MikeytheMagnificent]

Why not replace your date textbox with a calendar widget and pencil in an exact date (which might be wrong) together with a 'provisional' BOOLEAN' tickbox for later editing

[Villeroy]

Why not replace your date textbox with a calendar widget and pencil in an exact date (which might be wrong) together with a 'provisional' BOOLEAN' tickbox for later editing

Or use a comment field for arbitrary text comments ("possibly in the year 1948") together with an optional date field where you enter a point of time if you are sure about the date. For the representation of time spans a second date field is required.

Having a pair of date fields "From" and "Until" it is possible to restrict the second date like this:
menu:Tools>SQL...
ALTER TABLE "Table1" ADD CONSTRAINT "From_before_Until" CHECK("From"<="Until");
[Execute]
menu:View>Refresh Tables
Now you can not enter "Until" dates being greater than "From" dates.