In operator question

T

Tony

Hi,

Does anyone know how to use the In() operator in a
parameterized option in the sql where clause in MS Access
2000. For some reasons, When I try to use the In()
operator and test it with a list of values, I get 0
records return. However, if I only enter one value, a
correct number of records returned.

Thanks in advance.
Tony
 
E

Eric Butts [MSFT]

Hi Tony,

Could you post your SQL Syntax for review.

Here are examples out of Northwind that work just fine:

SELECT Orders.OrderID, Orders.CustomerID
FROM Orders
WHERE (((Orders.OrderID) In ([value1])) AND ((Orders.CustomerID) In
([customer])));


SELECT Orders.OrderID, Orders.CustomerID
FROM Orders
WHERE (((Orders.OrderID) In ([value1],[value2])));


I hope this helps! If you have additional questions on this topic, please
respond back to this posting.


Regards,

Eric Butts
Microsoft Access Support
(e-mail address removed)
"Microsoft Security Announcement: Have you installed the patch for
Microsoft Security Bulletin MS03-026? If not Microsoft strongly advises
you to review the information at the following link regarding Microsoft
Security Bulletin MS03-026
<http://www.microsoft.com/security/security_bulletins/ms03-026.asp> and/or
to visit Windows Update at <http://windowsupdate.microsoft.com/> to install
the patch. Running the SCAN program from the Windows Update site will help
to insure you are current with all security patches, not just MS03-026."

This posting is provided "AS IS" with no warranties, and confers no rights


--------------------
| Content-Class: urn:content-classes:message
| From: "Tony" <[email protected]>
| Sender: "Tony" <[email protected]>
| Subject: In operator question
| Date: Tue, 21 Sep 2004 14:45:40 -0700
| Lines: 12
| Message-ID: <[email protected]>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="iso-8859-1"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| thread-index: AcSgJFbNMdZ9Bkz8T9y9ckcAEcvpog==
| X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4910.0300
| Newsgroups: microsoft.public.access.queries
| Path: cpmsftngxa06.phx.gbl
| Xref: cpmsftngxa06.phx.gbl microsoft.public.access.queries:214049
| NNTP-Posting-Host: tk2msftngxa14.phx.gbl 10.40.1.166
| X-Tomcat-NG: microsoft.public.access.queries
|
| Hi,
|
| Does anyone know how to use the In() operator in a
| parameterized option in the sql where clause in MS Access
| 2000. For some reasons, When I try to use the In()
| operator and test it with a list of values, I get 0
| records return. However, if I only enter one value, a
| correct number of records returned.
|
| Thanks in advance.
| Tony
|
|
 
T

Tony

Hi Eric,

Here is my query:

SELECT Temp_Sales.Cl_key
FROM Temp_Sales
WHERE (((Temp_Sales.Cl_key) In ([enter value:])));

The where clause is generated from the Design view of MS
Access.

I entered the values as '4385,2591' in the msgbox for
Cl_key and get 0 record. When I entered '4385', I get a
correct result.

Thanks for your help,
Tony
-----Original Message-----
Hi Tony,

Could you post your SQL Syntax for review.

Here are examples out of Northwind that work just fine:

SELECT Orders.OrderID, Orders.CustomerID
FROM Orders
WHERE (((Orders.OrderID) In ([value1])) AND ((Orders.CustomerID) In
([customer])));


SELECT Orders.OrderID, Orders.CustomerID
FROM Orders
WHERE (((Orders.OrderID) In ([value1],[value2])));


I hope this helps! If you have additional questions on this topic, please
respond back to this posting.


Regards,

Eric Butts
Microsoft Access Support
(e-mail address removed)
"Microsoft Security Announcement: Have you installed the patch for
Microsoft Security Bulletin MS03-026? If not Microsoft strongly advises
you to review the information at the following link regarding Microsoft
Security Bulletin MS03-026
<http://www.microsoft.com/security/security_bulletins/ms03 -026.asp> and/or
to visit Windows Update at
Click to expand...
 
D

Duane Hookom

You can't expect this to work with more than one value entered in the
parameter. "4385,2591" will not match any single value.
You might have some success with
WHERE Instr([Enter Values],Cl_key) >0;

--
Duane Hookom
MS Access MVP


Tony said:
Hi Eric,

Here is my query:

SELECT Temp_Sales.Cl_key
FROM Temp_Sales
WHERE (((Temp_Sales.Cl_key) In ([enter value:])));

The where clause is generated from the Design view of MS
Access.

I entered the values as '4385,2591' in the msgbox for
Cl_key and get 0 record. When I entered '4385', I get a
correct result.

Thanks for your help,
Tony
-----Original Message-----
Hi Tony,

Could you post your SQL Syntax for review.

Here are examples out of Northwind that work just fine:

SELECT Orders.OrderID, Orders.CustomerID
FROM Orders
WHERE (((Orders.OrderID) In ([value1])) AND ((Orders.CustomerID) In
([customer])));


SELECT Orders.OrderID, Orders.CustomerID
FROM Orders
WHERE (((Orders.OrderID) In ([value1],[value2])));


I hope this helps! If you have additional questions on this topic, please
respond back to this posting.


Regards,

Eric Butts
Microsoft Access Support
(e-mail address removed)
"Microsoft Security Announcement: Have you installed the patch for
Microsoft Security Bulletin MS03-026? If not Microsoft strongly advises
you to review the information at the following link regarding Microsoft
Security Bulletin MS03-026
<http://www.microsoft.com/security/security_bulletins/ms03 -026.asp> and/or
to visit Windows Update at
Click to expand...
the patch. Running the SCAN program from the Windows Update site will help
to insure you are current with all security patches, not just MS03-026."

This posting is provided "AS IS" with no warranties, and confers no rights


--------------------
| Content-Class: urn:content-classes:message
| From: "Tony" <[email protected]>
| Sender: "Tony" <[email protected]>
| Subject: In operator question
| Date: Tue, 21 Sep 2004 14:45:40 -0700
| Lines: 12
| Message-ID: <[email protected]>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="iso-8859-1"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| thread-index: AcSgJFbNMdZ9Bkz8T9y9ckcAEcvpog==
| X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4910.0300
| Newsgroups: microsoft.public.access.queries
| Path: cpmsftngxa06.phx.gbl
| Xref: cpmsftngxa06.phx.gbl microsoft.public.access.queries:214049
| NNTP-Posting-Host: tk2msftngxa14.phx.gbl 10.40.1.166
| X-Tomcat-NG: microsoft.public.access.queries
|
| Hi,
|
| Does anyone know how to use the In() operator in a
| parameterized option in the sql where clause in MS Access
| 2000. For some reasons, When I try to use the In()
| operator and test it with a list of values, I get 0
| records return. However, if I only enter one value, a
| correct number of records returned.
|
| Thanks in advance.
| Tony
|
|

.
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Problem with MS Access + Operator in Query 5
Parameter In Operator 3
Problem with the Like Operator 2
not in criteria fails 2
LIKE Operator used with a form 3
explicit operator cast --> Specified cast is not valid 1
bitwise operator in SQL 3
OR operator not working 1

Top