Conditional IF Parameter Query Doesn't Work 1

[0212]

Hi, all! I am pulling my hair out trying to get the following Conditional IF parameter Query to work:

IIf([Forms]![RptDialogBoxFrm]![RptType]=1 And [Forms]![RptDialogBoxFrm]![DateType]="Due Date",([RA Report].[RADue]) Between [Forms]![RptDialogBoxFrm]![BeginDate] And [Forms]![RptDialogBoxFrm]![EndDate])

I have a form that I am using to pass the query parameters. If I take any part of the above statement, it will work. If I use the conditional IF with other statements, it works. However,the above statement placed in the criteria for [RA Report].[RADue]returns nothing. I hope I was specific enough! Thanks!

 

[r937]

IIF requires 3 parameters -- you're only specifying 2

r937.com | rudy.ca

 

[0212]

Thanks, r937! However, I thought that if you do not include a "false" parameter, it would understand to do nothing. Secondly, I don't know what the alternative would be. I have tried to include "," only, but it removes it. I think the problem is with the "Between..And" Statement in conjunction with the IF Conditional Statement. Everything works if I use only one of the between conditions. I am still stumped. Thanks!

 

[dhookom]

You should provide all arguments for IIf() if only to be explicit. You can't have the "Between" inside the IIf().
You might need to use IIf() twice like (carriage returns added for clarity):

Between IIf(
  [Forms]![RptDialogBoxFrm]![RptType]=1 And [Forms]![RptDialogBoxFrm]![DateType]="Due Date",
   [RA Report].[RADue],  
   [Forms]![RptDialogBoxFrm]![BeginDate]
           ) AND 
        IIf(
  [Forms]![RptDialogBoxFrm]![RptType]=1 And [Forms]![RptDialogBoxFrm]![DateType]="Due Date",
  [RA Report].[RADue], 
  [Forms]![RptDialogBoxFrm]![EndDate]
            )

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[0212]

Thanks, dhookom!! I switched it around just a bit so that when the if is true, it will give me the data in the specified date range. If it is false it will give me no data. If the below can be improved, let me know. Otherwise, thank you so much and have a Happy New Year!

Between IIf(
[Forms]![RptDialogBoxFrm]![RptType]=1 And [Forms]![RptDialogBoxFrm]![DateType]="Due Date",
[Forms]![RptDialogBoxFrm]![BeginDate],[RA Report].[RADue]>1/1/3000)
And IIf(
[Forms]![RptDialogBoxFrm]![RptType]=1 And [Forms]![RptDialogBoxFrm]![DateType]="Due Date",
[Forms]![RptDialogBoxFrm]![EndDate],[RA Report].[RADue]>1/1/3000)

 

[dhookom]

I don't understand why you placed an expression in the False part of the IIf() that will generally return false in every case.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[0212]

Thanks, dhookom, for the reply! I did change it again and removed the >1/1/3000. If the conditional If is false, I didn't want to return any values. However, I will be using this expression in several fields with several different categories. I will just be changing the Query Name Field. The idea is that only one condition will be true. I know this can be done better through VB. But I'm not up on it yet. Thanks again!