Use logical OR in ADO parameters

[NikkiSpencer]

Hi

I have set up a class that gets data from an MS Access query passing parameters into query to return relevant records to Excel - this all works perfectly - even using wildcards (e.g. S%) - however I want to be able to pass a parameter using logical OR e.g.

following works fine:

[VBA]
LSIP.NewQuery "qForTemplate_PC_Main_Number"
LSIP.AddParamater "@pSection", "PC Primary"
LSIP.AddParamater "@pLevel", "School"
LSIP.AddParamater "@pLevelName", "04047"
LSIP.ImportDataToWorksheet Range("rngPC_NOR")
[/VBA]

But what I WANT to do is something like:
[VBA]LSIP.AddParameter "@pLevelName", "04047" OR "04036"
[/VBA]

but obviously this treats the whole thing like text and returns no records.

I've tried:
"04047 OR 04036"
"'04047' OR '04036'"
etc. but no joy.

Is it possible to pass an OR so in effect two or more parameters can be passed to query?

Many thanks for any help!

By the way the LSIP Object pretty much uses the following code to add attributes (the attributes are added as a collection):

[VBA]
Public Sub ImportDataToWorksheet(rTargetRange As Range)
Dim rs As New ADODB.Recordset
Dim cmd As New ADODB.Command
Dim o As LSIPParam
cmd.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & sDBPathAndName
cmd.CommandType = adCmdStoredProc
cmd.CommandText = sQueryName
' Set parameters
For Each o In oParams
cmd.Parameters.append cmd.createParameter(o.GetParam, adVarChar, adParamInput, 50, o.GetParamValue)
Next
rs.Open cmd
rTargetRange.ClearContents
rTargetRange.Range("a1").CopyFromRecordset rs
rs.Close
Set rs = Nothing
Set cmd = Nothing
End Sub
[/VBA]

Nikki x

[Bob Phillips]

Surely, you need to setup two separate parameters and do the OR in the query.

[NikkiSpencer]

Yes but I would then have to enter potentially loads of extra parameters most of which would probably never be used - also is it not possible to pass IN("04046","04037","04089") etc. ?

No probs if this isn't possible I'll have to think of something else as putting in separate parameters wouldn't really be feasible at moment.

Many thanks for your help though.

Nikki

[Bob Phillips]

Does Access SQL support the IN operator. If so, you could pass the parameter as a comma delimited string, and check that in your query using something like

WHERE myValue IN @myList

[NikkiSpencer]

It does but I tried that and it returns no records - I think it interprets it as e.g. "04047, 04036" instead of "04047", "04036".

No probs, thanks for your help xld I might just have to amend the way I was looknig at it slightly e.g. instead of using 1 query call to pull 5 rows of data I could just call the query 5 times each pulling 1 row with a different parameter - just means it will be slower.

Many thanks again for your help.

Nx

[Bob Phillips]

That is a problem so you need to code around it. You would have to parse the list, maybe put it as strings into a temporary table and join that table in your SQL. I have done in SQL Server, but not Access.

[Bob Phillips]

Nikki, I knocked a si8mple example based on the pubs database. As I said, it is SQL server, but maybe you can do something in Access

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetTitles]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[GetTitles]
GO

USE [pubs]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE PROC [dbo].[GetTitles]
(
	@titleids varchar(500)
)
AS
BEGIN	

	SET NOCOUNT ON

	CREATE TABLE #TempList
	(
		titleid varchar(10)
	)

	DECLARE @titleid varchar(10), @Pos int

	SET @titleids = LTRIM(RTRIM(@titleids))+ ','
	SET @Pos = CHARINDEX(',', @titleids, 1)

	IF REPLACE(@titleids, ',', '') <> ''

		BEGIN

			WHILE @Pos > 0

				BEGIN

					SET @titleid = LTRIM(RTRIM(LEFT(@titleids, @Pos - 1)))
					IF @titleid <> ''

						BEGIN

							INSERT INTO #TempList (titleid) VALUES (CAST(@titleid AS varchar(10)))
						END

					SET @titleids = RIGHT(@titleids, LEN(@titleids) - @Pos)
					SET @Pos = CHARINDEX(',', @titleids, 1)
				END
		END	

	SELECT [title_id], [title] 
	FROM [pubs].[dbo].[titles] AS b
	JOIN #TempList As t On t.titleid = b.title_id	
END
GO

and it is used like so

USE [pubs]
GO

DECLARE	@return_value int

EXEC	@return_value = [dbo].[GetTitles]
		@titleids = N'BU1032,BU1111'
GO

[NikkiSpencer]

Thanks a lot xld. Access SQL isn't quite as advanced as SQL Server!! But I might be able to transfer that to VBA somehow - I will have a go today.

Many thanks for taking the time to do that.

Cheers

Nikki