OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

[SOLVED]Question about Queries

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Base
View previous topic :: View next topic  
Author Message
MjrTeez
Newbie
Newbie


Joined: 20 Apr 2009
Posts: 2

PostPosted: Mon Apr 20, 2009 10:40 pm    Post subject: [SOLVED]Question about Queries Reply with quote

I created 2 queries. the first is based on all records that have an appointment date
The second is based on records that have the current date (today)
I am having a couple of problems.
1: The All Appointment date record shows all records even if they don't have a date
How do I filter the query so that records w/o an appointment date are not listed??
2: How do I filter the report generated from this query to only show appointments from today forward?

I am totally new to this program and muddling my way through. Any help would be greatly appreciated!!!!!
Kent


Last edited by MjrTeez on Tue Apr 21, 2009 4:55 pm; edited 1 time in total
Back to top
View user's profile Send private message
Sliderule
Super User
Super User


Joined: 29 May 2004
Posts: 2499
Location: 3rd Rock From The Sun

PostPosted: Tue Apr 21, 2009 8:02 am    Post subject: Reply with quote

Kent:

Welcome to the universe of OpenOffice Base.

You have indicated that you are 'new' to this program . . . and . . . I will assume using a database.

Therefore, I would like to recommend . . . you check out the videos ( each one is short, just a few minutes ) showing some very simple uses of OpenOffice and databases. These videos were created by Dai, an OpenOffice user. In my opinion, for a beginner learning something about databases, these video tutorials are very helpful. The website below shows his OpenOffice Video Tutorials . . . and you might be especially interested in No 343 - Creating a Query

http://showmedo.com/videos/series?name=AXggL6j0a

I hope this helps, please be sure to let me / us know.

Sliderule

Thanks to add [Solved] in your first post title (edit button) if your issue has been fixed / resolved.
Back to top
View user's profile Send private message
lithops
Power User
Power User


Joined: 10 Jan 2008
Posts: 51
Location: Transplanted Brit residing in Maine

PostPosted: Tue Apr 21, 2009 12:15 pm    Post subject: Reply with quote

Try:

1. <> 0

2. > Today()

in the date field of the criterion part of the query.
_________________
All the best,
lithops
Back to top
View user's profile Send private message
Sliderule
Super User
Super User


Joined: 29 May 2004
Posts: 2499
Location: 3rd Rock From The Sun

PostPosted: Tue Apr 21, 2009 1:03 pm    Post subject: Reply with quote

Just as an FYI ( For Your Information ) . . . if you are using the 'default' database engine ( HSQL ) in OpenOffice Base ( you can confirm this by looking at the status line - if it says HSQL database engine ) . . . the available 'functions' can be found at either:
  1. http://www.hsqldb.org/doc/guide/ch09.html#N1251E
  2. http://wiki.services.openoffice.org/wiki/Built-in_functions_and_Stored_Procedures

Therefore . . . TODAY() is NOT a valid function. But, the following are available:
  1. CURRENT_DATE - Returns the current date CURRENT_DATE = 09/01/07
  2. CURRENT_TIME - Returns the current time CURRENT_TIME = 10:44:28 PM
  3. CURRENT_TIMESTAMP - Returns the current timestamp CURRENT_TIMESTAMP = 09/01/07 10:34 PM
  4. CURDATE() - Returns the current date. This is the current system date on your PC. CURDATE() = 09/01/07 NOTE - The parenthesize are required.
  5. CURTIME() - Returns the current time CURRENT_TIME = 10:44:28 PM NOTE - The parenthesize are required.
  6. NOW() - Returns the current date and time as a timestamp use of CURRENT_TIMESTAMP instead is suggested NOW() = 09/01/07 10:34 PM

I hope this helps, please be sure to let me / us know.

Sliderule

Thanks to add [Solved] in your first post title (edit button) if your issue has been fixed / resolved.
Back to top
View user's profile Send private message
MjrTeez
Newbie
Newbie


Joined: 20 Apr 2009
Posts: 2

PostPosted: Tue Apr 21, 2009 4:54 pm    Post subject: Thanks for all your help!!! Reply with quote

I am new to Base but not databases, though I will admit I am a little lost, the last time I used Access was 1998. I fooled around with CURTIME() and then came across another post with an equation for date diff. Worked like a champ after I adapted it slightly.
Now only current records with a appointment show up. today and forward 300 days in my report.
The statement is this.
WHERE "Appointment" >= CURDATE( ) AND DATEDIFF( 'dd', CURDATE( ), "Appointment" ) <= 300

Thanks again!!

Now if I could figure out how to make a weekly and or monthly Calendar layout report that would be trick.
Back to top
View user's profile Send private message
Sliderule
Super User
Super User


Joined: 29 May 2004
Posts: 2499
Location: 3rd Rock From The Sun

PostPosted: Tue Apr 21, 2009 5:48 pm    Post subject: Reply with quote

Kent:

I appreciate that you have marked this item as SOLVED, for others looking at a similar question / solution. Thank-you.

You did add, in your post above:

Kent wrote:
Now if I could figure out how to make a weekly and or monthly Calendar layout report that would be trick.

Just as an FYI . . . in the two links I gave above for HSQL functions . . . a few that might be of interest to help with the 'grouping' in your report . . .

MONTH("Appointment") - returns the month (1-12)
WEEK("Appointment") - returns the week of this year (1-53)
DAYOFWEEK("Appointment") - returns the day of the week (1 means Sunday)

I hope this helps, and, continued success in using OpenOffice Base.

Sliderule
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Base All times are GMT - 8 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group