How do I add another criteria to the WHERE clause?

Question

0.00/5 (No votes)

See more:

Sub UpdateX() 
     
        Dim dbs As Database  
     
        Set dbs = OpenDatabase("SOShops.mdb") 
         
        dbs.Execute "UPDATE Employees " _ 
            & "SET Payrate = 12 " _ 
            & "WHERE DaysAtWork= 10 " _
            & "WHERE HoursePerDay = 8 ;" 
             
        dbs.Close 
     
    End Sub

What I have tried:

I tried to add another criteria (namely "HoursPerDay") to the WHERE Clause to narrow down on my choice but to no avail.

Posted 26-Apr-23 16:07pm

Jeffery Tengen

Updated 26-Apr-23 18:50pm

Add a Solution

2 solutions

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)

Dave Kreskowiak · Accepted Answer · 2023-04-26T17:19:00

Solution 1

How about adding an AND clause?

SQL

UPDATE Employees
    SET Payrate = 12
    WHERE DaysAtWork = 10
      AND HoursPerDay = 8

Posted 26-Apr-23 17:19pm

Dave Kreskowiak

Comments

Jeffery Tengen 27-Apr-23 1:50am

Appreciate your help. How exactly could i put this piece in VBA Code, please?

Dave Kreskowiak 27-Apr-23 7:37am

You can't be serious? Look at your own code and see where that one piece of the puzzle fits.

Jeffery Tengen 27-Apr-23 8:44am

Appreciate it

OriginalGriff · Accepted Answer · 2023-04-26T18:50:00

To expand on what Dave has rightly said...
An SQL statement can only have one WHERE clause, so you need to expand the WHERE expression using the logical operators that SQL supports: AND and OR.

They both have the same format:

SQL

WHERE <Expression1> <Logical operator> <Expression2>

AND combines expressions so the result is TRUE only if both expressions are TRUE. If the first expression is FALSE, the second is not evaluated.

OR combines expressions so the result is TRUE if either of the expressions is TRUE. If the first expression is TRUE the second is not evaluated.

You can chain there together along with brackets to get complex WHERE clauses:

SQL

WHERE a AND b AND (c OR d)

Will be TRUE only if a and b are TRUE, and at least one of c and d are TRUE

See here: SQL Operators[^]