Help with report filter

[Nathan Bloomfield]

I am using the code below to filter a report:

Private Sub Set_Filter_Click()
If Me![BeginDate] And Me![EndDate] <> "" Then
If Application.CurrentProject.AllReports("rptDailyOut put").IsLoaded
Then
DoCmd.Close acReport, "rptDailyOutput"
DoCmd.OpenReport "rptDailyOutput", acViewPreview, , "[MyDate]
Between #" & Format(Me![BeginDate], "mm/dd/yy") & "# and #" &
Format(Me![EndDate], "mm/dd/yy") & "#"
Else
DoCmd.OpenReport "rptDailyOutput", acViewPreview, , "[MyDate]
Between #" & Format(Me![BeginDate], "mm/dd/yy") & "# and #" &
Format(Me![EndDate], "mm/dd/yy") & "#"
End If
Else
MsgBox "You must enter a Start Date & End Date", vbOKOnly
End If
End Sub

I have added a new combo-box on the filter form called [DC]
This combo box row source type is "value list" and the source is:
"ALL";"VDC";"QDC"

How can I include [DC] in the code above? (where it is a required
selection)
I have tried with no success.

[fredg]

On 15 Jan 2004 01:15:33 -0800, na**************@hotmail.com (Nathan
Bloomfield) wrote:

I am using the code below to filter a report:

Private Sub Set_Filter_Click()
If Me![BeginDate] And Me![EndDate] <> "" Then
If Application.CurrentProject.AllReports("rptDailyOut put").IsLoaded
Then
DoCmd.Close acReport, "rptDailyOutput"
DoCmd.OpenReport "rptDailyOutput", acViewPreview, , "[MyDate]
Between #" & Format(Me![BeginDate], "mm/dd/yy") & "# and #" &
Format(Me![EndDate], "mm/dd/yy") & "#"
Else
DoCmd.OpenReport "rptDailyOutput", acViewPreview, , "[MyDate]
Between #" & Format(Me![BeginDate], "mm/dd/yy") & "# and #" &
Format(Me![EndDate], "mm/dd/yy") & "#"
End If
Else
MsgBox "You must enter a Start Date & End Date", vbOKOnly
End If
End Sub

I have added a new combo-box on the filter form called [DC]
This combo box row source type is "value list" and the source is:
"ALL";"VDC";"QDC"

How can I include [DC] in the code above? (where it is a required
selection)
I have tried with no success.

You didn't tell us the name of the field to be filtered using [DC]!!!
I'll assume there is a field called [SomeField] in the table and it is
Text DataType.
Just add the criteria at the end of your existing where clause, after
removing the existing final ".

"[MyDate] Between #" & Format(Me![BeginDate], "mm/dd/yy") & "# and #"
& Format(Me![EndDate], "mm/dd/yy") & "# AND [SomeField] = '" & Me![DC]
& "'"

--
Fred

Please reply only to this newsgroup.
I do not reply to personal e-mail.

[Nathan Bloomfield]

> Just add the criteria at the end of your existing where clause, after

removing the existing final ".

"[MyDate] Between #" & Format(Me![BeginDate], "mm/dd/yy") & "# and #"
& Format(Me![EndDate], "mm/dd/yy") & "# AND [DC] = '" & Me![DCselect]
& "'"

When using the code above I get the following error:

"Microsoft Access can't find the field "|" referred to in your expression

I have confirmed the names of the fields & they are correct.

[fredg]

On 15 Jan 2004 14:39:32 -0800, na**************@hotmail.com (Nathan
Bloomfield) wrote:

Just add the criteria at the end of your existing where clause, after
removing the existing final ".

"[MyDate] Between #" & Format(Me![BeginDate], "mm/dd/yy") & "# and #"
& Format(Me![EndDate], "mm/dd/yy") & "# AND [DC] = '" & Me![DCselect]
& "'"

When using the code above I get the following error:

"Microsoft Access can't find the field "|" referred to in your expression

I have confirmed the names of the fields & they are correct.

Nathan,

The correct syntax is
"[FieldInTheTableToBeSearched] = '" & Me![ControlOnForm] & "'"

According the this post, [DCselect] is the combo on the form; [DC] is
the field in the table.

You originally posted:I have added a new combo-box on the filter form called [DC]
This combo box row source type is "value list" and the source is:
"ALL";"VDC";"QDC"

In other words, the control [DC] is the one on the filter form that
contains the 3 possible words, of which only one is wanted to be
returned.
You didn't post the name of the field in the table.

I wrote:
& "# AND [SomeField] = '" & Me![DC] & "'"

You wrote:
& "# AND [DC] = '" & Me![DCselect] & "'"

You mixed the control on the form ([DC]) with the field in the table
([DCSelect]).

You should have written:
"# AND [DCSelect] = '" & Me![DC] & "'"
--
Fred

Please reply only to this newsgroup.
I do not reply to personal e-mail.

[Nathan Bloomfield]

> Just add the criteria at the end of your existing where clause, after

removing the existing final ".

"[MyDate] Between #" & Format(Me![BeginDate], "mm/dd/yy") & "# and #"
& Format(Me![EndDate], "mm/dd/yy") & "# AND [DC] = "'" & Me![DCSelect]
& "'"

Hi Fred,

I should have mentioned that I changed the control on the form from DC
to DCselect to avoid confusion with the field DC on the report.

Also, I added a quotation mark (") after the [DC] = as I was getting
an error.

Thanks for your help, any more advice would be much appreciated as
this problem I've just encountered is holding back a project I've been
working on for weeks.

Regards,

Nathan