Sending "All" parameter to query 1

[wagnj1]

Hello!!!
I've been working on an Access db that takes in quarterly business plan information from different users (managers) and allows an administrator to compile reports. I've used Forms for data entry. I have a "create report" feature that pops up a report menu. In this menu are preformed reports. When you select to view a certain report, it pops up a parameter form. One of the fields in the parameter form is a combo box, and I would like to include an "All" option that will query for all of the fields in that box. I'v been trying different ways to do this and I'm stuck. Help me please!!!
Email me or post a reply...

 

[cghoga]

2 ideas come to mind

1.can you set the default value of the combo to "ALL" and put a condition in your code that says if the combo = "ALL" then do... otherwise, do....

or

2.another method would be to create a table with the values you need to show in your combo and include "ALL" as a value in the table. then set the control source of the combo to be that table. then set your conditional statement within your code.


hope these ideas help.

 

[cghoga]

2 ideas come to mind

1.can you set the default value of the combo to "ALL" and put a condition in your code that says if the combo = "ALL" then do... otherwise, do....

or

2.another method would be to create a table with the values you need to show in your combo and include "ALL" as a value in the table. then set the control source of the combo to be that table. then set your conditional statement within your code.

 

[wagnj1]

Thanks for your input...I have already set it up so that "All" is an option in the combo box. I have created a code expression for the button you click once you are ready to create the report (after selecting parameters) so that you can create an If...Then kinda thing. But IF this combo box equals "All", then what??? What do I change so that the query will actually include all of the fields??

 

[cghoga]

first, if the combo value = "ALL" or whatever you want it to be set to, set the field to NULL.

Me.YourCombo.Value = Null

then, in your query criteria where you reference the field value you want to query for, put an iif is null condition.

something like this....

Like IIf([Forms]![YourForm]![YourCombo].[value] Is Null,"*",[Forms]![YourForm]![YourCombo].[value])

within your form, your code that generates the report will look something like this:

If Me.YourCombo.Value = "All" Then
Me.YourCombo.Value = Null
DoCmd.OpenReport "YourReport"
'Me.Combo6.Value = "Yeh"
Else
DoCmd.OpenReport "YourReport"
End If

hope this helps.

 

[wagnj1]

thanks man, I'll try that out and get back to you

 

[wagnj1]

awesome!! That worked like a charm, thanks a lot!!!!

 

[CARD66]

This does not work does anyone know why ??
Like "* [forms]![SelectProjectF]![OType]*"

I'm trying to pass a parameter from a Text Box OType with a wildcard on the front and back?

Thanks

 

[CARD66]

Sorry, I posted this question in the wrong place. I just signed up and did not see where to post a new question.

 

[cghoga]

That's ok.

Try this...

Like "*" & [forms]![SelectProjectF]![OType].[Value] & "*"

I'm pretty sure you need the '[Value]' tagged on to the end for it to work.

hope this helps you.

 

[CARD66]

I don't know if you got my last reply but thanks again.

 

[dcurtis]

I have been looking for this also, thanks.