Update SQL/VBA with multiple where clause

[jsdba]

I have an update sql statement that isn't quite working properly.

My where clause has 3 criteria.
*print = -1
*stDocCriteria (project_num and client_id)

Below is what the code looks like.

updateSQL = "update tblTimedTasks set printed =-1, invoice_date = Now() where print = -1 and " & stDocCriteria

Add watch: so you guys can see "stDocCriteria"

updateSQL = "update tblTimedTasks set printed =-1, invoice_date = Now() where print =-1 and ([project_num]= '140012' And [client_id] = 87)"

I want to only updates records that meet all three criteria. The above sql not only updates all records that satisfy stDocCriteria regardless if print is -1(true) or 0(false).

If anyone can help rewrite this code to work properly.

 

[DavidAtWork]

why not include the 'where print =-1' in your stDocCriteria

David

 

[jsdba]

why not include the 'where print =-1' in your stDocCriteria

Because stDocCriteria is a public function that is used for a number of reports i have. print=-1 would not work as apart of stDocCriteria.

I'm curious as to how access reads Update queries with multiple where clauses. Meaning, which clause is read first, if they are read together etc. I would understand if my sql had OR instand of AND. But doesn't AND mean all creteria must be met?

 

[plog]

isn't quite working properly.

What exactly does that mean? Demonstrate with data.

Also, are you're data types right? In your query

printed is a number
invoice_date is a date
print is a number
project_num is text
client_id is a number

Is that the way they are set up in tblTimedTasks?

 

[jsdba]

What exactly does that mean? Demonstrate with data.

I want to only updates records that meet all three criteria. sql updates all records that satisfy only stDocCriteria (project_num and client_id) regardless if print is -1(true) or 0(false).

e.g.

updateSQL = "update tblTimedTasks set printed =-1, invoice_date = Now() where print =-1 and ([project_num]= '140012' And [client_id] = 87)"

The code above should only update records where print = -1 AND project_num = '140012' AND client_id = 87.

Instead all records that matches project_num = '140012' AND client_id = 87 are updated regardless of print.

Data types
printed - boolean (yes/no)
print - boolean (yes/no)
invoice_date - date
project_num - text
client_id - number

 

[DavidAtWork]

I suspect it's to do with your braketing within your WHERE clause
where print = -1 and " & stDocCriteria

try:
where((( print) = -1) and " & stDocCriteria & ")"

David

 

[plog]

Can you post your database? Or at least a database with tblTimedTasks in it?

 

[jsdba]

Will try this and let you know how it works.

 

[jsdba]

Can you post your database? Or at least a database with tblTimedTasks in it?

I dont want to post db unless its a last resort for help, too many objects involved and the like. The brakets didnt help, but alas i know what the problem is. Take a look at the code when i'm updating multiple (project_num AND client_id)

"update tblTimedTasks set printed =-1, invoice_date = Now()
where (((print) =-1) and ([project_num]= '140001' And [client_id] = 192)
Or<< print =-1 should be inserted here>>([project_num]= '140001' And [client_id] = 167) 
Or<< print =-1 should be inserted here>>([project_num]= '140002' And [client_id] = 70)

Notice that "print" criteria is missing from the where clause after OR.

I build stDocCriteria from a listbox when i build my report, and use it as my criteria to update records when i close the report.

 

[plog]

If the print=-1 criteria is applicable to every project_num/client_id permutation, you only need it once:

WHERE (print=-1) AND ((permutation1) OR (permutation2) OR (permutation3) OR...)

 

[jsdba]

Here is my GetCriteria function:

Private Function GetCriteria() As String
On Error GoTo GetCriteria_Err
Dim stDocCriteria As String
Dim VarItem As Variant

     For VarItem = 0 To Me.List2.listcount - 1
     
      stDocCriteria = stDocCriteria & "([project_num]= '" & Me.List2.Column(1, VarItem) & "' And [client_id] = " & Me.List2.Column(0, VarItem) & ") Or "
    
    Next VarItem
        If stDocCriteria <> "" Then
            stDocCriteria = Left(stDocCriteria, Len(stDocCriteria) - 4)
        Else
            stDocCriteria = "True"
        End If       
        
         GetCriteria = stDocCriteria
 
basUpdate.updateSQL = "update tblTimedTasks set printed =-1, invoice_date = Now() where ([print] =-1) and " & stDocCriteria & ""
        
        
    
GetCriteria_Exit:
    Exit Function
GetCriteria_Err:
    MsgBox Error$
    Resume GetCriteria_Exit
End Function

I think i just need the right syntax for my updateSQL statement. No combination of brackets and parenthesis has worked thus far :banghead: