Creating report by Date Range.

[icoso]

#1. Why is it that this runs in myPHPAdmin just fine and it retrieves the two records:

SELECT TaxCustomers.* FROM TaxCustomers WHERE TaxCustomers.tax_billdate BETWEEN concat(SUBSTR('02-10-2021', 7, 4),"-",SUBSTR('02-10-2021', 1, 5)) AND concat(SUBSTR('02-21-2021', 7, 4),"-",SUBSTR('02-21-2021', 1, 5));

but in the nuBuilder SQL it doesn't?

When I run this in nuBuilder:

SELECT TaxCustomers.* FROM TaxCustomers WHERE TaxCustomers.tax_billdate BETWEEN '2021-02-10' AND '2021-02-21';

It actually works! I get the report The Concat Statements in the 1st SQL statement return the exact same date structure.

#2. How do I get the SQL to read the date fields off my form when I click the Run button? (see the picture of the form that the report calls and opens that I uploaded previously) I added the code:

nuSetProperty('RPT_date1', date1);
nuSetProperty('RPT_date2', date2);

to the Custom Code of the form to move the date1 & date2 fields to the fields being used on the report and that I tried to use in the SQL in place of the dates? I even tried changing my SQL to be:

SELECT TaxCustomers.* FROM TaxCustomers WHERE TaxCustomers.tax_billdate BETWEEN '#RPT_date1#' AND '#RPT_date2';

And I get no data, just the headings and lines on my report. SO somehow the date1 & date2 fields on the form are not getting passed to the SQL. What code am I missing and Where should I put it?

SO I tried another test. I put this in the SQL:

SELECT TaxCustomers.* FROM TaxCustomers WHERE TaxCustomers.tax_billdate BETWEEN concat("2021-","02-10") AND concat("2021-","02-21");

(I changed the dates in the SQL above that worked to use a concat. and it does not work! I get nothing at all. However this SQL statement works just fine in phpMyAdmin and it retrieves the records.

Can anyone tell me WHY????

3. According to the object inspector the "RUN" button that automatically appears at the top of my form when I run the report is: <input id="nurunButton" type="button" title="" class="nuActionButton" value="Run" onclick="nuRunReport("CRTest")"> It ALREADY has an onclick event attached to it that runs the report and I assume the SQL statement. So the question is AGAIN how do I get the date1 & date2 fields assigned to the variables that Im using in my SQL statement and on the report?

ReportScreen1.png

4. UNFREAKING BELIEVABLE!!!! I had to change how this all is run. I had to change the form so that there was my own button on the form itself. (NOTE: I had to piece together how to add a button on the form itself - I'd LOVE for someone to show me where in the wikis, the manuals, the videos, even these post where it even comes CLOSE to showing how to add a button on a form!)

Form-ReportButton1.png

Then I had to add JavaScript Code to the button: (NOTE: I originally formatted the date fields as yyyy-mm-dd but then tried mm-dd-yyyy (because that how 95% of Americans refer to the date) I then had to change my JavaScript to parse this date into MySQL format of yyyy-mm-dd.

Code: Select all

var start_date=$('#date1').val();
var end_date=$('#date2').val();
alert("S= "+start_date+"  E= "+end_date);
if (start_date==='') {
    start_date='2000-01-01';
} else {
    start_date = start_date.substr(6,4)+"-"+start_date.substr(0,5);
}   
if (end_date==='')   {
    end_date='2099-01-01';
} else {
    end_date=end_date.substr(6,4)+"-"+end_date.substr(0,5);
}   
alert("S= "+start_date+"  E= "+end_date);
nuSetProperty('RPT_sdate', start_date);
nuSetProperty('RPT_edate', end_date);
alert("Report Date="+$('#RPT_sdate'));
nuRunReport('CRTest');

Then I had to change my SQL statement to: SELECT TaxCustomers.* FROM TaxCustomers WHERE TaxCustomers.tax_billdate BETWEEN '#RPT_sdate#' AND '#RPT_edate#';
THIS WORKED!!!! EXCEPT I wanted the dates on the report itself!
I then added the #RPT_sdate# and #RPT_edate# fields to my report (see pic below)

Form-ReportButton2.png

BUT this did NOT work.... I got the report with the expected records on it. But I DID NOT get the dates on it.

Can ANYONE EXPLAIN why 1. the report would not work initially as explained in the wikis/manuals on how to run a report. I was running the report which would load the form with the dates on it, but when I clicked RUN the date values never got passed. NOW I have to run the report from a LAUNCH form for it to run properly and I STILL cant get that dates on the report! WHY????

EDIT NOTE: Finally figured this out. You HAVE TO put the HashTag fields on the report as LABELS, not FIELDS. THEY ARE LABELS then it worked. On a side note, I actually created a hashtag in the JavaScript code on my button (remember the JavaScript has to be on the custom code of the button) that contains the whole text of the date range in one hashtag: daterange="Dates: " + $('#date1').val() + " to: " + $('#date2').val(); nuSetProperty('RPT_daterange', daterange); then you can use the single LABEL on the report with the ID name of #RPT_daterange#, that looks like: "Dates: 02-01-2021 to: 03-02-2021" rather than trying to place, align and space out 4 different report fields (LABELS)...

[steven]

reformat.png

[icoso]

steven, I think you're misunderstanding what Im asking. But to reply to your answer, so that others that might read this understand. Yes, you do have to reformat the dates if you're searching by a date.

MySQL saves the date in this format: YYYY-MM-DD
In the USA the VAST majority refer to and enter dates in this format: MM-DD-YYYY

If I have a launch form with a start_date and end_date on it, and the format I choose to have them enter the dates is MM-DD-YYYY I absolutely have to change the date in the PHP code to YYYY-MM-DD format BEFORE the SQL search is performed. The hash cookies that I use in the SQL to perform the search have to have the date in the SAME format as how they are stored in the data table.

I tried it both ways in my code and ONLY after I convert he dates does the SQL work properly.

HOWEVER, I STILL cant use the has cookies on the report itself? Any idea why they wont print on the report?