Multiple Parameter Query 2

[0212]

Hi, all! I am using a form to pass multiple parameters to a query. Depending on the options selected in the form, a certain set of dates are returned. There are three categories and for each category there are three options.

In a multiple table query, there a nine fields that match the nine options. I have developed a conditional IIF Statement to test each field for the selected option. If the the options passed to the field are true, the date range is allowed to pass and the recordset that matches the date range is returned. If it is false, I want the field to allow the return of all dates, not just records that are not null (like there is no criteria).

However, when I include two options for two fields in a date category, only one record is returned - the one record that is not null for that field. This is very difficult to explain. I have included a few of the criteria expressions to show you how I have set it up.

RA Report (Category 1)Option 1 ("Due Date")

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

RA Report (Category 1) Option 2 ("Actual Completion Date")

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

CSP Report (Category 2) Option 1 ("Due Date")

Between IIf([Forms]![RptDialogBoxFrm]![RptType]=2 And [Forms]![RptDialogBoxFrm]![DateType]="Due Date",[Forms]![RptDialogBoxFrm]![BeginDate],[CSP Report].[CSPDue]) And IIf([Forms]![RptDialogBoxFrm]![RptType]=2 And [Forms]![RptDialogBoxFrm]![DateType]="Due Date",[Forms]![RptDialogBoxFrm]![EndDate],[CSP Report].[CSPDue])

When Option 2 is false, it should return all dates from Option 1. It only returns non-null records for Option 2. I appreciated any suggestions that you may have!

THANKS!

 

[dhookom]

I see "Rpt" and "Report" in several places in your post. Is this query the record source for a report? If it is, I would remove nearly all criteria from the query and use the Where Condition of DoCmd.OpenReport.

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]

dhookom, thanks again for your response! Eventually, I would like to use this query as the record source for a form. The purpose of this query is to provide information on tracking the due dates and completion dates for various reports (non-access). From a dialogbox form, the user selects which report, which date type (due date, completion date, etc) and what date range for which they are interested. Only one condition of the nine will be true. For that condition, I want to see all dates that satisfy that condition.

Here is an example of out put when category 1 and option 1 are true:

ID RADue RAProj RAComp CSPDue CSPProj etc.

1 06/01/2006 05/01/2006
2 06/01/2006 07/01/2006 06/01/2006

Hope this helps!

 

[dhookom]

If the query is for opening a form or report, then I would remove the criteria and build a where clause to use in the OpenForm or OpenReport method. For instance:

   Dim strWhere as String
   strWhere = "1=1 "
   If Me.RptType = ... Then
      strWhere = strWhere & " AND ..... "
   End If
   ' more lines of code to build strWhere
   DoCmd.OpenReport "rptYourName", acPreview, , strWhere

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! However, I am not up on VB Code at all. I write SQL through the QBE Window. Can you guide me? This is what I believe I must do:

- Set up variables to except the begin date and end dates of the report. Depending on the results of each CASE Statement (it evaluates options), the variable would be sent to the specific field criteria through an SQL between statement for the Query by opening the display form.

-Attach the code to the On Click Property in the DialogueBox Form to the parameter query. The current code attached to an ok button is as follows:

Private Sub Ok_Click()
Me.Visible = False
DoCmd.OpenQuery "QryRptsSelect", acViewNormal, acEdit
DoCmd.Close acForm, "RptDialogBoxFrm"
End Sub

I realize that I will have to change the OpenQuery to OpenForm. If you can give me guidance, I will attempt to write the code. Thanks!!

 

[dhookom]

You will need to provide the logic etc but something like:

Private Sub Ok_Click()
  Me.Visible = False
  Dim strWhere as String
  strWhere = "1=1 "
  If Not IsNull(Me.txtStartDate) Then
    strWhere = strWhere & " And [DateField]>=#" & _
      Me.txtStartDate & "# "
  End If
  If Not IsNull(Me.txtEndDate) Then
    strWhere = strWhere & " And [DateField]<=#" & _
      Me.txtEndDate & "# "
  End If

  DoCmd.OpenForm "frmYourForm", acViewNormal, acEdit, strWhere
  DoCmd.Close acForm, "RptDialogBoxFrm"
End Sub

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, again, dhookom! But, is strWhere the variable that I put in the criteria in each field of the query? or How do I tell in vba which field in the query/form to place strWhere? I know you are lightyears ahead of me in this. If you could just explain a little, it would help me a lot!

 

[dhookom]

You need to forget about your query once you have removed all the criteria. For instance, delete all this stuff from your query:
[tt][blue]
Between IIf([Forms]![RptDialogBoxFrm]![RptType]=1 And [Forms]![RptDialogBoxFrm]![DateType]="Actual Completion Date",[Forms]![RptDialogBoxFrm]![BeginDate],[RA Report].[RAComp]) And IIf([Forms]![RptDialogBoxFrm]![RptType]=1 And [Forms]![RptDialogBoxFrm]![DateType]="Actual Completion Date",[Forms]![RptDialogBoxFrm]![EndDate],[RA Report].[RAComp])[/blue][/tt]


The where condition in DoCmd.OpenForm creates the criteria.

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]

Thank you, dhookom!! I finally got it to work. I used the Select Case for the logic. One last thing would be helpful, however. Could you explain each term in the following?

If Not IsNull(Me.txtStartDate) Then
strWhere = strWhere & " And [DateField]>=#" & _
Me.txtStartDate & "# "
End If

I would like to apply it to numbers and text. Thanks again for all of your help!!

 

[lespaul]

If you want to add a string:

If Not IsNull(Me.txtStringField) Then
strWhere = strWhere & " And [StringField]>='" & _
Me.txtStringField & "' "
End If


If you want to add a number:

If Not IsNull(Me.txtNumberField) Then
strWhere = strWhere & " And [NumberField]>=" & _
Me.txtNumberField
End If

date delimiter = #
String delimiter = '
Numbers have no delimiter



Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi

 

[0212]

Thank you, Leslie! I appreciate all of the help that all of you have given me!

Jim.

 

[0212]

lespaul, one other thing. What is the & used for? Thanks!

Jim.

 

[lespaul]

It's used to combine the constant string with the variable. strWhere is a variable that you are modifying.

So you start by putting WHERE at the beginning of strWhere:
strWhere = "WHERE "

When you have:

strWhere = strWhere & " SomeCondition = " & SomeVariable

your strWhere string ends up as:

strWhere = "WHERE SomeCondition = VariableValue"

if you didn't have the bolded section above, your variable would end up being:

strWhere = "SomeCondition = VariableValue"

you REPLACE the value of the variable instead of concatenating more information to the end of the existing value.

Make sense?

Leslie

 

[0212]

Thanks, Lespaul. It does make sense!

Jim.