Parameter Query multiple criteria help

[acss]

In my query design grid i can place a criteria such as 830100 or 850600 and
it will bring all records for these two accounts under this account column.
How do you create a parameter query so a user can type in multiple criteria
such as 830100,850600,870600 so it brings back these records?

 

[Allen Browne]

There's not an efficient way to do this, you can get this approach to work:

PARAMETERS WotNum Text ( 255 );
SELECT tClient.ClientNum
FROM tClient
WHERE "," & Replace([WotNum], " ", "") & "," Like "*," & [ClientNum] & ",*";

 

[KARL DEWEY]

I would have said use this and use only a space to separate the number
entries --
PARAMETERS WotNum Text ( 255 );
SELECT tClient.ClientNum
FROM tClient
WHERE [WotNum] Like "*" & tClient.ClientNum] & "*";

--
Build a little, test a little.

There's not an efficient way to do this, you can get this approach to work:

PARAMETERS WotNum Text ( 255 );
SELECT tClient.ClientNum
FROM tClient
WHERE "," & Replace([WotNum], " ", "") & "," Like "*," & [ClientNum] & ",*";


--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

In my query design grid i can place a criteria such as 830100 or 850600
and
it will bring all records for these two accounts under this account
column.
How do you create a parameter query so a user can type in multiple
criteria
such as 830100,850600,870600 so it brings back these records?

.

 

[Allen Browne]

Trouble is in finding numbers inside other numbers, e.g. client 12, 123,
1234, 12345 etc all match client 12.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I would have said use this and use only a space to separate the number
entries --
PARAMETERS WotNum Text ( 255 );
SELECT tClient.ClientNum
FROM tClient
WHERE [WotNum] Like "*" & tClient.ClientNum] & "*";

--
Build a little, test a little.

There's not an efficient way to do this, you can get this approach to
work:

PARAMETERS WotNum Text ( 255 );
SELECT tClient.ClientNum
FROM tClient
WHERE "," & Replace([WotNum], " ", "") & "," Like "*," & [ClientNum] &
",*";


--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

In my query design grid i can place a criteria such as 830100 or 850600
and
it will bring all records for these two accounts under this account
column.
How do you create a parameter query so a user can type in multiple
criteria
such as 830100,850600,870600 so it brings back these records?

.

 

[acss]

Thanks for the direction yet i have tried placing this within the SQL and
receive a syntax error message for WHERE [WotNum] Like "*" &
tClient.ClientNum] & "*";

Is there something within this statement that appears incorrect since i have
tried using my information such as table names and fields though this error
continues?

Thank you

Trouble is in finding numbers inside other numbers, e.g. client 12, 123,
1234, 12345 etc all match client 12.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I would have said use this and use only a space to separate the number
entries --
PARAMETERS WotNum Text ( 255 );
SELECT tClient.ClientNum
FROM tClient
WHERE [WotNum] Like "*" & tClient.ClientNum] & "*";

--
Build a little, test a little.

There's not an efficient way to do this, you can get this approach to
work:

PARAMETERS WotNum Text ( 255 );
SELECT tClient.ClientNum
FROM tClient
WHERE "," & Replace([WotNum], " ", "") & "," Like "*," & [ClientNum] &
",*";


--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.


In my query design grid i can place a criteria such as 830100 or 850600
and
it will bring all records for these two accounts under this account
column.
How do you create a parameter query so a user can type in multiple
criteria
such as 830100,850600,870600 so it brings back these records?

.

.

 

[John Spencer]

Have you mistyped? You show an extraneous "]" in the where clause
WHERE [WotNum] Like "*" & tClient.ClientNum] & "*";

That should be
WHERE [WotNum] Like "*" & tClient.ClientNum & "*";

OR it should be
WHERE [WotNum] Like "*" & [tClient].[ClientNum] & "*";

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

 

[John Spencer]

Have you mistyped? You show an extraneous "]" in the clause.

WHERE [WotNum] Like "*" & tClient.ClientNum] & "*"

Should be
WHERE [WotNum] Like "*" & [tClient].[ClientNum] & "*"

OR
WHERE [WotNum] Like "*" & tClient.ClientNum & "*"

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County