ADODB Parameter Queries

[Elysium]

To all,

I have several queries built in A2K that can take different values for Time. I did this so that a user could choose to view reports in the following 3 categories:

Current Week
Month to Date
Year to Date

I obviously don't want to have 3 sets of the same queries. Rather, I would like to adjust the Time criteria directly in the SQL according to the report chosen. I have experimented with pulling in the CommandText, using Replace to change the Where statement and then updating the Command property on the catalog object. But, this is not as flexible as I need. Once I change the criteria, the Replace function will fail because I will no longer know what it is. I want to avoid nasty search functions to extract the text out of the Where statement.

So, is there any way to make a change to the Time criteria by adjusting a parameter?

Thanks,



Randy

 

[cmmrfrds]

There is an example of using a Command Object for executing a stored procedure in thread958-923533 is this what you are looking for?

 

[Elysium]

No. I'm trying to manipulate queries saved in an Access Database, not Stored Procs. I would like to pass a parameter set on the Time field a value such as "IN ('01','02')", but I can't figure out how.

Regards,


Randy

 

[cmmrfrds]

Check this link out. It seems to be what you are asking.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnacc2k/html/adocreateq.asp

 

[formerTexan]

Hi,

I may be overlooking some requirement here, but otherwise why not keep it simple and use a date range in your SQL statement: (SomeDate to TodaysDate).

The user can then pass a SomeDate value as (TodaysDate - 7) or (TodaysDate - 30)

Cheers,
Bill

 

[Elysium]

Bill,

I want to 'pick and choose' fields to set criteria for. The date range would help if it was only Time (which I didn't mention in my original post ... sorry). If I wanted to change the Region I'm looking at, then a range won't help much.

cmmrfdrs,

I checked that link out and it's code that I was experimenting with. However, I'm finding that it's probably impossible to pass a statement such as 'IN' or 'LIKE' to a parameter. Unless you know otherwise.

Thanks,


Randy