Filtering report using a form with combo boxes (1 Viewer)

[jamlaw]

I have a report that gets filtered by a form with 2 combo boxes on. If I select data from both the combo boxes the report works fine. If I select data from th e first combo and not the second it works fine. But if I leave the first combo blank and data in the second it doesn’t work. I'd like to get it to have the ability to select either data from 1 or 2 of the combo boxes. The code is;
Dim StrRptWhere as String

If nz([Text91])<>0 then
StrRptWhere = "(Performance.Specialty =[Forms]![Performance Dynamic Report Builder]![Text91])"
end if

If nz([Text94])<>0 then
if nz(StrRptWhere) = 0 then
StrRptWhere = "(Performance.Gender=[Forms]![Performance Dynamic Report Builder]![Text94])"
else
StrRptWhere=StrRptWhere & " AND " &"(Performance.Gender=[Forms]![Performance Dynamic Report Builder]![Text94])"
end if
end if
'check StrRptWhere contains data
If NZ(StrRptWhere)=0 then
msgbox "No criteria selected",vbInformation,"Title"
exit sub
end if

DoCmd.OpenReport " Dynamic Specialty Performance ", acViewPreview, , StrRptWhere

 

[RainLover]

Jam

In order to answer your question you should make it as easy as possible for us to follow.

First thing I noticed was the use of names like "Text91". If you are serious about building a good Database then you need to use a more appropriate naming convention.

The Second is that you have not presented your procedure in the same way you should write and store it. Things like indenting Code and making sure things line up. It is best to write your code in the editor. Then do a Compile to see if you have any mistakes that you can fix before posting. Once your code compiles then copy and paste it in your message. Make sure you use Code tags.

If you do this then things will be so much easier for us to read and consequently help with.

 

[jamlaw]

Hi RainLover

Thanks for the reply. I'm new to this.

Private Sub Command93_Click()
Dim StrRptWhere As String

If Nz([Specialties]) <> 0 Then
StrRptWhere = "(Performance.Specialty =[Forms]![Performance Dynamic Report Builder]![specialties])"
End If

If Nz([Gender]) <> 0 Then
If Nz(StrRptWhere) = 0 Then
StrRptWhere = "(Performance.Gender=[Forms]![Performance Dynamic Report Builder]![gender])"
Else
StrRptWhere = StrRptWhere & " AND " & "(Performance.Gender=[Forms]![Performance Dynamic Report Builder]![gender])"
End If
End If
'check StrRptWhere contains data
If Nz(StrRptWhere) = 0 Then
MsgBox "No criteria selected", vbInformation, "Title"
Exit Sub
End If

DoCmd.OpenReport " Dynamic Specialty Performance ", acViewPreview, , StrRptWhere
End Sub

This is copied direct from code builder. Is this any better?

Cheers

 

[RainLover]

Something else.

Using a Message Box display the result of each "StrRptWhere"

Include the Sub Name in your Code.

State where you are firing the Procedure from. Is it a special Form or the one that contains the Combo boxes.

 

[RainLover]

Is this any better?

Yes it is better.

Now is just looks terrible which is not much better than the first attempt.

Are you actually happy with a command named "Command93" I hope not.

Sorry for being mean but you must admit that yourcode looks nothing like any sample you have seen in a Book or used by Microsoft.

Waiting for a responce to my previous post before moving on.

 

[jamlaw]

Hi

I originally posted the following thread

I have a report that gets filtered by a form with 2 combo boxes on. If I select data from both the combo boxes the report works fine. I'd like to get it to have the ability to select data from 1 or 2 of the combo boxes. The code is;

DoCmd.OpenReport " Dynamic Specialty Performance ", acViewPreview, , "(Performance.Specialty =[Forms]![Performance Dynamic Report Builder]![specialty])and (Performance.Gender=[Forms]![Performance Dynamic Report Builder]![gender])"

and someone kindly gave me the code that I'm using. Hope this helps?

thanks

 

[RainLover]

Hi

I originally posted the following thread

I have a report that gets filtered by a form with 2 combo boxes on. If I select data from both the combo boxes the report works fine. I'd like to get it to have the ability to select data from 1 or 2 of the combo boxes. The code is;

DoCmd.OpenReport " Dynamic Specialty Performance ", acViewPreview, , "(Performance.Specialty =[Forms]![Performance Dynamic Report Builder]![specialty])and (Performance.Gender=[Forms]![Performance Dynamic Report Builder]![gender])"

and someone kindly gave me the code that I'm using. Hope this helps?

thanks

If you are suggesting that it would help me then no it doesn't.

More interested in the results from those message boxes.

 

[jamlaw]

I get the following message;

Run-time error '3075':

Syntax error (missing operator) in query expression ' AND (Performance.Gender=[Forms]![Performance Dynamic Report Builder]![gender])'.

 

[RainLover]

You are not going to answer my questions from post # 4 are you.

 

[jamlaw]

Sorry

It's a form with the combo boxes on.

 

[RainLover]

Someone please jump in.

Getting the right information is like pulling teeth.

I'll leave it with you for a while.

Someone else may jump in.