AUTHOR'S MAIN SITE   > > > > >   TABLE OF CONTENTS for Open Office database tutorials. Delicious.Com Bookmark this on Delicious    StumbleUpon.Com Recommend to StumbleUpon


Open Office
Using dates to locate records

You may find that the database included in OpenOffice delights you as much as it has me. This page tries to help you use it.

Remember that Open Office, including ooBase, is free. Don't let that fool you, though. Big organizations, governmental and civilian, have adopted it as their standard office suite... and saving million$, but still Getting The Job Done. And making things easy for users on different platforms... Linux, Mac, Windows all spoken here!

There's more about ooBase in the main index to this material. Adabas? Star Office? Ancient history.

This page is "browser friendly". Make your browser window as wide as you want it. The text will flow nicely for you. It is easier to read in a narrow window. With most browsers, pressing plus, minus or zero while the control key (ctrl) is held down will change the texts size. (Enlarge, reduce, restore to default, respectively.) (This is fully explained, and there are more tips, at my Power Browsing page.)

Page contents © TK Boyd, Sheepdog Software, 2/06-10/12.



Locating records by date...

This "How To" will tell you a bit about finding records in a database by the contents of a field with date type data in it.

Of course, there are many ways to find a set of records. This just shows you a bit of one... but quite a good technique, at least!

Data to play with

We don't need more than one table for what we are going to do. And it doesn't have to be "clever". The following will do....

Table: DaInLives: Dates In Lives of (some) important people
(For now I'll confine my table to dates of deaths. An extra
field could be added to flag a record as a death date or
birth date... but I am trying, for once, to keep things
simple!)

Fields:
ID: (Primary key) Simple auto incrementing integer. See I CAN be simple!
EventDate: data-type "date"
PersName: text. Name of person who died on given date.

Don't be tempted to call that field "Date". Using "everyday" words... especially if they might be in use by Open Office... for names of anything you are created often ends in tears. Make up a new work by combining things that help you remember what the entity is for... like "EventDate" as a name for the field to hold the date of an event in the person's life. (I inadvertently used "Date" during the first pass through this, and things which "shouldn't" have worked, did... sort of. But when I changed the field name to "EventDate", those things just didn't work. (Probably best.) An example of why you don't want to use "ordinary" words for names.

And here's some sample data. (Actual dates, by the way).

ID     EventDate       PersName
 0   5 December 1791  Mozart, WA
 1    28 July 1750    Bach, JS
 2   14 April 1759    Handel, GF
 3   9 August 1996    Whittle, F
 4   28 July 2004     Crick, F

If you want to actually try things as we go along, create that table now, fill it with the sample data. Set the "format example" for the EventDate field to "30 December 1999".

Using the database

While our database may have only 5 records, you can, I trust, imagine it as being much larger? If it were larger, the crude "answers" below would become even more unsatisfactory, wouldn't they? If you only wanted to track five people, you wouldn't need a database!

Let's pretend that you want to know if anyone died on 28 July 1750. Yes, a bit of a coincidence that you "just happened" to want to know about a date on which someone in our database died. Bear with me. We're just getting started.

You could, of course access the table, and scroll around in it. You can even set filters on what is displayed. Bad Idea, though.

A crude answer would be to put the table's data in a form, sort it on the EventDate field, and scroll to July 1750. Pretty crude!

A query would be the next stop "up" from the ill advised alternative of just accessing the table directly or the crude form-based "answer".

Just set up a simple "ad hoc" query, using the query wizard, and just accepting defaults almost everywhere. You do have to tell it which table to query, and what fields to put in the answer. You get a chance to specify some search criteria... but don't be tempted.

When you've got the query roughed out, re-open it in edit mode. Try putting in "28 July 1750" in the "Criterion" row for the "EventDate" column. Doesn't work, does it? Neither with nor without the quote marks. '28 July 1750' will work, though. Or #28 July 1750#. 'July 28 1750' will work, and even 'Jul 28 1750'.

'July 28 1750' will work ("Greater than July 28 1750")

One other thing about date formats. Cast your mind back. Remember we put in a "format example" when we set up the table? That only controls how the data displays when viewed in the table. It doesn't determine which formats are acceptable or inferred when you are entering data. Thanks a lot, computer.

To tell you the truth, because dates are so hinky, I tend to avoid the "date" data-type in the tables I make for myself. I'll either use separate fields for year, month and day, or enter a string based on yymmdd. (But I'm just weak. Real men fight every passing windmill.)

So.. assuming you want to press on with using "date" data-type data...

A little more demanding...

Suppose, for the sake of arguement, we wanted to see all records where the person died in the 1900s? Easy!...

>#31/12/1899# and <#01/01/2000#

A lot more demanding, and the "grand finale" of this "How To"...

Not so easy. Suppose now that you want: "All the people who've died in the past 50 years."

What we need is something like ">Now-50 years"

The ">" for "greater than" is easy enough. The problems lie in the "Now" and the "arithmetic".

The "Now" is pretty easy to fix. Change one of the records in the table to show the man dying today. (Be sure to move the cursor out of the record's line, so that the change is written to the database.) Make the criterion for displaying a record say Current_Date. The person recorded as dying today should be returned. If you run the same query tomorrow, without changing again the person's date of death, that person will no longer be part of the results of the query.

So! It might seem that ">(Current_Date-50 years)" in the criterion row is what we need.

However: you won't get that to work, no matter how you fiddle with all the things I fiddled with for an hour. Here's what you do instead to get what you want....

First you need to add an extra field to the report.

Try something nice and simple first. (My tutorial Putting Calculated Fields on Forms may be helpful, if you don't get the following working easily.

For the "something simple", I'm going to suggest that you add a column to the query that will display the ID of the record, multipled by three. All you need to do is to type... very carefully, matching case in respect of field names...

DaInLives.ID*3

... into the "Field" row of the query designer. (You can use the pull-down to accurately type the "DaInLives.ID") Make sure that the "Visible" box is ticked... it will probably tick itself as you leave the "Field" box... and see that the query works. (Remove the "Current_Date" you had in the EventDate column, won't you?)

Nearly there!

You should see....

ID	EventDate	PersName	"DaInLives"."ID" * 3
1	05/12/91	Mozart, WA	3
2	28/07/50	Bach, JS	6
3	14/04/59	Handel, GF	9
4	09/08/96	Whittle, F	12
5	25/10/12	Crick, F	15

Now, where you had "DaInLives.ID * 3", enter...

"DateDiff('yy',DaInLives.EventDate,Current_Date)"

What that says is: In this column, for each record, work out the difference in date, in years ('yy'), between the date in the EventDate field (of this record) and today's date (Current_Date)"

A bit long-winded... but marvelous! Isn't it? (I'm speaking of the expression, not the tutorial, by the way. Also by the way... if not needed to eliminate ambiguity, which won't arise in a one table database, you can leave out the "DaInLives.".

And now, if you just want people who have died in the past 50 years, add "<50" in the criterion row for this column of the query design.

Easy when you know how.

Of course, you probably don't care exactly how many years ago the person in question died, so un-tick the "visible" box. The work will still be done, the selection of records will still take place... your users just won't see the column with the figures used to see how long ago the person died.

Concluding remarks...

We were able to say things like "Records where the date of the event was less than 1/1/1900". All we had to do was put "<1/1/1900" in the criterion row under the field concerned.

Why couldn't we, in the same manner, say "less than 50 years before today"? Because we didn't have the years before today" number.... but we were able (easily) to get it! This kind of "trick" will be useful in many situations, not just in cases involving dates.





Editorial Philosophy

I dislike 'fancy' websites more concerned with a flashy appearance than for good content. For a pretty picture, I can go to an art gallery. Not everyone has fast broadband.

I present this material in a format aimed at to helping you USE it. There are two aspects to that: The way it is split up, and the way it is posted. Details at my page about how the material is split up and how it is posted.

Please remember the material is copyright. (TK Boyd, 2006 and later) The procedures in the page just cited are suggested only for convenient personal use of the material, however, also....

Feel free to use this information in computer courses, etc, but a credit of the source, quoting the URL, would be appreciated. If you simply copy the pages to other web pages you will do your readers a disservice: Your copies won't stay current. Far better to link to the original pages, and then your readers will see up-to-date versions. For those who care- thank you. I have posted a page with more information on what copyright waivers I extend, and suggestions for those who wish to put this material on CDs, etc. (There is at least one prison using the material for inmate education. Situations do exist where good internet connections are not possible!)

Translations are welcomed. Tell me about yours, so I can post links to it. (More information at the page about copyright waivers.)


Ad from page's editor: Yes.. I do enjoy compiling these things for you... I hope they are helpful. However.. this doesn't pay my bills!!! If you find this stuff useful, (and you run an MS-DOS or Windows PC) please visit my freeware and shareware page, download something, and circulate it for me? Links on your page to this page would also be appreciated!

PLEASE >>> Click here to visit editor's Sheepdog Software (tm) freeware, shareware pages <<< PLEASE


If you liked this ooBase tutorial, see the main index for information other help from the same author.

Editor's email address. Suggestions welcomed!     - - -    Want a site hosted, or email? I like 1&1's services.




Valid HTML 4.01 Transitional Page tested for compliance with INDUSTRY (not MS-only) standards, using the free, publicly accessible validator at validator.w3.orgMostly passes. There were two "unknown attributes" in Google+ button code. Sigh.


One last bit of advice: Be sure you know all you need to about spyware.

. . . . . P a g e . . . E n d s . . . . .