haastreaming.blogg.se - Libreoffice basic date functions

Note that “Date+1” is the name of the fied it is a string, not the formula (calculation is made in Query1) I use MAX( "Date+1" ) to display this field.

Fields used in a “group” query must all apply a function.

Query2 SELECT "Date", MAX( "Date+1" ) "Date + 1", COUNT( "Date+1" ) "Count" FROM "Query1" GROUP BY "Date" My interface is in French, so I use ‘DD/MM/YYYY’

TO_CHAR displays the date in the format of your choice.

CAST converts the expression to DATE format (which is a number).

the date is split by year, month, day so you can do DAY( "Date" ) + 1.

Query2 counts records from Query1 and display the 2 dates.Query1 selects the records according to the parameter and create a new column by adding one day to the date.You can use 2 queries as in this AddOneDay.odb example. This can be extended to any number of days. I modified the sample provided by Open the form and enter the date and it will display the count for that day and the next 2 days. I have created a sample of using a Macro to do what you require. I think a macro would be required to carry out this requirement. So it is showing the count for the date entered not for the next day.Īs it is not possible to modify the value entered in a parameter before the query runs I can’t see a way of doing this with a parameter query. In the Table in your attached database there are no records with a date. The Query2 counts the records in the Date+1 field of Query1 which is therefore a count of the records which match the parameter date not the parameter date plus 1. You create an alias field Date+1 with 1 added to the date. Your Query1 selects records with the date entered in the parameter not the next day’s date. the date entered in the parameter plus 1 day.

He wants to enter a date in a parameter query and get a count of the records that have in a date field the next day’s date i.e. Your solution does not do what the poster wants.