Multiple Query Criteria

[striker]

I have a database which comprises 6 tables which are linked by 1 field. This field can only ever have 44 unique entries. However I want to produce a query which has a criteria using only 1 or all 44 or any combination of multiples in between.

I know how to write the query the long way, but its not for my use, so I want to keep it simple. ie the user selects his requirements by clicking buttons on a form which then write into a string variable.

Is this the right way or do any of you guru's out there got a better idea. My knowledge of programming is limited so go easy on me.

Thanks in advance.

 

[Jack Cowley]

See if this article is of help to you...

http://support.microsoft.com/search/preview.aspx?scid=kb;en-us;Q210530

 

[striker]

I'm actually using access 97 and have seen the equivelant article. I have tried to use this method but cannot work out how to replace the typed in criteria with the criteria help in my string variable. I thought I had got it sorted but after leaving the query running for 1 hour nothing had happened. The query needs to run with around 17000 records.

I had thought that I could use a function to get the string variable with each search term seperated by commas into the In() function in the criteria in the query window. By using the following statement.

In(myfunctionname) when I try to run this the function name gets surrounded by "".

I then tried,
In(myfunctionname()) this at least ran but always shows no results in the query.

Can I do it this way and if so any ideas where I'm going wrong.

Thanks again

 

[David R]

Synchronicity is fun. I just used a routine to move items from a multi-select list box to a WHERE statement. (borrowed from Jack Cowley's original post I believe, which was borrowed from R.Hicks)

Private Sub cmdYourButtonName_Click()
Dim varItem As Variant
Dim strWhere As String


For Each varItem In Me![YourListBoxName].ItemsSelected
strWhere = strWhere & "NameOfFieldinTable =" & Me![YourListBoxName].Column(0, varItem) & " Or "
Next varItem


strWhere = Left(strWhere, Len(strWhere) - 4) 'Remove the last " Or "


DoCmd.OpenReport "YourReportName", , , strWhere


End Sub

HTH,
David R

[This message has been edited by David R (edited 04-24-2002).]

 

[Jack Cowley]

The code David is using works well so if you can use that then do. If not I have a demo using a multi-select list box and the Function described in the MS Knowledge Base article. If you email me directly I will send you the demo.