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

[tfallin]

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.....

[Villeroy]

Create a most simple query

Code: Select all

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).

[tfallin]

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?

[Villeroy]

Two equivalent queries:

Code: Select all

SELECT * FROM "Some Table" WHERE "Date Field" BETWEEN :Start_Date AND :End_Date

Code: Select all

SELECT * FROM "Some Table" WHERE ("Date Field" >= :Start_Date) AND ("Date Field" <= :End_Date)

[MTP]

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.

[Villeroy]

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.

[tfallin]

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".

[Villeroy]

The 2 names in "double quotes". The table name and "Date Field" (unless your date field is named "Date Field").

[tfallin]

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.

[tfallin]

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...

[Villeroy]

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.

[MTP]

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

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.