If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
In operator question
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 |
#3
|
|||
|
|||
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 "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/se...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" | Sender: "Tony" | Subject: In operator question | Date: Tue, 21 Sep 2004 14:45:40 -0700 | Lines: 12 | Message-ID: | 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 | | . |
#4
|
|||
|
|||
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" wrote in message ... 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 "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/se...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" | Sender: "Tony" | Subject: In operator question | Date: Tue, 21 Sep 2004 14:45:40 -0700 | Lines: 12 | Message-ID: | 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 | | . |
Thread Tools | |
Display Modes | |
|
|