Parameterized Query

[rm]

For two tables (over simplified for the posting): T1 = **ID, type,
detail; T2 = **ID, *T1_ID, value where ** = a primary key and * =
foreign key. When I execute the query SELECT T1.type, T1.detail,
T2.value FROM T1 LEFT JOIN T2 ON T1.ID = T2.T1_ID WHERE T1.ID = 0 then
1 row is returned as expected.

When I execute SELECT T1.type, T1.detail, T2.value FROM T1 LEFT JOIN
T2 ON T1.ID = T2.T1_ID WHERE T1.ID = [enter id] and enter a value of 0
at the prompt then all rows are returned from T1. This is not the
expected result - only 1 row should be returned.

I haven't worked with Access since 1998. I used parameterized queries
many times. What is happening to me (all rows returned) in Access 2003
is not expected behavior compared with my past experience.

I checked in the help system and used the "parameters" option from the
"query" menu where the user is to enter the data type for each
parameter. That didn't produce the expected result. Also, I tried
CINT([enter id]) to no avail.

Help!

 

[Tom Wickerath]

Hi RM,

Your query issues *might* be related to the fact that you have used three
reserved words in Access (type, detail and value are all reserved words). I'd
try picking names that are not considered reserved words as one of the first
things:

Problem names and reserved words in Access
http://allenbrowne.com/AppIssueBadWord.html

Don't miss Allen's cool utility that you can download for free.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[rm]

Hi - I used clng. That seemed to do it. Thank you for the heads up on
the reserved words though!

Hi RM,

Your query issues *might* be related to the fact that you have used three
reserved words in Access (type, detail and value are all reserved words). I'd
try picking names that are not considered reserved words as one of the first
things:

    Problem names and reserved words in Access
   http://allenbrowne.com/AppIssueBadWord.html

Don't miss Allen's cool utility that you can download for free.

Tom Wickerath
Microsoft Access MVPhttp://www.accessmvp.com/TWickerath/http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

For two tables (over simplified for the posting): T1 = **ID, type,
detail; T2 = **ID, *T1_ID, value where ** = a primary key and * =
foreign key. When I execute the query SELECT T1.type, T1.detail,
T2.value FROM T1 LEFT JOIN T2 ON T1.ID = T2.T1_ID WHERE T1.ID = 0 then
1 row is returned as expected.

When I execute SELECT T1.type, T1.detail, T2.value FROM T1 LEFT JOIN
T2 ON T1.ID = T2.T1_ID WHERE T1.ID = [enter id] and enter a value of0
at the prompt then all rows are returned from T1. This is not the
expected result - only 1 row should be returned.

I haven't worked with Access since 1998. I used parameterized queries
many times. What is happening to me (all rows returned) in Access 2003
is not expected behavior compared with my past experience.

I checked in the help system and used the "parameters" option from the
"query" menu where the user is to enter the data type for each
parameter. That didn't produce the expected result. Also, I tried
CINT([enter id]) to no avail.

Help!- Hide quoted text -

- Show quoted text -