Querydef - multiple parameter

[Russie]

Hi.

I'm using queryDef to run a query.

'Retrieve DailyTrans
Set qrydef = db.QueryDefs("2_DailyTrans&quot
qrydef("Region&quot = strRegionParam
qrydef("StartDate&quot = startDate
qrydef.Execute

When I use strRegionParam = "D Or 51" the return is null
when I use strRegionParam = "D" I get a return.

How do I express my strRegionParam = "D Or 51" so that it reads both parameters into my query?

A star for the correct answer.

Thanks for anything.

Russie

 

[jrbarnett]

qrydef("Region&quot = "D Or Region = 51"

John

 

[Russie]

Thanks for the reply John but it didn't work...

..and I want to read the "D or 51" into a string variable before the query is executed, as detailed in the header post.

 

[mpastore]

Hey Russie,

I tried a number of variations using In([myparm]) but cannot get it working. One thing you might want to consider is dynamic sql like:


sSQL = &quot;select * from mytable where startdate = #<<mystart>># and region in(<<myregions>>)&quot;

sSQL = replace(sSQL,&quot;<<mystart>>&quot;,startdate)
sSQL = replace(sSQL,&quot;<<myregions>>&quot;,strRegionParam)

currentdb.Execute(sSQL)

Only difference is that you would have to pass strRegionParm as something like &quot;'D','51'&quot; instead of using Or.





Mike Pastore

Hats off to (Roy) Harper

 

[Russie]

Thanks for the response Mike.

Using SQL opens up another issue that I've explored but not yet solved.

Currently the query is based on Linked Tables which communicate with SQL server.

I have an SQLOLEDB connection object set up and the SQL that queries SQL Server works fine. What I can't seem to do is to simply append the SQL Server recordset into an access table, I have to define fiels variables and read the recordset in field by field from SQL Server into Access, which takes too much time.

If you know how to directly append a query into Access to SQL Server my problem will have been solved with style.

I can give examples if you think that you have a solution.