Query with not correct result

[unforgiven747]

Hi All

i need to use this query to find person,each person has a 3 fields as unique : mem_id,c_id,r_id
mem_id and c_id are as numerical and r_id is string

i use the query but is not works accurate and show another records .

rs.Open "SELECT * FROM Reg where mem_id=" & Val(txtfind.Text) & " or c_id=" & Val(txtfind.Text) & " or r_id='" & Trim(txtfind.Text) & "' ", db, adOpenKeyset, adLockOptimistic

what's wrong this query!?

how can i use query so that i can run query with this 3 fields as simultaneously with accurate result ?

thanks all

[si_the_geek]

Are you sure that you want to be using OR?

[unforgiven747]

Are you sure that you want to be using OR?

what do you mean?

[si_the_geek]

Just like with If statements in VB, when using multiple boolean conditions, you can "join" them using OR, AND, or a mixture of the two (preferably with brackets to specify the order of evaluation).

AND means both items must be true
OR means only one of them needs to be true

[unforgiven747]

Hi si_the_geek
Do i use this query ?

rs.Open "SELECT * FROM Reg where mem_id like '%" & txtfind.Text & "%' or c_id like '%" & txtfind.Text & "%' or r_id like '%" & Trim(txtfind.Text) & "%' ", db, adOpenKeyset, adLockOptimistic

the query at post 1 was not return accurate result ,when i want to show record's field r_id, if mem_id was 44 and r_id 4DB8 , it show 44 in instead of 4DB8 .

is it correct query that i use ?

thanks

[kyrsoronas]

If you need to return only one record and not multiple I believe you need to use a WHERE clause in the query using the unique ID of the record you need?
If all the information you need is in the same row that is.

[si_the_geek]

As you are using OR, the query will probably be returning multiple records.

Based on what you have said, it sounds like your code is only reading the first of those record(s), which isn't the one you actually want to see.

You should show all of the records that are returned (even if you do manage to get just one record, as it might not always happen), and you should probably change your Where clause somehow (perhaps in multiple ways) so that you can specify more accurately what you want to return.

[unforgiven747]

let me clear my means
this query use for finding record to edit ,so it returns one record .each member has a 3 unique fields mem_id,c_id,r_id . user can search members with this 3 fields . maybe user wants to search based on mem_id or c_id or r_id,any how it should show member's info .
I think I should use separate query based on each field.like this and use option control to get better result:

rs.Open "SELECT * FROM Reg where mem_id=" & txtfind.Text & " ", db, adOpenKeyset, adLockOptimistic
rs.Open "SELECT * FROM Reg where c_id=" & txtfind.Text & " ", db, adOpenKeyset, adLockOptimistic
rs.Open "SELECT * FROM Reg where r_id='" & txtfind.Text & "' ", db, adOpenKeyset, adLockOptimistic

thanks all

[si_the_geek]

If you want to make sure a query returns only one record, you need to do the following:

• Don't use multiple conditions joined with OR as you did before, because that allows multiple records. Using AND is usually valid (but not always), and using OR is occasionally valid (but not in the way you had it).
• Don't use things such as LIKE, because that also allows multiple records (eg: LIKE '%44%' will match records that have the values 44 and 144 and 447, etc)
• Make sure that the field you are checking only has unique values. If it is possible for a field to have exactly the same value in different records, you will (sometimes) get multiple records

Using just one of the queries in your last post will deal with the first two items (and make it easier for the user to specify what they want), but might not deal with the last item - you will need to check the values in the table and/or the table design to be sure.

If there is any chance that multiple records could be returned (eg: the values in c_id can be repeated), then you should provide some kind of way for the user to select which of the records they want to edit.

[unforgiven747]

Dear si_the_geek
thanks 4 your advice