Query criteria question

[hammerva]

Is it possible to include some kind of "If" processing when decided to use criteria. For example I have this query:

SELECT Contractors.Developer_ID, Contractors.Case_Prefix, Contractors.Case_Num, Contractors.Expense_ID, Contractors.Expense_Amount, Contractors.Expense_Date
FROM Contractors
WHERE (((Contractors.Expense_Date) Between [Forms]![Contractor Date Search]![Start_Date] And [Forms]![Contractor Date Search]![End Date]))
ORDER BY Contractors.Expense_Date;

Is there a way to exclude the WHERE clause if the Forms!Contractor Date Search!Start Date is null?

Hope that isn't too confusing.

 

[RichMorrison]

WHERE
(
(SomeDate is Between StartDate and EndDate)
OR
(EndDate Is Null)
)

should do it.

RichM

 

[hammerva]

I knew I wasn't going to explain this properly. Let me try to reprhase it.

If the user doesn't enter a value in the form Start Date and End Date fields I want the select query to read:

SELECT Contractors.Developer_ID, Contractors.Case_Prefix, Contractors.Case_Num, Contractors.Expense_ID, Contractors.Expense_Amount, Contractors.Expense_Date
FROM Contractors

But if there is data in the Start Date & End Date fields I want to include this WHERE clause to the select:

WHERE (((Contractors.Expense_Date) Between [Forms]![Contractor Date Search]![Start_Date] And [Forms]![Contractor Date Search]![End Date]))

Is this possible?

 

[RichMorrison]

I don't know any way to design a query to meet your requirements.
Perhaps another poster will have a method.

What I would do is this:
1) Create a command button on the form, call it "Run Query" or something. Create an "OnClick" event procedure for the button.

2) In the procudure, Dim a variable called strSQL As String. Set strSQL to:
"SELECT Contractors.Developer_ID, Contractors.Case_Prefix, Contractors.Case_Num, Contractors.Expense_ID, Contractors.Expense_Amount, Contractors.Expense_Date
FROM Contractors"

3) Test Start_Date and End_Date for valid date values "IsDate(Me![Start_Date])"

4) If both Start_Date and End_Date contain valid date values then update strSQL:
strSQL = strSQL &
" WHERE (((Contractors.Expense_Date) Between [Forms]![Contractor Date Search]![Start_Date] And [Forms]![Contractor Date Search]![End Date]))"

5) Set Me.RecordSource = strSQL. This will requery the form either with or without the WHERE clause

RichM