Toggle on form to query between two criteria

[Asoul]

I am attempting to create a toggle on a form that would instruct a query which criteria to use.

Specifically they are date criteria. I want to be able to toggle between evaluating on a start basis verse a ship basis.

If the toggle is set to 'Start Date', the query would use the start date as its criteria. If the toggle is set to 'Ship Date" it would use the ship date.

The other complication is that within the query, "Start Date" and "Ship Date" are two separate fields always contained in the query, so how do I write the query so that it only applies criteria to the appropriate field based on the toggle setting?

Much appreciation in advance for any help.

 

[billmeye]

Is this query used as the record source for the form? How are you using this query?

 

[Asoul]

The query is not the record source, a table which contains start and report date are the record source.
The query is used to select the data to be run through analysis (with the use of many subsequent queries).

Basically the table contains the evaluation period (start and report date), the form allows selection of the appropriate evaluation period, and the query begins data selection from the data set based upon the evaluation periods.

What I want to do is be able to use a toggle to choose whether the evaluation period is queried on the data's Start Date or Ship Date. The difference of the two date basis are in performing an inventory verse costing analysis.

Make sense?

 

[billmeye]

I would place 2 hidden controls on the form, 1 to be used as the criteria for the Start Date field and one to be used as the criteria for the Ship Date field. Then, based on your toggle, assign these values depending on which is to be used as the actual criteria:

If Me.Toggle1 = 0 Then 'assume use Start Date field for eval
Me.StDate = Me.CriteriaDate
Me.ShDate = #01/01/1900#
Else 'use Ship Date field for eval
Me.StDate = #01/01/1900#
Me.ShDate = Me.CriteriaDate
End If

In the query just add the appropriate criteria string:
Start Date >=[Forms]![yrFormName]![StDate]
Ship Date >=[Forms]![yrFormName]![ShDate]

 

[Cronk]

That won't show records with a StartDate where the ShipDate has not yet been entered.

Alternative use a function

Function QueryCriteria(dteStartDate, dteShipDate) as boolean
  If me.Toggle1= true then
    QueryCriteria=(dteStartDate >= Me.criteriaDate)
  else
      QueryCriteria=(dteShipDate >= Me.criteriaDate)
  endif
end function

In the query, in a new column in the Field value put QueryCriteria([ShipDate], [ShipDate]) and set its Criteria value to True

 

[billmeye]

You could set the criteria for ship date to:

[Forms]![yrFormName]![ShDate] Or Is Null

 

[Asoul]

Thanks Bill & Cronk.
I had started with Bill's method but realized it was not allowing me to impact the criteria data in two separate fields of the query.
I tried Cronk's recommendation but could not get the code to function; likely my lack of VB knowledge.
It did however lead me to searching "Criteria Function" where I found the following post:
access-programmers.co.uk/forums/showthread.php?t=138771
where Jon K supplied an If statement used as a criteria in a new field.

I applied that to the toggle I had set up in the form and got my intended result.
It looks like:
Expr1: IIf([Forms]![Toggle20]=True, StDate >=[Forms]![Start Date] And StDate <=[Forms]![ReportDate], ShDate >=[Forms]![Start Date] And ShDate <=[Forms]![ReportDate])
with the Criteria of "True".

Hope that helps to provide others additional / alternative solutions.

Thanks again for both your feedback, couldn't have done it without you!

Asoul

PS - anyone know how to mark this thread as solved?