parameters Help

[dbsql]

Hi,

I want to use parameter and would like get result like this

I have access file that has linked tables of oracle database.

I am trying to create prameter but oracle doesn't like named parameter so I am trying to use " ? " as parameter.

I have query similier to following:

select user_NM, user_Id, Address
from user
where user_NM like ?

but it doesn't work that way. how can i put ? or parameter to act such i can look for any thing such as if i type A then it should give me everything with starting or has A in name?

thanks

Thanks!!

 

[xlbo]

Is this in MS Reporting Services ?

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[dbsql]

yes I am using SQL Reporting Sevices to create reports.

 

[xlbo]

where are you trying to enter the parameter and why are you querying Oracle through Access - can you not connect directly to the Oracle database ? Your issue may well ahve to do with differeing syntax for Access and Oracle.

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[MisterMo]

Actually is not Oracle that does not like named parameter, is the type of connection you are using.

Oracle connection drivers allow for parameter like aramName
SQL will use @ParamName

OLE connection regardless to what database you use will only take ?

the LIKE option works but will have to add the % sign after and/or before.


select user_NM, user_Id, Address
from user
where (user_NM like ?)

where ? = 'BlaBla%'

you have to make sure that in your report when the parameter is passed the % is included.


-Mo

 

[dbsql]

Hi,

I could not have direct access to oracle database and have Access database that has linked table to Oracle.

i tried using (where user_NM like ?) did not return anything.

and also tried where user_NM like '%?%' and it did not work.

thanks

 

[xlbo]

I could not have direct access to oracle database and have Access database that has linked table to Oracle.

That does not make sense. If your linked tables have access to Oracle tables, you have access using the same connection properties.....

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[dbsql]

somehow they have not give me rights and they use some connection with different for oracle Database.

But everything works fine if i don't include parameter. but I would like to have parameter so I can have search capacity in report.

I used ? for parameter but did not work. i also tried using
'?%' and where (user_NM like ?)

how can i make it happen!!

 

[xlbo]

Ok - lets start from the top here

You have an access database with linked tables to an Oracle database
To allow these linked tables to work, there must be a connection set up to allow you to view these tables within access
You should therefore be able to use the connection properties for the linked tables to allow you to query the Oracle database directly

Your syntax for parameters is different in Access than it is in Oracle. I believe this is what is causing you the issue here as you need 1 syntax to pass to access but a different one to pass to Oracle...ergo, you need to connect directly to the Oracle database to allow you to use a paramter in this query.

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question