Parameterised query to return all records or those Between two val

  • Thread starter Thread starter Pete
  • Start date Start date
P

Pete

Re Access 2003
I have a parent form that provides criteria for a subform, which is based on
a parameterised query. The criteria includes two date textboxes: Start & End.
I would like the query to return all records if the 2 date textboxes are
blank:-
Like "*"
Or, if two dates entered, use:-
Between [Forms]![frmPO_TotalsBySupplierAndDateRange]![txtStartDate] And
[Forms]![frmPO_TotalsBySupplierAndDateRange]![txtEndDate]

I have entered the following string into the criteria row of the relevant
date field:-
IIf(IsNull([Forms]![frmPO_TotalsBySupplierAndDateRange]![txtStartDate]),
"Like '*'","Between
[Forms]![frmPO_TotalsBySupplierAndDateRange]![txtStartDate] And
[Forms]![frmPO_TotalsBySupplierAndDateRange]![txtEndDate]")

.... but I get the message:
"This expression is typed incorrectly, or it is too complex to be evaluated."

Is there any way I can get this to work using a single query, or will I have
to create a second query and hot-swap the RecordSource, based on a value
being present in the date fields or not?

Many thanks.
Pete.
 
"Like '*' " becomes text rather than a logical operator as you used it.
Try this --
(Between [Forms]![frmPO_TotalsBySupplierAndDateRange]![txtStartDate] And
[Forms]![frmPO_TotalsBySupplierAndDateRange]![txtEndDate]) OR
([Forms]![frmPO_TotalsBySupplierAndDateRange]![txtStartDate] Is Null)
 
IF the field will ALWAYS have a value, you can use

Between
Nz([Forms]![frmPO_TotalsBySupplierAndDateRange]![txtStartDate],#1/1/1800#) And
NZ([Forms]![frmPO_TotalsBySupplierAndDateRange]![txtEndDate],#12/31/2999#)

That will allow you to leave either or both parameters blank.
IF values in both, you will search the specified date range.
If both are null, you will seach from Jan 1, 1800 to Dec 31,2999
If value in txtStartDate and null in txtEndDate then you should get all
records greater than or equal to txtStartDate.
IF null in txtStartDate and value in txtEndDate then you should get all
records on or before txtEndDate
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
 
Back
Top