[Solved] Dates

[Fao]

Does anyone know how to add dates, other than the current date, to a date field?

[UnklDonald418]

Your question is a little too vague to know exactly what you need.
You can type a date into any Date field.
You can add a date picker (Calendar) to a Date control on a Form by selecting Yes for the Dropdown property.
If you want to set a default date on an existing column execute a command something like

Code: Select all

ALTER TABLE "YourTableName" ALTER COLUMN "YourDateColumnName" SET DEFAULT '2012-01-05';

in Tools->SQL
Or add a new date column

Code: Select all

ALTER TABLE "YourTableName" ADD COLUMN "NewDateName" DATE DEFAULT '1999-12-05';

[Fao]

Thanks for that reply, I am trying to use your reply and that of another user to what I posted regarding this issue yesterday (that post was much more detailed - no one replied until a few minutes ago). I am not seeing a way to add a date picker, I am trying by loading forms, right clicking on that form and choosing control; I also tried by starting with the corresponding table, which I used to create the db in the first place, right clicking on the table and selecting edit (I do get the options I got when I was creating the table yesterday - all three date fields are set to "date" for the field type, and down below I have nothing entered into the default value box) . Yesterday I mentioned in my post that it seems to automatically enter the current date, that is not exactly right, it enters a specific date (which happens to be yesterday's date) regardless of the date when I enter new data, in a new record. It's as if all date fields are set to enter yesterday's date (in a format other than what I chose, in table view), for every date field in every record of the db.

[UnklDonald418]

First check the Date control on your form. In the Edit/Design mode right click on the Date box and select Control to open the Properties: Date Field dialog. On the General tab make sure there is nothing in the Default date property. If you scroll down the list there is a Dropdown property, if you select Yes it will add a small down arrow to your control that can be used to access the calendar. If you have made any changes be sure to save your edited form before returning to the live mode.

I have encountered the situation where there is default date set that doesn't show up in the table design GUI. In that case, edit and execute the following command in Tools->SQL

Code: Select all

ALTER TABLE "YourTableName" ALTER COLUMN "YourDateColumnName" DROP DEFAULT;

it should respond with "Command successfully executed".
To be safe, select Tables in the main database window and then select View->Refresh Tables.

If none of that helps then perhaps you should upload a sample database so someone can try to resolve the issue.
[Forum] How to attach a document here

[Fao]

I would think that checking the default value of the field in the way you suggest above would be the same as doing so from the main db screen, where you can choose either tables or forms (and two other choices that I have never used). I selected tables then right clicked on the one table that is there (I have one table and one form, they have different names, but I'm pretty sure they are connected to the same db). I would try it your way anyway, but I can't see a way to get into this area you mention. What I am doing is, in the main screen i click on "Forms" in the L pane, then right click on the only form on the R and selecting edit; this brings up that blue grid with a bunch of squares formed by a perforated white line, with green squares around each field (so you can manually move/resize them) and otherwise looks more or less like the db in normal mode; when I right click on any date field (or any other field, for that matter) then select "control" from that list, I do get a Properties box but it is not specific to the (date) field that I right clicked on, the properties box is called "Multiselection" (obviously there is no default value box in there, since that setting could be different for different fields). I'm not sure if I'm doing something wrong or, more likely, this is just another bizarro thing the program is doing, but if I am doing something wrong then how do I get to the properties of one specific field? And should't that setting (nothing entered into "default value") be the same in forms control as it is in the table edit view?

[Villeroy]

If a date control on a form does not recognize your input as a valid date, it will either put the last stored date value or the current date. If your input is recognized or not depends entirely on the global locale setting.

[UnklDonald418]

I responded to some of the issues you describe above in your other thread
Variable Dates

Another tool that is very useful when designing forms is the Form Navigator. Open the form document in the Edit/Design mode and look on the Form Design toolbar located along the bottom of the window. The Form Navigator is usually the 4th from the left. When you click on the Form Navigator a dialog will pop up displaying all the Forms and Controls and also the structure of your form document. Click on a control in the Form Navigator and the control will be highlighted on your form document. Click on a Form and a group of controls that are connected to that form will be surrounded by the green handles. If the Properties dialog isn't already open, right click on that Form and select Properties. Select the Data tab to see the data source for the selected Form.

You should also be aware that Base is a front end for a database engine. When using the Embedded database model in Base the engine is HSQLDB version 1.8. The database engine has a limited set of formatting options, and default values is one of those.
Base as a front end is used to add many more formatting options beyond those offered by HSQLDB. There is some overlap such as default value. So in theory HSQLDB could contain one default value and the Form document could display a different default value. The ALTER TABLE commands I posted earlier work on the database engine side of things, while the Properties dialogs work on the Base side.

[Villeroy]

The embedded database in the attached document includes a table with a date and a text column.
The text column is set up with a default value "BLAH".
The date column is set up with the current date as default value.
When you enter a text only to a new record, the date column gets todays date.
When you enter a date only to a new record, the text column gets "BLAH".

The input form provides 5 different form controls linked to the same date column and one text box for the text column.
More details are explained on the form.