Multi Select Form Parameter to build Query

[Basshopper]

I have a form called Parameter, It has 3 list boxes all set to multi select call Customer ID, Assembly # and Quote #, How do I run code to build this query or run my current query posted below, This query then pulls the data to a sub form. What is best way??? thanks in advance for your help

SELECT [t_BOM Part Cross].[Customer ID], [t_BOM Part Cross].[Assembly #], [t_BOM Part Cross].[quote=#], [t_Customer Profile].[Company Name]
FROM [t_Customer Profile] INNER JOIN [t_BOM Part Cross] ON ([t_Customer Profile].[Customer ID] = [t_BOM Part Cross].[Customer ID]) AND ([t_Customer Profile].[Customer ID] = [t_BOM Part Cross].[Customer ID]) AND ([t_Customer Profile].[Customer ID] = [t_BOM Part Cross].[Customer ID]) AND ([t_Customer Profile].[Customer ID] = [t_BOM Part Cross].[Customer ID]) AND ([t_Customer Profile].[Customer ID] = [t_BOM Part Cross].[Customer ID])
GROUP BY [t_BOM Part Cross].[Customer ID], [t_BOM Part Cross].[Assembly #], [t_BOM Part Cross].[quote=#], [t_Customer Profile].[Company Name]
HAVING ((([t_BOM Part Cross].[Customer ID]) Like [Forms]![Parameter]![Customer ID] & "*") AND (([t_BOM Part Cross].[Assembly #]) Like [FORMS]![Parameter]![Assembly #] & "*") AND (([t_BOM Part Cross].[quote=#]) Like [FORMS]![Parameter]![quote=#] & "*"))
ORDER BY [t_BOM Part Cross].[Customer ID], [t_BOM Part Cross].[Assembly #], [t_BOM Part Cross].[quote=#];

 

[SeeThru]

Id use a loop to build a text string from the combo box....from memory it's something like

Dim x As Variant, strTemp As String

For Each x In Combo.ItemsSelected
strTemp = strTemp & FieldListCombo.ItemData(x) & ", "
Next x
If strTemp <> "" Then
strTemp = Mid(strTemp, 1, Len(strTemp) - 2) 'trim trailing comma
End If

Then use that string to build and SQL where clause and open your form based on that, or use the SQL string as a filter for the subform.

 

[dhookom]

There are several suggestions in this forum's FAQs

http://www.tek-tips.com/faq.cfm?pid=701.

I believe there are others in the FAQs for Access Reports.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[Basshopper]

The other issue I am having with this query is when I put in the Quote # say T7634 I get that one and T7634A I added the Select Distinct to the query but still getting both records, How can I get exact as I put in my paramter box.

thanks

 

[PHV]

How can I get exact as I put in my paramter box
Don't use the Like operator with wildchar but the = operator for exact match.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Basshopper]

I put the = in place of the Like and now it returns no records, I remove the wildcard and it works. I do need to beable to ask for exact and all with no matching value

thanks

 

[dhookom]

Basshopper,
Did you ever tell us which of the many solutions you are implementing?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[Basshopper]

After talking with the users of this, They informed me that it would be better to do this another way so might have to do this on the back end of the process not sure yet,I have some other items to change in the database first then will get back to this. This was a project I did almost a year ago so have not touched it since then been working great, but with all things manmade change is inevitable suffering is optional. So just getting back into this and I will have more questions, hang in with me

thanks

 

[FancyPrairie]

Check out this FAQ faq181-5497 It contains a function that will build the Where clause for you. It works for single and multi-select listboxes, combo boxes, text boxes, ranges, option groups, and check boxes. All you have to do to make it work is to:

1. Create a new module and copy the function from the FAQ and paste it into the new module. Name the module something like basBuildWhere.

2. Set the tag properties of your controls as specified in the FAQ. It is important that you do this correctly because this is the key to making it work.

3. Open the report as specified in the FAQ. Something like this: DoCmd.OpenReport "YourReport",,,BuildWhere(Me)

 

[Basshopper]

I actually tried this but kept getting a compile error on the replace command of the code. I am using access 97 so not sure if that was it or not. This could work for what I am going to need and was going to contact you but have been slammed and did not get back to it. I could try it again when I get back at it this weekend,