Multiple records for one parameter

[MNJoe]

I am in the Query editor window (design view) and I have on the top of the
window all the tables with all the keys selected and working. In the bottom I
have the fields with one field I wish to enter a parameter. Now I need to
enter from one to several (Work order IDs) values. If I hard code in the line
in the criteria field

IN ("WO12345","WO12346","WO12347")

this works, But if I try to make a parameter to have this entered in

IN([Enter Work Order Ids])

then enter in the work orders when prompted it doesn't work. It will accept
one work order id but not 2 or more. Why is this happening?
When prompted I have tried to enter in

"WO12345","WO12346","WO12347"
or
WO12345,WO12346,WO12347

 

[Stefan Hoffmann]

hi Joe,

If I hard code in the line in the criteria field
IN ("WO12345","WO12346","WO12347")
this works, But if I try to make a parameter to have this entered in
IN([Enter Work Order Ids])
then enter in the work orders when prompted it doesn't work.

This is not possible. The only thing you can do:

use a public function in a standard module, e.g.

Option Compare Database
Option Explicit

Private m_FilterValues As String

Public Funtion CheckValues(AField As Variant) As Boolean

Dim Count As Long
Dim FilterValues() As String

CheckValues = False
FilterValues() = Split(m_FilterValues, ",")
For Count = LBound(FilterValues()) To UBound(FilterValues())
If CStr(AField) = FilterValues(Count) Then
CheckValues = True
Eexit For
End If
Next Count

End Function


So you can use

WHERE CheckValues([yourField])

as criteria. But you have to set m_FilterValues manually.


mfG
--> stefan <--

 

[Jeff Boyce]

The reason it isn't working is because your answer to the Parameter/Prompt
is "WO12345, WO12346, WO12347" ... and I'm guessing that that exact string
does NOT appear in your table/field.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[MNJoe]

Doesn't the prompt get replaced by the string entered in? I can hard code in
the 3 work orders Ids and it works. You only have the double quotes on the
end of the first and last work order Id. I put the double quotes around each
work order id. I was hoping for an easy answer but I guess not.

Thanks
--
MNJoe

The reason it isn't working is because your answer to the Parameter/Prompt
is "WO12345, WO12346, WO12347" ... and I'm guessing that that exact string
does NOT appear in your table/field.

Regards

Jeff Boyce
Microsoft Office/Access MVP

I am in the Query editor window (design view) and I have on the top of the
window all the tables with all the keys selected and working. In the
bottom I
have the fields with one field I wish to enter a parameter. Now I need to
enter from one to several (Work order IDs) values. If I hard code in the
line
in the criteria field

IN ("WO12345","WO12346","WO12347")

this works, But if I try to make a parameter to have this entered in

IN([Enter Work Order Ids])

then enter in the work orders when prompted it doesn't work. It will
accept
one work order id but not 2 or more. Why is this happening?
When prompted I have tried to enter in

"WO12345","WO12346","WO12347"
or
WO12345,WO12346,WO12347

 

[Duane Hookom]

You might be able to implement your strategy using syntax like:
WHERE Instr([Enter Work Order Ids],[WorkOrderIDField])>0

Having said that, IMHO, parameter prompts are never appropriate user
interface.
http://www.tek-tips.com/faqs.cfm?fid=6763


--
Duane Hookom
Microsoft Access MVP
If I have helped you, please help me by donating to UCP
http://www.access.hookom.net/UCP/Default.htm

Doesn't the prompt get replaced by the string entered in? I can hard code in
the 3 work orders Ids and it works. You only have the double quotes on the
end of the first and last work order Id. I put the double quotes around each
work order id. I was hoping for an easy answer but I guess not.

Thanks
--
MNJoe

The reason it isn't working is because your answer to the Parameter/Prompt
is "WO12345, WO12346, WO12347" ... and I'm guessing that that exact string
does NOT appear in your table/field.

Regards

Jeff Boyce
Microsoft Office/Access MVP

I am in the Query editor window (design view) and I have on the top of the
window all the tables with all the keys selected and working. In the
bottom I
have the fields with one field I wish to enter a parameter. Now I need to
enter from one to several (Work order IDs) values. If I hard code in the
line
in the criteria field

IN ("WO12345","WO12346","WO12347")

this works, But if I try to make a parameter to have this entered in

IN([Enter Work Order Ids])

then enter in the work orders when prompted it doesn't work. It will
accept
one work order id but not 2 or more. Why is this happening?
When prompted I have tried to enter in

"WO12345","WO12346","WO12347"
or
WO12345,WO12346,WO12347