How to deal with update records with various conditions?

Question

1.00/5 (1 vote)

See more:

VB

sql = "update [note] " & _
" set flag='on' " & _
" where doc_type='credit' " & _
" and doc_type='debit' and doc_no like " & "'" & text1 & "%'"

This code doesn't return any result though I'm having records in database...

DOC_NO  	DOC_TYPE  RETURN	FLAG                
121001	        DEBIT                   off    //on
666	        CREDIT    121002        off    //on
122004		DC                      off
888		CREDIT    121005        off    //on
122006		DC                      off

Now I want to make flag 'on' of those records which are
[1] credited and
[2] debited as well as having doc_no='121%'(for ex. text1 has 121 typed)
I tried above code which is not working ...can anyone help me.....

Posted 22-May-13 2:27am

surkhi

Updated 22-May-13 6:18am

Maciej Los

v3

Add a Solution

Comments

[no name] 22-May-13 8:31am

What sort of a "result" are you expecting from an UPDATE statement?
What exactly does "is not working" mean?

ZurdoDev 22-May-13 11:04am

That's because you have "WHERE doc_type='credit' AND doc_type='debit'" It can't be both credit and debit.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Accepted Answer · 2013-05-22T06:26:00

Solution 1

You can't do it in one command! Why? Please, read ryanb31 comment.

1)

SQL

UPDATE [note] set flag="on" 
WHERE doc_type="credit"

2)

SQL

UPDATE [note] set flag="on" 
WHERE doc_no like "121*"

Please, follow this link: Examples of query criteria (MS Access)[^]

Posted 22-May-13 6:26am

Maciej Los

Comments

surkhi 23-May-13 6:52am

k thanks for the help...

Maciej Los 23-May-13 6:53am

You're welcome ;)