multiple criteria query

[orshims]

hi all
my question is i have a query that shows the last record for specific sets of data. this works fine however what i want to achieve is the user to be able to select a number of different values within one field then display this information in a report
i have tried
[enter no] and [enter no] however this doesnt work


please help me

thanks

 

[pbaldy]

Something along these lines?

http://www.baldyweb.com/multiselect.htm

 

[Brianwarnock]

For what its worth this

[enter no] and [enter no]

should have been

[enter no1] or [enter no2]

Brian

 

[orshims]

For what its worth this

should have been

[enter no1] or [enter no2]

Brian

hi again

sorry i should have explained it better i have tried this (as above) however when i run the query it doesnt show the result if i just do one parameter ie [enter quote number] it works if i do [enter quote number] and [enter quote number 2] it asks me the second quote number but doesnt display them
might be me!!!!
thanks

 

[pbaldy]

Brian's post was accurate, but not the way I would go (he probably wouldn't either, but was explaining why your effort failed). For starters, it isn't flexible enough. In the example, you would be limited to 2 selections. If that fits your scenario, then I might use it, but more commonly I might want to choose 1, 2, 3 or whatever. If you set it up with say 5 selections, then the user who only wants 1 still has to enter through the other 4 prompts. If you want to go this way rather than the listbox, you can either use Like and wildcards or something like this to handle prompts left empty:

http://www.mvps.org/access/queries/qry0001.htm

Personally, I lean towards the listbox.

 

[orshims]

Brian's post was accurate, but not the way I would go (he probably wouldn't either, but was explaining why your effort failed). For starters, it isn't flexible enough. In the example, you would be limited to 2 selections. If that fits your scenario, then I might use it, but more commonly I might want to choose 1, 2, 3 or whatever. If you set it up with say 5 selections, then the user who only wants 1 still has to enter through the other 4 prompts. If you want to go this way rather than the listbox, you can either use Like and wildcards or something like this to handle prompts left empty:

http://www.mvps.org/access/queries/qry0001.htm

Personally, I lean towards the listbox.

hi
sorry for my ignorance!!
im not sure what you mean when you say use like and wildcards exactly how do i write this???????
this information is then going to be part of a report based on the information the user has selected
again sorry for my ignorance!!

 

[pbaldy]

A query criteria like:

Like "*" & [enter quote number] & "*"

but that technique works best with text data. If it's numeric, entering 7 will find records 7, 17, 70, etc.

No need to be sorry for your ignorance. We're all ignorant on a topic until we learn about it. The number of things I'm ignorant about far outnumber the things I'm knowledgeable about.

 

[orshims]

A query criteria like:

Like "*" & [enter quote number] & "*"

but that technique works best with text data. If it's numeric, entering 7 will find records 7, 17, 70, etc.

No need to be sorry for your ignorance. We're all ignorant on a topic until we learn about it. The number of things I'm ignorant about far outnumber the things I'm knowledgeable about.

hi again
thanks for that this is to find a single record im guessing so how do i now select several different numbers
ie if i want to display the records for a number of different quote numbers using the same query? or am i barking up the wrong tree????
thanks again in advance

 

[pbaldy]

Well, we're back to separating more than one with OR as Brian noted. In SQL view it might look like

WHERE FieldName Like "*" & [enter quote number] & "*" OR FieldName Like "*" & [enter quote number 2] & "*"

But for the reasons previously given, which you've reinforced by saying "several" and "a number of", I'd use a multiselect listbox, as in the link I posted in post 2.

 

[orshims]

Well, we're back to separating more than one with OR as Brian noted. In SQL view it might look like

WHERE FieldName Like "*" & [enter quote number] & "*" OR FieldName Like "*" & [enter quote number 2] & "*"

But for the reasons previously given, which you've reinforced by saying "several" and "a number of", I'd use a multiselect listbox, as in the link I posted in post 2.

hi
do apologize!!
yes didn't see the link to the sample this is exactly what im looking for thanks ever so much and for your patients
im going offline now

 

[Brianwarnock]

Brian's post was accurate, but not the way I would go (he probably wouldn't either, but was explaining why your effort failed).

Personally, I lean towards the listbox.

Your are 100% correct Paul and it was remiss of me not to point it out more strongly.

Brian

 

[orshims]

Your are 100% correct Paul and it was remiss of me not to point it out more strongly.

Brian

hi guys

ive done what you suggested however i keep getting an error message which reads
syntax error (missing operator) in query expression "(quote number in (47258,47588))"

the full SQL view is below i have altered to include my tables

Private Sub cmdOpenReport_Click()
On Error GoTo Err_cmdOpenReport_Click

Dim strWhere As String
Dim ctl As Control
Dim varItem As Variant

'make sure a selection has been made
If Me.order.ItemsSelected.Count = 0 Then
MsgBox "Must select at least 1 quote number"
Exit Sub
End If

'add selected values to string
Set ctl = Me.order
For Each varItem In ctl.ItemsSelected
strWhere = strWhere & ctl.ItemData(varItem) & ","
Next varItem
'trim trailing comma
strWhere = Left(strWhere, Len(strWhere) - 1)
'open the report, restricted to the selected items
DoCmd.OpenReport "order", acPreview, , "quote number IN(" & strWhere & ")"

Exit_cmdOpenReport_Click:
Exit Sub

Err_cmdOpenReport_Click:
MsgBox Err.Description
Resume Exit_cmdOpenReport_Click

thanks guys appreciate your help

 

[SOS]

"[quote number] IN(" & strWhere & ")"

 

[orshims]

"[quote number] IN(" & strWhere & ")"

hi
i am now getting the error message

data type mismatch in criteria expression!!

any idea!!!!!!!!1

thanks

 

[SOS]

You probably are dealing with a text field and not numeric.

So this:

strWhere = strWhere & ctl.ItemData(varItem) & ","

would need to be changed to this:

strWhere = strWhere & Chr(34) & ctl.ItemData(varItem) & Chr(34) & ","

 

[Brianwarnock]

Paul's sample code told the user how to handle text

'Use this line if your value is text
'strWhere = strWhere & "'" & ctl.ItemData(varItem) & "',"

Brian

 

[orshims]

You probably are dealing with a text field and not numeric.

So this:

strWhere = strWhere & ctl.ItemData(varItem) & ","

would need to be changed to this:

strWhere = strWhere & Chr(34) & ctl.ItemData(varItem) & Chr(34) & ","

hi again

thank you very much for that this works fine will probably need to ask a few more things as i go on!!!!!!!!!!!

thanks again