Select Statement Query Problem

I am trying following query but i am not getting desired result,
and i know that query is not correct..
what am i doing wrong..?
Please correct me...

BD.GName related record only but where CST = 0 or text1(0).text  and  BD.IDT = 0 and code = 0 or code = 1 or code = 2 or BD.Status_Rec = 2 or BD.Status_Rec = 3)

where BD.GName = '" & Text1(3).text & "' and BD.CST =  '" & Text1(0).text & "' and BD.IDTNo = 0 or BD.CST = '0' or BD.Code = 1 or BD.Code = 0 or BD.Status_Rec = 2  or BD.Status_Rec = 3))
LVL 1
crystal_TechAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

IronhoofsCommented:
I have 2 suggestions:

If you create a query string from user data, allways replace single quotes with 2 single quotes to avoid incorrect termination. For example: if text1(0).text contains "The King's Arms Pub", the query string would search for "BD.GName = 'The King'" and probably generate an error.

In addtion, use extra parenthesis around the "OR" statements to group the conditions.


where BD.GName = '" & Replace(Text1(3).text, "'", "''") & "' and BD.CST = '" & Replace(Text1(0).text, "'", "''") & "' and (BD.IDTNo = 0 or BD.CST = '0' or BD.Code = 1 or BD.Code = 0 or BD.Status_Rec = 2  or BD.Status_Rec = 3)

Open in new window

0
aikimarkCommented:
1. you have an extra ) or two at the end of your string

2. You are missing a code=2 check
BD.GName = '" & Text1(3).text & "' and BD.CST =  '" & Text1(0).text & "' and BD.IDTNo = 0 or BD.CST = '0' or BD.Code In (0,1,2) or BD.Status_Rec In (2,3)

3. As Ironhoofs already stated, you need to add parentheses to make your logic right.

4. Is there a difference between the BD.IDT and BD.IDTNo fields?
0
crystal_TechAuthor Commented:
Thanks for helping me.

@aikimark:
i have tried your suggestion,
but its giving me all records. but i want filtered record.
let me Show you whole procedure.. (Please dont put this post on any search engine like google..etc.)

To check STOCK i am using following query.
 
RsTrans.Open "Select BD.BiltyNo, BD.To, BD.Article, BD.Pvt_Mark AS PM_No, BD.Description, BD.Consignee, BD.Weight, BD.Total, BD.BDate, BD.Consignor, BD.Pymt_mode from Bilty_Detail as BD where BD.GroupName = '" & Combo1.text & "' and (Code = 1 or Code = 0) and (Status_Rec = 2 or Status_Rec = 0) and CST = '0' group by BD.BiltyNo, BD.To, BD.Article, BD.Pvt_Mark, BD.Description, BD.Consignee, BD.Weight, BD.Total, BD.BDate, BD.Consignor, BD.Pymt_mode order by BD.BiltyNo", con, adOpenKeyset, adLockPessimistic

Open in new window


then some of field i am editing
like this.
 
rsBilty!code = "2"
rsBilty!Status_Rec = "3"
rsBilty!CST = Text1(0)

Open in new window


Now what i want is
i want both detail which is together.
i hope i explained.

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

aikimarkCommented:
* I don't understand how you can edit a recordset based on a Group By query.

* Why are you using Group By if you don't have any aggregation functions?

* Everything you post is public.

* You may have explained it, but I don't understand it in relation to your question.
0
crystal_TechAuthor Commented:

<<I don't understand how you can edit a recordset based on a Group By query.>>

* no no i am edit record not from query its from another process.

* because to avoid Duplication record i am using GROUP BY


0
aikimarkCommented:
You can also use Distinct
0
crystal_TechAuthor Commented:

Thanks aikimark:
my answer was in my question,
Parameter was wrong..

Please assist me here..
http://www.experts-exchange.com/Programming/Languages/Visual_Basic/Q_27524221.html#a37391592
0
aikimarkCommented:
@crystal_Tech

>>Parameter was wrong..

Does that mean that you solved your own problem?
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.