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 - Base Query to show records for the last 7 days

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


Joined: 04 Jun 2012
Posts: 4
PostPosted: Mon Jun 04, 2012 9:21 am    Post subject: SOLVED - Base Query to show records for the last 7 days Reply with quote
OpenOffice v3.4 Base, Windows XP
I am attempting to run a query showing incidents for a specific agency for the last 7 days. I have the query working by manually putting in "where date > #6/1/2012#" but I want the query to be 'dynamic' in that it uses the current date automatically without user interaction.

If I was doing this in Microsoft Access, the SQL would be:
Code:
SELECT"Response_Master_Incident"."Master_Incident_Number", "Response_Master_Incident"."Response_Date", "Response_Master_Incident"."Agency_Type",
"Response_Master_Incident"."Problem"
FROM "Response_Master_Incident"
WHERE "Response_Master_Incident"."Response_Date" >= DATEADD("d",-7,NOW)
AND "Response_Master_Incident"."Agency_Type" = 'CFD'
ORDER BY "Response_Master_Incident"."Response_Date" DESC


Supposedly OO Base has the NOW, CurrentDate, DateAdd functions but they do NOT seem to be working in the "criterion" section of the query designer or when manually typed in using SQL mode.
_________________
You get what you pay for

Last edited by politot on Tue Jun 05, 2012 9:35 am; 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: 2474
Location: 3rd Rock From The Sun
PostPosted: Mon Jun 04, 2012 10:19 am    Post subject: Reply with quote
Quote:
Supposedly OO Base has the NOW, CurrentDate, DateAdd functions but they do NOT seem to be working in the "criterion" section of the query designer or when manually typed in using SQL mode.


You did NOT say, which database back-end you are using with Base. After you open your AOO ( Apache OpenOffice ) Base file, on the status bar, at the bottom, what does it say?

If, it says, you are using Embedded Database, in that case, it is using HSQL version 1.8 and that version does NOT support the use of the DATEADD function.

If, you are using a version of HSQL 2.0 or greater, please see the link below, for documentation on the syntax for DATEADD

http://www.hsqldb.org/doc/2.0/guide/builtinfunctions-chapt.html#bfc_datetime_arithmetic

So, the WHERE clause should be written as ( NOTE: the first parameter to denote days are between SINGLE QUOTES ) :

Code:
WHERE "Response_Master_Incident"."Response_Date" >= DATEADD('dd', -7, CURRENT_DATE)


If you are using the 'older' version of HSQL backend ( 1.8 ) . . . you could write it as ( NOTE: the first parameter, dd is between SINGLE QUOTES ):

Code:
WHERE DATEDIFF('dd', "Response_Master_Incident"."Response_Date", CURRENT_DATE) <= 7


Documentation for HSQL 1.8 for above is found at:

http://www.hsqldb.org/doc/1.8/guide/ch09.html#N1251E

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

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
politot
Newbie
Newbie


Joined: 04 Jun 2012
Posts: 4
PostPosted: Mon Jun 04, 2012 4:00 pm    Post subject: Reply with quote
First and foremost, THANK YOU for the quick response and the alternative solutions. Unfortunately neither worked but I think that's my fault. My apologies for omitting the database back-end. It's ODBC, connecting to Microsoft SQL Server. It would appear that the syntax I need to worry about is whatever the back-end database server is using. So it seems tomorrow's task would be to do the query on the SQL server then mirror it in the OO Base query.

Also, thanks for the reference links, being new to AOO, it helps to have good reference starting points.
_________________
You get what you pay for
Back to top
View user's profile Send private message
Sliderule
Super User
Super User


Joined: 29 May 2004
Posts: 2474
Location: 3rd Rock From The Sun
PostPosted: Mon Jun 04, 2012 4:40 pm    Post subject: Reply with quote
You said:

politot wrote:
the database back-end. It's ODBC, connecting to Microsoft SQL Server.


Please check the link below for the functions available with Microsoft Server ODBC.

http://msdn.microsoft.com/en-us/library/ms714639.aspx

http://msdn.microsoft.com/en-us/library/ms714639.aspx wrote:
CURRENT_DATE( ) (ODBC 3.0)
Returns the current date.


Therefore . . .

Code:
WHERE "Response_Master_Incident"."Response_Date" >= CURRENT_DATE() - 7


So, if you are building the Query with the Base GUI ( Graphic User Interface -- Create Query in Design View... )

  1. On the Field line . . . put your field . . . "Response_Master_Incident"."Response_Date"

  2. On the Alias line enter whatever you please, which is what will appear in the output

  3. On the Criterion line, enter: >= CURRENT_DATE() - 7

Please understand, I canNOT test this on my machine, since, I am not running Microsoft Server via ODBC. Smile

Another link Microsoft Differences MS Server and Access

http://sqlserver2000.databases.aspfaq.com/what-are-the-main-differences-between-access-and-sql-server.html

With the above, you can scroll down and find DATEADD and see the differences Access vs MS Server. Smile

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
politot
Newbie
Newbie


Joined: 04 Jun 2012
Posts: 4
PostPosted: Tue Jun 05, 2012 5:42 am    Post subject: I've learned that SQL is not ODBC Reply with quote
SlideRule, will you marry me? Just kidding. The following code will work in SQL but does not work with Base and ODBC data engine:
Code:
(dbo.Response_Master_Incident.Response_Date > DATEADD(day, - 7, GETDATE()))


In fact, some of the Microsoft SQL functions didn't work in SQL (nor ODBC)! So my next step is to determine the function differences for SQL & ODBC. As usual, the links you provided were very informative and helpful with other mental projects. This is definitely something I need to figure out but does not need to be a priority on your end.

Thank you for the quick and helpful responses! Once I get this resolved, I will definitely post here.
_________________
You get what you pay for
Back to top
View user's profile Send private message
politot
Newbie
Newbie


Joined: 04 Jun 2012
Posts: 4
PostPosted: Tue Jun 05, 2012 9:35 am    Post subject: SOLVED Reply with quote
I took the easy way out. I created a view on the SQL server since I could get that to show the time frame. Then built specific queries for the different agencies in OpenOffice Base.

But if someone ever does come across how to do this all in Base queries, please drop me note?
_________________
You get what you pay for
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