Web Development

Ask something
Discussion Locked

Problem with a subquery

0 helpful
5 replies

by viplav76

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

Dear Sir/Madam,
I am developing an application in ASP. The database I am using is access. I would be grateful if you could help me solve a small problem. My problem is in MS-Access.
I have a table which has certain values in a particular field. These values are numbers separated by an 'Or'. They are inserted into this table from a web page (throught ASP). I need them to be passed to an access query (procedure) as a subquery.The problem is when there is a single value (without OR) the query executes perfectly, however when more that 1 values are present separated with an 'Or' the query fails to return any result.
When the values are manually typed in the 'criteria' dialog, the query returns the results, however when they are passed in the form of a sub query (SELECT[]), & the field has more than 1 value (v1 Or v2 Or v3) it does not return any result.
Kindly help me out of this problem. Alternatively please send me addresses of sites/people who can help me solve this problem.

Awaiting your reply.
Viplav Kambli

5 replies

by sl-campbell

Could you post a comment with the actual complete SELECT statement and it's criteria?
You written: "v1 Or v2 Or v3"

If the criteria has been written exactly like this, (that is: WHERE myField = 'v1' Or 'v2' Or 'v3') then it needs to be changedto:

WHERE myField = 'v1' Or myField = 'v2' Or myField = 'v3'

Make sure you use the single quotes around the criteria for a text field.

Also, for a sub criteria you may need to us the Any or All equations:

SELECT * FROM Orders
WHERE [CustID] =
ANY(SELECT [CustID] FROM Customers
WHERE CustID= 'SomeCustomer');

by viplav76

I shall try the suggestion & get back to you, However, the operator 'Any' seems to be the key thing, as that was the only thing i had not tried. You understood the problem well enough, however note that the values are sent by ASP in the form of a text string which can be saved in any field in the database. This string has all the values which can be separated by an 'Or' keyword or any other method that may be required, The entire string needs to be used as a parameter. I am not accepting this answer simply to make my reply reach you. Thank you.
regards
Viplav

by bob sellman

I think that your real problem is the handling of the answers on the web page. It sounds like on the web page the user can enter more than one value and that is translated into "v1 or v2" which is saved in one field on your table.

Redesign your database by adding a table (Values) that contains two columns: ID (which refers to the main record in the main table) and value. The ASP should then save a separate record in Values for each value entered by the user. So in the example there would be two records: ID1, v1 and ID1, v2. (Note that no primary key is needed and ID1 should be indexed with duplicates OK.)

Now you can easily write a query that combines the tables and lets you select the main records of interest.

A far less pleasant method of handling this is to use VBA code that will first make a list of individual values from the "v1 or v2" text saved in the field (by looking for the "or" in the field in order to parse the field using the MID$() statement. Then in VBA you create and execute the SQL statement that performs the selection you want.

The separate table is a much better design.

by viplav76

Thank you for your reply, However i was avoiding to do it as being an application on the Internet, i felt the design would become very complex due to presence of multiple users using the same application. You understood the problem well enough, however note that the values are sent by ASP in the form of a text string which can be saved in any field in the database. This string has all the values which can be separated by an 'Or' keyword or any other method that may be required, The entire string needs to be used as a parameter. I felt the database option that you suggested would make it unvialble.I am not accepting this answer simply to make my reply reach you. Thank you.

regards
Viplav

This discussion is closed to new replies.