Is it possible to have a Form Generate a Report by date?

Getting your data onto paper - or the web - Discussing the reports features of Base

Is it possible to have a Form Generate a Report by date?

Postby tfallin » Sat May 17, 2014 11:50 pm

I work for our local law enforcement that has switched over to OpenOffice from Microsoft Office. I have created a database for imputing training data and other information. I am trying to run a report for certain information between certain dates. I found and downloaded a sample data base from here and reviewed but I do not understand the code functions of this software. Is there a way for someone to walk me step by step on getting the date function to work. I have created reports and queries and they do work. I also have attached my phone number if anyone is able to lend their support. The sample I found was "open_report_by_form" which is exactly what I am looking for.


Thank you in advance for your help.....
Last edited by tfallin on Mon Jul 16, 2018 1:40 am, edited 1 time in total.
openoffice 4.0.1 on windows xp
tfallin
 
Posts: 5
Joined: Fri May 16, 2014 10:11 pm

Re: Is it possible to have a Form Generate a Report by date?

Postby Villeroy » Sun May 18, 2014 11:45 am

Create a most simple query
Code: Select all   Expand viewCollapse view
SELECT * FROM "Some Table" WHERE "Date Field" = :Please_Enter_Date

with the actual field name and table name in double-quotes and the parameter with a leading colon and not spaces.

Save and close the query.
Right-click the query icon and build the report from here.
Every time the user opens the report (s)he will be prompted for a date (3 numbers separated by slashes e.g. 1/3/14).
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 27758
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Is it possible to have a Form Generate a Report by date?

Postby tfallin » Sun May 18, 2014 1:13 pm

I am not sure where to put the above code and what I am trying to do is use between dates. e.g. 1/1/14 to 1/31/14. I will try and figure out how to build by using code. Can you give me a step by step on how to between dates?
openoffice 4.0.1 on windows xp
tfallin
 
Posts: 5
Joined: Fri May 16, 2014 10:11 pm

Re: Is it possible to have a Form Generate a Report by date?

Postby Villeroy » Tue May 20, 2014 10:01 am

Two equivalent queries:
Code: Select all   Expand viewCollapse view
SELECT * FROM "Some Table" WHERE "Date Field" BETWEEN :Start_Date AND :End_Date

Code: Select all   Expand viewCollapse view
SELECT * FROM "Some Table" WHERE ("Date Field" >= :Start_Date) AND ("Date Field" <= :End_Date)
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 27758
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Is it possible to have a Form Generate a Report by date?

Postby MTP » Tue May 20, 2014 6:04 pm

It sounds like you have only made queries in the design editor? I think you will find it very helpful to learn about queries in "SQL view" as well. To follow Villeroy's instructions, click on the "Create Query in SQL View" link and paste his code into that window.
OpenOffice 4.1.1 on Windows 10, HSQLDB 1.8 split database
User avatar
MTP
Volunteer
 
Posts: 1620
Joined: Mon Sep 10, 2012 7:31 pm
Location: Midwest USA

Re: Is it possible to have a Form Generate a Report by date?

Postby Villeroy » Tue May 20, 2014 11:10 pm

Well, menu:Insert>Query (SQL View) is easier to communicate than the graphical query designer menu:Insert>Query (Design View). Queries are commands to read data from your database. Copy and paste my query and adjust the names to the names used in your database.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 27758
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Is it possible to have a Form Generate a Report by date?

Postby tfallin » Mon May 26, 2014 5:02 pm

Are the two above codes together or do I pick the one that I want? And what names do I a change to match? I know I change "some table".
openoffice 4.0.1 on windows xp
tfallin
 
Posts: 5
Joined: Fri May 16, 2014 10:11 pm

Re: Is it possible to have a Form Generate a Report by date?

Postby Villeroy » Mon May 26, 2014 5:10 pm

The 2 names in "double quotes". The table name and "Date Field" (unless your date field is named "Date Field").
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 27758
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Is it possible to have a Form Generate a Report by date?

Postby tfallin » Mon May 26, 2014 5:17 pm

The date field will be the name I have set in the Table correct "start date"? And there are two codes above. Do I use the one that I like or do they go together? I apologize in advance but code is new to me. I have a limited time to get this figured out for our agency. This is pretty much it. I just need to be able to run the reports I have created between a certain date period. If you are able to call me for 5 mins I would be greatful.
openoffice 4.0.1 on windows xp
tfallin
 
Posts: 5
Joined: Fri May 16, 2014 10:11 pm

Re: Is it possible to have a Form Generate a Report by date?

Postby tfallin » Mon May 26, 2014 7:02 pm

ok,,, I got this code to work adn it brings up a pop-up box:

SELECT "Last, First Name", "Address", "Phone #", "Start Date", "Start Date" FROM "Clients" AS "Clients" WHERE "Start Date" >= :Start_Date AND "Start Date" <= :End_Date

Now is there a way to make this a form? fill in the start box and end box and then click "filter" or "run" button?

I found this code by I can not get it to work:

SELECT "Tbl_Contacts"."ID", "Tbl_Sessions"."ID", "Tbl_Contacts"."SName", "Tbl_Contacts"."FName", "Tbl_Contacts"."SName" || ' ' || "Tbl_Contacts"."FName" AS "Name", "Tbl_Sessions"."Date", "Tbl_Sessions"."Remark", "Tbl_Sessions"."Date" FROM "Tbl_Contacts", "Tbl_Sessions" WHERE ( "Tbl_Sessions"."Date" <= ( SELECT "DatTO" FROM "Tbl_Filter" ) AND "Tbl_Sessions"."Date" >= ( SELECT "DatFROM" FROM "Tbl_Filter" ) AND "Tbl_Sessions"."ID_Name" = "Tbl_Contacts"."ID" OR "Tbl_Sessions"."Date" <= ( SELECT "DatTO" FROM "Tbl_Filter" ) AND "Tbl_Sessions"."ID_Name" = "Tbl_Contacts"."ID" AND ( SELECT "DatFROM" FROM "Tbl_Filter" ) IS NULL OR "Tbl_Sessions"."Date" >= ( SELECT "DatFROM" FROM "Tbl_Filter" ) AND "Tbl_Sessions"."ID_Name" = "Tbl_Contacts"."ID" AND ( SELECT "DatTO" FROM "Tbl_Filter" ) IS NULL OR "Tbl_Sessions"."ID_Name" = "Tbl_Contacts"."ID" AND ( SELECT "DatFROM" FROM "Tbl_Filter" ) IS NULL AND ( SELECT "DatTO" FROM "Tbl_Filter" ) IS NULL )


it is not my base file but tried to duplicate it and create a form. No luck...
openoffice 4.0.1 on windows xp
tfallin
 
Posts: 5
Joined: Fri May 16, 2014 10:11 pm

Re: Is it possible to have a Form Generate a Report by date?

Postby Villeroy » Mon May 26, 2014 7:32 pm

tfallin wrote:I am trying to run a report for certain information between certain dates. I found and downloaded a sample data base from here and reviewed but I do not understand the code functions of this software.

I gave you the most simple parameter query hoping that you would not need the full featured solution with form controls, filter tables and alike. Create your report from the simple parameter query and you will be prompted for the start and end date every time when you open that report.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 27758
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Is it possible to have a Form Generate a Report by date?

Postby MTP » Tue May 27, 2014 5:47 pm

The query you got working can be used to make a "report", as Villeroy explained.

The implementation is different for a "form". There are multiple ways to approach it. One way I like is to use a filter table with (usually) one row. The form has date fields where the user can store dates in the filter table. The query, instead of having a popup box for the fields, looks at the filter table. For example
Code: Select all   Expand viewCollapse view
SELECT "Clients"."Last, First Name", "Clients"."Address", "Clients"."Phone #", "Clients"."Start Date"
   FROM "Clients"
   INNER JOIN "Filter"
      ON "Filter"."Start Date" <= "Clients"."Start Date"
         AND "Filter"."End Date" >= "Clients"."Start Date"


There is more to it, with subforms and pushbuttons. Some of the techniques are explained here.

I'm sorry to hear you are under a lot of time pressure. While the functionality of Base is fairly high, and it can do everything you've described, it is definitely a user-unfriendly program with a steep learning curve. If you can get more time to work through some tutorials and get a better feel for form design and SQL coding, I would highly recommend that.
OpenOffice 4.1.1 on Windows 10, HSQLDB 1.8 split database
User avatar
MTP
Volunteer
 
Posts: 1620
Joined: Mon Sep 10, 2012 7:31 pm
Location: Midwest USA


Return to Reporting

Who is online

Users browsing this forum: No registered users and 4 guests