How to display records from oldest date if no date specified in form textbox control?

Question

I have an unbound textbox on a form that is used to enter the start date for a report that lists dated transactions.  User typically enters a date and runs the report.  I want to set it up so that if user leaves the form textbox empty the report starts with the first date in the table (or some constant date like 1/1/2000, which is before the oldest date in the transactions recordset).

So if user enters 3/1/2018 as the start date the report returns all transactions starting at 3/12018.  If user leaves the textbox empty the report returns all transactions.

The form, query and report all work fine as long as a start date is entered but I can’t figure out how to have it return all records.  I also have an end date on the form.  I think if I can solve this for the start date I can adapt that solution to the end date textbox as well.

The report’s underlying query has this criteria to display transactions on the report:

>=[Forms]![frm_Main_Menu]![dte_From]

How can I modify this expressing in my query criteria to check for a null value and if null display all records?

Answer 1

ciao THTC,

I would be more inclined to apply a wherecondion parameter instead than enclose such kind of paramenter into a query. In this way no need to set any parameter in your query.

Try to open your report in this way :

DoCmd.OpenReport "yourReportName", _

                 View:=acViewPreview, _

                 wherecondition:="yourDateField>=" & CLng(CDate(Nz(Me.dte_From, 0)))

but, in case you need to keep your query, define the parameter as date in your query, applying that below where condition

PARAMETERS [Forms]![frm_Main_Menu]![dte_From]DateTime;

select.....

 

WHERE yourTableName.yourDateField>= [Forms]![frm_Main_Menu]![dte_From]DateTime OR  [Forms]![frm_Main_Menu]![dte_From]DateTime Is Null

I would prefer the former.

HTH.

Ciao, Sandro.

Answer 2

Hi Sandro.  Thanks for the reply.  I want to use a query.  I set up a test to try your SQL but it doesn't run.  Here's the query:

parameters [Forms]![frm_Testing_StartDate_Is_Null]![txtStartDate]DateTime;
SELECT tbl_Consulting_Time.Date, Format(([tbl_Consulting_Time]![End Time]-[tbl_Consulting_Time]![Start Time])*24,"0.000") AS [Hours worked], tbl_Consulting_Time.[Start Time], tbl_Consulting_Time.[End Time]
FROM tbl_Consulting_Time
WHERE tbl_Consulting_Time.Date>=[Forms]![frm_Testing_StartDate_Is_Null]![txtStartDate]DateTime OR
[Forms]![frm_Testing_StartDate_Is_Null]![txtStartDate]DateTime is null;

When I try to save the object I get an error:  Syntax error (missing operator) in query expression in the Where clause.  Can you help me find the error in my sql code?

Also, I'm not familiar with the Parameter statement.  What is it doing exactly?

Many Thanks.

Answer 3

Hi,

Pardon me for jumping in but I think there may have been some typos in the previous SQL. How about this one?

PARAMETERS[Forms]![frm_Testing_StartDate_Is_Null]![txtStartDate] DateTime;
SELECT tbl_Consulting_Time.[Date],
 Format(([tbl_Consulting_Time]![End Time]-[tbl_Consulting_Time]![Start Time])*24,"0.000") AS [Hours worked],
 tbl_Consulting_Time.[Start Time],
 tbl_Consulting_Time.[End Time]
FROM tbl_Consulting_Time
WHERE tbl_Consulting_Time.[Date]>=[Forms]![frm_Testing_StartDate_Is_Null]![txtStartDate]
 OR [Forms]![frm_Testing_StartDate_Is_Null]![txtStartDate] Is Null;

Also, please consider naming your field differently than "Date" because "Date" is a *reserved* word.

Hope it helps...

Answer 4

ciao THTC,

I'm endorsing .theDBGuy's suggestion, as I made a typo in my previous reply, sorry for that.

Following .TheDbGuy's predicate for sure you will accomplish your task.

I agree also on renaming date field, generally speaking it's recommended choosing not reserved word like date.

One more suggestion...try moving the calculation field on report, so you should achieve the same result applying the where condition on openReport method.

Ciao and good luck for your project :-)))

Sandro.

Answer 5

Thanks!

I was able to copy this SQL and use it to get what I needed.  I also changed the name of the field in my tbl to "Gig Date", so I'm not in violation of generally accepted practices!  Thanks for pointing that out.

Now what I need to do is figure out how to build on this to add more sophisticated criteria.  In short I need to include rows where the row date is >= txtStartDate OR all rows if txtStartDate is null.  On top of that I have an end date (txtEndDate) and I need to include rows where the row date is <= txtEndDate OR all rows if txtEndDate is null.

If both textboxes are null, include all rows.  If txtStartDate is null and txtEndDate is a date, then include all rows up to an including txtEndDate.  If txtEndDate is null and txtStartDate is a date, then include all rows from and including txtStartDate. 

I've tried a few variations on the SQL you provided, but no joy so far.  I'll post another question for this enhanced problem.

Thanks again.

Answer 6

Thanks for your help!

Answer 7

Now what I need to do is figure out how to build on this to add more sophisticated criteria.  In short I need to include rows where the row date is >= txtStartDate OR all rows if txtStartDate is null.  On top of that I have an end date (txtEndDate) and I need to include rows where the row date is <= txtEndDate OR all rows if txtEndDate is null.

Hi THTC,

For very flexible selection I have a specialized form where the user can choose on any field and on almost any condition. This form is the bases to construct dynamically the WHERE-part of an SQL-string. When nothing is filled in for a field, that field is not included in the WHERE-part (so no restriction for that field). As an example see the next picture:

The special thing about the specialized form is that this is available for any form in any application.

Imb.