When No Date Range Parameter Is Entered, Show All

[ed333]

I am working on a query regarding billing data. There are 2 date fields in this data: DateEntered, and DateBilled. I have a query which selects both there ranges with parameters. Under the criteria for DateEntered I have:

Between [Starting Date?] And [Stopping Date?]

Under DateBilled I have:

Between [Start Bill Date?] And [End Bill Date?]

Here is my problem:

Parameters for DateEntered will always be required. However, if nothing is entered for the second set of parameters, I want no criteria to be set for DateBilled (return all records in range of DateEntered). I have searched the forums here, but I've not seen a solution to this particular problem. Many thanks for any ideas....

Ed

 

[dcx693]

Using a parameter to return all records if it's left null

 

[ed333]

Right. I've seen that page many times. I just am not sure how to incorporate the OR statements with "Between [Param1] And [Param2]"

I tried using () and saying Between ([Param1] Or Like [Param1] Is Null) And ([Param2] Or Like [Param2] Is Nulll)

This did not work. How must I modify this?

Thanks,
Ed

 

[Jon K]

In the criteria for the DateEntered column, put:-

Between [Starting Date?] And [Stopping Date?]


And put this in a new column:-

Field: IIf([Start Bill Date?] Is Null Or [End Bill Date?] Is Null, True, [DateBilled] Between [Start Bill Date?] And [End Bill Date?])

Show: uncheck

Criteria: <>False

 

[ed333]

Sorry. I've been away for a few days working on another project. I tried the above suggestion and it did not work. I'm posting a copy of the SQL for this query with the above included for [BILLDATE].

How I can get no entry for the BILLDATE parameters of [Start Bill Date] and [End Bill Date] to return all records within the tblCharges.DATE parameter range?

Thanks,
Ed

Here's the SQL:


SELECT tblCharges.BILLFLAG, tblCharges.HOLDFLAG, tblCharges.DATE, tblCharges.BILLDATE, tblCharges.CHGATTY, Sum(tblCharges.BILLED_UNITS) AS SumOfBILLED_UNITS, Sum(tblCharges.ADJUSTED_UNITS) AS SumOfADJUSTED_UNITS, Sum(tblCharges.UNITS) AS SumOfUNITS, Sum(tblCharges.CHARGE) AS SumOfCHARGE
FROM tblCharges
GROUP BY tblCharges.BILLFLAG, tblCharges.HOLDFLAG, tblCharges.DATE, tblCharges.BILLDATE, tblCharges.CHGATTY
HAVING (((tblCharges.DATE)>#11/30/2003# And (tblCharges.DATE) Between [Start] And [Stop]) AND ((tblCharges.BILLDATE)=IIf([Start Bill Date?] Is Null Or [End Bill Date?] Is Null,True,[BILLDATE] Between [Start Bill Date?] And [End Bill Date?])) AND ((tblCharges.CHGATTY) Is Not Null))
ORDER BY tblCharges.CHGATTY;

 

[Jon K]

The criteria for selecting records are placed in the Where Clause of an SQL statement. The Having Clause in a Totals query applies to the groups, not the records.

The Design View of a Totals query is different from that of a Select query. The Criteria row is translated into the Having Clause whereas the key word Where is hidden in the Total row.

SELECT [tblCharges].[BILLFLAG], [tblCharges].[HOLDFLAG], [tblCharges].[DATE], [tblCharges].[BILLDATE], [tblCharges].[CHGATTY], Sum([tblCharges].[BILLED_UNITS]) AS SumOfBILLED_UNITS, Sum([tblCharges].[ADJUSTED_UNITS]) AS SumOfADJUSTED_UNITS, Sum([tblCharges].[UNITS]) AS SumOfUNITS, Sum([tblCharges].[CHARGE]) AS SumOfCHARGE
FROM tblCharges
WHERE [tblCharges].[DATE] Between [Start] And [Stop] And IIf([Start Bill Date?] Is Null Or [End Bill Date?] Is Null,True,[BILLDATE] Between [Start Bill Date?] And [End Bill Date?]) And [tblCharges].[CHGATTY] Is Not Null
GROUP BY [tblCharges].[BILLFLAG], [tblCharges].[HOLDFLAG], [tblCharges].[DATE], [tblCharges].[BILLDATE], [tblCharges].[CHGATTY]
ORDER BY [tblCharges].[CHGATTY];


Since [tblCharges].[DATE] Between ... And ... is used in the query, I have also removed the tblCharges.DATE>#11/30/2003#.

 

[ed333]

Thank you!!!!! It finally works!!!!

Ed

 

[ed333]

Well..... I spoke too soon it seems. This is almost working the right way ..... The only problem is that if a BILLDATE range is given, only records that actually have a BILLDATE will be pulled -- I need ones that are NULL in that field to also be pulled. However, if no BILLDATE range is given, this query works fine!

Thanks for all the help,

Ed

 

[Jon K]

To return also the Null values when a BillDate range is specified, you can add Or [BILLDATE] Is Null at the end of the IIF expression:-

IIf([Start Bill Date?] Is Null Or [End Bill Date?] Is Null,True,[BILLDATE] Between [Start Bill Date?] And [End Bill Date?] or [BILLDATE] is null)

 

[ed333]

Thank you sir!

Ed