Query - Dates

[Oreynolds]

Hi,

I have a query I am filtering dates with criteria as follows which works perfectly:

Between [Forms]![SalesAnalysisQuotes]![txtStartDate] And [Forms]![SalesAnalysisQuotes]![txtEndDate]

However I want to introduce an IIf into this as follows:

IIf([Forms]![SalesAnalysisQuotes]![txtQuoteDateOption]=1,[Quotations].[QuoteDate] Between [Forms]![SalesAnalysisQuotes]![txtStartDate] And [Forms]![SalesAnalysisQuotes]![txtEndDate],[Quotations].[QuoteDate] Between [Forms]![SalesAnalysisQuotes]![txtStartDate2] And [Forms]![SalesAnalysisQuotes]![txtEndDate2])

However it will not return any records, am I doing something wrong or is it not possible to achieve this in this way?

Thanks

 

[theDBguy]

I probably wouldn't use an IIf() for that. Are you saying you want to use a different set of dates based on whether the checkbox is checked or not?

 

[The_Doc_Man]

Well, for starters, if your controls "txtStartDate" "txtStartDate2" etc. are text dates, I'm surprised anything works without converting the fields to dates first. However, that is a mere nit-pick. I'm going to highlight two parts of your second query.

IIf([Forms]![SalesAnalysisQuotes]![txtQuoteDateOption]=1,[Quotations].[QuoteDate] Between [Forms]![SalesAnalysisQuotes]![txtStartDate] And [Forms]![SalesAnalysisQuotes]![txtEndDate],[Quotations].[QuoteDate] Between [Forms]![SalesAnalysisQuotes]![txtStartDate2] And [Forms]![SalesAnalysisQuotes]![txtEndDate2])

Everything else has the [Forms]![SalesAnalysisQuotes] qualifier but these do not. They also don't appear in the first query, which you say works. Perhaps you need [Forms]![Quotations]![QuoteDate] (a fully qualified field path). That leads to other questions, such as whether that actually IS an open form at the time of the query.

(EDIT 2: Of course, you only showed us the IIF, not the whole query, so if [Quotations] comes from the query and the other stuff comes from a form, it would make more sense.)

This might be more easily served with a JOIN query in which your SalesAnalysisQuotes and Quotations tables (or queries, whatever they are) are somehow joined based on some common field that links the records together so that you can do

SELECT SAQ.txtQuoteDateOption, Q.QuoteDate, SAQ.txtStartDate, SAQ.txtEndDate, SAQ.txtStartDate2, SAQ.txtEndDate2, ...<<<other fields>>>
FROM <<<table that drives SalesAnalysisQuotes form>>> SAQ INNER JOIN <<<whatever drives Quotations>>> Q
ON SAQ.<<<linking field>>> = Q.<<<linking field>>>
WHERE ( ( SAQ.txtQuoteDateOption = 1 ) AND ( Q.QuoteDate BETWEEN SAQ.txtStartDate AND SAQ.txtEndDate ) ) OR
        ( ( SAQ.txtQuoteDateOption <> 1 ) AND ( Q.QuoteDate BETWEEN SAQ.txtStartDate2 AND SAQ.txtEndDate2 ) ) ...<<< more where conditions>>>

Of course, you have to supply appropriate material such as query or table names for the things in <<<>>>

EDIT: I see that our friend theDBguy agrees with me. IIF is probably NOT the way to go.

If you are not familiar with JOIN queries, you can look them up here or on the web. The "SAQ" and "Q" here are table aliases to allow for less typing of lengthy source names.

 

[Oreynolds]

I probably wouldn't use an IIf() for that. Are you saying you want to use a different set of dates based on whether the checkbox is checked or not?

I've changed my tack just slightly but in essence if the value in [txtQuoteDateOption] =1 I want to filter the records retuned using the values between [txtStartDate] and [txtEndDate]. If the the vlaue in [txtQuoteDateOption] is anything else I just want it to return ALL records and not filter on the date field, like this:

IIf([Forms]![SalesAnalysisQuotes]![txtQuoteDateOption]=1,[Quotations].[QuoteDate] Between [Forms]![SalesAnalysisQuotes]![txtStartDate] And [Forms]![SalesAnalysisQuotes]![txtEndDate],[Quotations].[QuoteDate])

When I test the above and the value of [txtQuoteDateOption] <> 1 you do get ALL unfiltered records however when the value = 1 I get nothing. Strange thing is if I just put the following as a criteria the between statement works fine:

Between [Forms]![SalesAnalysisQuotes]![txtStartDate] And [Forms]![SalesAnalysisQuotes]![txtEndDate]

Any thoughts?

 

[Oreynolds]

Well, for starters, if your controls "txtStartDate" "txtStartDate2" etc. are text dates, I'm surprised anything works without converting the fields to dates first. However, that is a mere nit-pick. I'm going to highlight two parts of your second query.

IIf([Forms]![SalesAnalysisQuotes]![txtQuoteDateOption]=1,[Quotations].[QuoteDate] Between [Forms]![SalesAnalysisQuotes]![txtStartDate] And [Forms]![SalesAnalysisQuotes]![txtEndDate],[Quotations].[QuoteDate] Between [Forms]![SalesAnalysisQuotes]![txtStartDate2] And [Forms]![SalesAnalysisQuotes]![txtEndDate2])

Everything else has the [Forms]![SalesAnalysisQuotes] qualifier but these do not. They also don't appear in the first query, which you say works. Perhaps you need [Forms]![Quotations]![QuoteDate] (a fully qualified field path). That leads to other questions, such as whether that actually IS an open form at the time of the query.

(EDIT 2: Of course, you only showed us the IIF, not the whole query, so if [Quotations] comes from the query and the other stuff comes from a form, it would make more sense.)

This might be more easily served with a JOIN query in which your SalesAnalysisQuotes and Quotations tables (or queries, whatever they are) are somehow joined based on some common field that links the records together so that you can do

SELECT SAQ.txtQuoteDateOption, Q.QuoteDate, SAQ.txtStartDate, SAQ.txtEndDate, SAQ.txtStartDate2, SAQ.txtEndDate2, ...<<<other fields>>>
FROM <<<table that drives SalesAnalysisQuotes form>>> SAQ INNER JOIN <<<whatever drives Quotations>>> Q
ON SAQ.<<<linking field>>> = Q.<<<linking field>>>
WHERE ( ( SAQ.txtQuoteDateOption = 1 ) AND ( Q.QuoteDate BETWEEN SAQ.txtStartDate AND SAQ.txtEndDate ) ) OR
        ( ( SAQ.txtQuoteDateOption <> 1 ) AND ( Q.QuoteDate BETWEEN SAQ.txtStartDate2 AND SAQ.txtEndDate2 ) ) ...<<< more where conditions>>>

Of course, you have to supply appropriate material such as query or table names for the things in <<<>>>

EDIT: I see that our friend theDBguy agrees with me. IIF is probably NOT the way to go.

If you are not familiar with JOIN queries, you can look them up here or on the web. The "SAQ" and "Q" here are table aliases to allow for less typing of lengthy source names.

Trouble is the date fields in your proposed select statement do not exist in tables as this is a pop up filter form to run a report. The users select some options, enter a date range and then the report runs based on those values as criteria in the report's query.

 

[cheekybuddha]

If you are doing this in SQL view, your WHERE clause would look like:

WHERE (([Quotations].[QuoteDate] Between [Forms]![SalesAnalysisQuotes]![txtStartDate] And [Forms]![SalesAnalysisQuotes]![txtEndDate]) OR [Forms]![SalesAnalysisQuotes]![txtQuoteDateOption]<>1)
  AND (([Quotations].[QuoteDate] Between [Forms]![SalesAnalysisQuotes]![txtStartDate2] And [Forms]![SalesAnalysisQuotes]![txtEndDate2]) OR [Forms]![SalesAnalysisQuotes]![txtQuoteDateOption]=1)