SQL query based upon some criteria

Question

sir, it like searching at function

i need to search the table which has data stored in ms access table. i have create a text box for the contract no,username and date and need to search the table according to contract no or username or date.

for that i need a coding with solve the problem in single sql command. i have try it with where help is a table,search-test is form.

when the help(table).cont_no(field) is equal to search-test(form name).cont_no(text box field)

SELECT * FROM Help WHERE (((Help.cont_no)=[Forms]![search-test]![cont_no])) OR
(((Help.username)=[Forms]![search-test]![username]) or
((Help.date)=[Forms]![search-test]![cbo_date]));

hope you will understand my view

Answer 1

I think I understand that you want to search based on any combination of those criteria. I suggest you try this:

SELECT *
FROM Help
WHERE
    ([Forms]![search-test]![cont_no] = '' OR Help.[cont_no]=[Forms]![search-test]![cont_no])
AND
    ([Forms]![search-test]![username] = '' OR Help.[username]=[Forms]![search-test]![username])
AND
    ([Forms]![search-test]![cbo_date] = '' OR Help.[date]=[Forms]![search-test]![cbo_date])

This query will search for a combination of your search boxes if you have entered values in them. If they are blank, they are excluded from the search criteria. You may need to adjust it as required for the "empty" status of your fields. I have assumed "empty" to be a blank string

UPDATE If you want to search for any combination of the criteria you can use OR clauses as follows:

SELECT *
FROM Help
WHERE
    Help.[cont_no]=[Forms]![search-test]![cont_no]
OR
    Help.[username]=[Forms]![search-test]![username]
OR
    Help.[date]=[Forms]![search-test]![cbo_date]

Answer 2

thanks a lot for immediate reply.

i have give a shot to code. it's working if all the feilds is entered by user. i need a code that combine all the sql command. user may enter in the one field or two field or all the three fields. i need to search the database with one field query or two feild query or three feilds query.

i have try it with where help is a table & search-test is form.contract no,username & date of feild in database and forms. where the help(table).cont_no(field) is equal or not equal to search-test(form name).cont_no(text box field)

Answer 3

I am adding an answer because I cannot comment on your post.

Are you still having trouble? If you could please post the structure of your tables I can be more concise in my answer.

Alternatively, if my post answers your question adequately please mark it as the answer so other will know that your issue has been resolved :D