Passing a value to a query

[Guest]

Hi
I am having a few problems with the following.
I have a form that builds a filter for the system reports. One particular
report has a subform that also needs filtering.
The user can select to see "Completed", "Uncompleted" or "All" Milestones in
the report. When the user selects one of these values from a combo box, I am
saving that value in a global called strMilestone. I then have a function
called getMFilter() that returns the value. In the query for the report I
call getMFilter in the Milestone column.
This works fine when I send the value "Completed" but I can't get it to work
for Not Completed or All. Here is the code I am using to set the value.

If Me.cmbMilestone = 0 Then 'All
strMilestone = ""
ElseIf cmbMilestone = 1 Then 'Completed
strMilestone = "Completed"
Else
strMilestone = "<> 'Completed'" 'Not completed
End If

Can anyone tell me why this isn't working for Not Completed and All?
Thanks in advance
Sandy

 

[John Griffiths]

Sure that's your only problem ?

"If" called with Me.cmbMilestone
"ElseIF" called with cmbMilestone (No Me.)

Try :-
Select Case cmbMilestone
Case 0
strMilestone = ""
Case 1
strMilestone = " 'Completed' "
Case Else
strMilestone = " <> 'Completed' "
End Select

When looking at the possible values of a scalar it is preferable to use
Select Case than
multiple If..ElseIf...Else...End If to avoid hidden problems with
misspellings and ranges

If Milestone = 0 Then
'
ElseIf Nilestone = 1 Then
' misspelling Option Explicit would cause compiler eror unless there
' really is a variable called Nilestone
ElseIf Milestone >= 22 And Nilestone <= 34 Then <--- range?
'
ElseIf Nilestone >= 20 And Milestone <= 22 Then ' <---
range?
'
Else
'
End If

vs


Select Case Milestone
Case 0
'

Case 1
'
Case 20 To 22
'
Case 23 To 34
'
Case Else
'
End Select

Regards John

 

[Guest]

Thanks for the reply John. To be on the safe side, I implemented a Select
Case but unfortunately, this has not fixed the problem. It still recognises
Completed but not the <> Completed or All. If I enter the underlying query,
while strMilestone holds a value, it only works for completed. It's as if it
doesn't like the way the other two values are written.

Any further suggestions would be great.
Sandy

 

[stefan hoffmann]

hi Sandy,

The user can select to see "Completed", "Uncompleted" or "All" Milestones in
the report. When the user selects one of these values from a combo box, I am
saving that value in a global called strMilestone. I then have a function
called getMFilter() that returns the value. In the query for the report I
call getMFilter in the Milestone column.

The function returns a string in your query condition. This includes the
string delimiter. So the actual conditions will look like the following
in the query designer (SQL view):

This works fine when I send the value "Completed" but I can't get it to work
for Not Completed or All. Here is the code I am using to set the value.
If Me.cmbMilestone = 0 Then 'All
strMilestone = ""

[Milestone] = ""

ElseIf cmbMilestone = 1 Then 'Completed
strMilestone = "Completed"

[Milestone] = "Completed"

Else
strMilestone = "<> 'Completed'" 'Not completed

End If

Your tested field contains wheter "" nor "<> 'Completed'".

Drop your condition in the query and use:
DoCmd.OpenReport "..", acViewPreview, , "[Milestone] " & strMilestone


mfG
--> stefan <--

 

[John Griffiths]

I think that Stefan's post will solve your problem.

I was really trying to resolve the case that one way worked OK before
tackling your solution.

My reading of Stefan's Post :-

Select Case cmbMilestone ' Assuming RowSource something like
"0;All;1;Completed;2;Uncompleted"
Case 1
strMilestone = " [Milestone] = 'Completed' "
Case 2
strMilestone = " [Milestone] <> 'Completed' "
Case Else
strMilestone = ""
End Select

DoCmd.OpenReport "..", acViewPreview, , strMilestone

John

 

[Sandy H]

Hi Stefan
Thanks for the reply. The report that I am applying the filter to is a
subreport, within the main report so I don't think this will work. That is
why I set it up as a condition in the subreports underlying query. Any
other ideas?

Sandy

hi Sandy,

The user can select to see "Completed", "Uncompleted" or "All" Milestones
in
the report. When the user selects one of these values from a combo box,
I am
saving that value in a global called strMilestone. I then have a
function
called getMFilter() that returns the value. In the query for the report
I
call getMFilter in the Milestone column.

The function returns a string in your query condition. This includes the
string delimiter. So the actual conditions will look like the following
in the query designer (SQL view):

This works fine when I send the value "Completed" but I can't get it to
work
for Not Completed or All. Here is the code I am using to set the value.
If Me.cmbMilestone = 0 Then 'All
strMilestone = ""

[Milestone] = ""

ElseIf cmbMilestone = 1 Then 'Completed
strMilestone = "Completed"

[Milestone] = "Completed"

Else
strMilestone = "<> 'Completed'" 'Not completed

End If

Your tested field contains wheter "" nor "<> 'Completed'".

Drop your condition in the query and use:
DoCmd.OpenReport "..", acViewPreview, , "[Milestone] " & strMilestone


mfG
--> stefan <--

 

[stefan hoffmann]

hi Sandy,

Thanks for the reply. The report that I am applying the filter to is a
subreport, within the main report so I don't think this will work. That is
why I set it up as a condition in the subreports underlying query. Any
other ideas?

Set the recordsource property of your subreport in his on open event:

Private Sub Report_Open(Cancel As Integer)

Me.RecordSource = "SELECT * FROM [Query] WHERE " & getMFilter()

End Sub

with

If Me.cmbMilestone = 0 Then 'All
strMilestone = "1 = 1"
ElseIf cmbMilestone = 1 Then 'Completed
strMilestone = "[Milestone] = 'Completed'"
Else
strMilestone = "[Milestone] <> 'Completed'" 'Not completed
End If

to determine the correct strMilestone.


mfG
--> stefan <--

 

[Sandy H]

Hi Stefan
Your solutions seems quite logical but as soon as a try to set the
recordsource of the subform in the OnOpen Event, I get the following error:
Error 2191 - You can't set the recordsource property after printing has
started

So, I am still having no luck in getting this report filtered.

Sandy

hi Sandy,

Thanks for the reply. The report that I am applying the filter to is a
subreport, within the main report so I don't think this will work. That
is
why I set it up as a condition in the subreports underlying query. Any
other ideas?

Set the recordsource property of your subreport in his on open event:

Private Sub Report_Open(Cancel As Integer)

Me.RecordSource = "SELECT * FROM [Query] WHERE " & getMFilter()

End Sub

with

If Me.cmbMilestone = 0 Then 'All
strMilestone = "1 = 1"
ElseIf cmbMilestone = 1 Then 'Completed
strMilestone = "[Milestone] = 'Completed'"
Else
strMilestone = "[Milestone] <> 'Completed'" 'Not completed
End If

to determine the correct strMilestone.


mfG
--> stefan <--

 

[stefan hoffmann]

hi Sandy,

Your solutions seems quite logical but as soon as a try to set the
recordsource of the subform in the OnOpen Event, I get the following error:
Error 2191 - You can't set the recordsource property after printing has
started

Oops. My fault.

An others solution:

Create a query called SubReportSource and assign it as RecordSource to
your subreport.

Before opening the report, reset its SQL statement:

CurrentDb.QueryDefs.Item("SubReportSource").SQL = "SELECT * FROM table
WHERE " & strMilestone


mfG
--> stefan <--

 

[Sandy H]

Hi Stefan
Thank you so much for your assistance with this. I wound up using your last
solution and found a way around the Error 2191. Apparantly, it was loading
the subform twice (an Access thing) so I used a small piece of code to test
if the report was already open before setting the recordsource. This
finally did the trick for me.
I really appreciate your help.
Thanks
Sandy