I am creating a query that I would like to eventually turn into a report. There are a couple of fields that I am passing values to the query using this type of format [Enter Country]. Currently, when the query runs, the user can enter in the country string. If the user enters US, it only pulls the the records that the country = US. I have two questions about this process in order of importance:

1) How can I allow the user to enter multiple values without setting a limit. For example, one time the user would run the query with just country = US. The next time it would be country = US or country = CA. The next time, it might be 10 countries. Finally, what if the next time, the user wanted to select all countries like using a wild card or something.

2) This method assumes that the user knows all of the distinct values that are kept in the country column, in this example. Is there some way to create a multi-select field that pulls all of the values and allows the user to select any number of options (or all of them) by doing a control+click?

Any direction is greatly appreciated.

Thanks in advance.

Yes.

You want to use a form to allow the user to select parameters for the report - or query.

Create a form with a List Box control using the wizard. Point the data to the Country column, and make sure that the wizard does not take the PK field as well.

Once the Wizard is finished, open the properties dialog box on the list box, and select the data tab, then select the query designer ... next to the Row Source. Right click on the design window and select the Query propoerties dialog box. Change Unique Values to Yes. Then close the dialog box, and the designer and click yes to save changes.

Now the list box will only display one of each country.

On the list box properties dialog, on the Other tab, set Multi Select to Simple. This will allow users to select more than one country at a time from the list.

I am going to go get some code from a post that I was working on yesterday and today that shows how to build a WHERE clause from a multi select list box... brb


mmcdonal

Look it up at: http://wrox.books24x7.com

Okay, try this on the button to open the report based on your query (take that criteria language out of the query):

Dim varItem As Variant
Dim strwhere As String
Dim strreport As String

strreport = "rptYourReportName"
strwhere = ""

'Assume your ListBox is named List0

If List0.ItemsSelected.Count > 0 Then
    For Each varItem In List0.ItemsSelected
            strwhere = strwhere & "[Country] = '" _
            & Me![List0].Column(0, varItem) & "' Or "
    Next varItem
    strwhere = Left(strwhere, Len(strwhere) - 4)
End If

DoCmd.OpenReport strreport, acPreview, , strwhere

This will open the report with the selected countries, and will also open the report with no countries. Don't worry about no data since the list box only takes existing records, so at least one country will show up no matter what. Also, this way you don't have to worry about misspellings. (SP? - I can never spell that word.)

You may want to add a routine to reset the list box to no selections after each report is run so the user doesn't have to deselect them each time they want a new report.

Did that help?


mmcdonal

Look it up at: http://wrox.books24x7.com

Wow! Thanks for the detailed responses. I am going to have to take a look at all of this to see if I can make it work. Thanks again!

I followed the instructions below, but I am a little confused - it runs fine, but when I click on the command button to open the report I am getting error 3075 "syntax error in string in query experssion" I am guessing the problem must in my lay out of the print form, How do I pass the selected data over to the report?
Thank you for your time!

No, it looks like it is a problem with eh syntax somewhere. Can you add this line to the code just before the DoCmd. line:

MsgBox strWhere

Then copy and paste the resulting string to a post for me to review.

mmcdonal

Look it up at: http://wrox.books24x7.com

Yhank you for the quick response!!

It seems if I do not choose any criteria in my list box, the report opens, but if I select any criteria, it gives me the syntax error.

below is the code, I have in my command button:


Private Sub Command8_Click()
Dim varItem As Variant
Dim strwhere As String
Dim strreport As String

strreport = "rptBoroInv"
strwhere = ""
'List Box is placed here List6

If List6.ItemsSelected.Count > 0 Then
    For Each varItem In List6.ItemsSelected
    strwhere = strwhere & "[E_VEH_CD]='" & Me![List6].Column(0, varItem) & "'Or"
    Next varItem
strwhere = Left(strwhere, Len(strwhere) - 4)
End If

DoCmd.OpenReport strreport, acPreview, , strwhere

End Sub

thanks again!

What is the data type in Column0? If it is a PK autonumber field, then you should do this instead:

strwhere = strwhere & "[E_VEH_CD]=" & Me![List6].Column(0, varItem) & " Or "

Then this line: strwhere = Left(strwhere, Len(strwhere) - 4) will take care of the last " Or "

Did that help? If not, can you post the actual string that this returns?

mmcdonal

Look it up at: http://wrox.books24x7.com

Yes, Column0 is the PK! I should have thought of thar\t! here is my code now:


Private Sub Command8_Click()
Dim varItem As Variant
Dim strwhere As String
Dim strreport As String

strreport = "rptBoroInv"
strwhere = ""
'List Box is placed here List6

If List6.ItemsSelected.Count > 0 Then
    For Each varItem In List6.ItemsSelected
    strwhere = strwhere & "[E_VEH_CD]=" & Me![List6].Column(0, varItem) & "Or"
    Next varItem
strwhere = Left(strwhere, Len(strwhere) - 4)
End If

DoCmd.OpenReport strreport, acPreview, , strwhere

End Sub

the exact error I am getting is:

Run-time '3075'
Syntax error (missing operator) in query expression '[E_VEH_CD]=1COr[E_VEH_CD]='.

I was trying to pick E_VEH_CD 1C and 1D
Thanks again, and I really sorry for my lack of knowledge.

The first issue is that you did not add spaces to the string you are building, so you get this:

'[E_VEH_CD]=1COr[E_VEH_CD]='

Instead of this:

'[E_VEH_CD]=1C Or [E_VEH_CD]='

Add spaces here:

... & " Or "

Next, your string ended up being too long. So you got:

'[E_VEH_CD]=1C Or [E_VEH_CD]='

but you wanted to get:

'[E_VEH_CD]=1C'

Or, there was a value in the selection, but it was removed by this code:

strwhere = Left(strwhere, Len(strwhere) - 4)

Your string: '[E_VEH_CD]=1COr[E_VEH_CD]='
probably looked like this: '[E_VEH_CD]=1COr[E_VEH_CD]=12OR'
before it was trimmed.

This is more likely the case, and you need to ADD THE SPACES in the " Or " string, or you can't fix this problem! PLEASE ADD THE SPACES and then post what happened.





mmcdonal

Look it up at: http://wrox.books24x7.com