Quicky - UPDATE with an IIF?

[ClimoC]

Probably not exactly what you thought when seeing the thread title...

I already evaluate a whole bunch of stuff from collections etc. Then I get to do the update query.

                            strsql = "UPDATE [work] SET status = 'not required', updated = " & clng(int(now())) & ", archive = -1 WHERE ID = " & SAT.Item(iKey) & " AND archive = 0"
                            cnImportConn.Execute (strsql)

Thing is, that I have to set 'Status' to be either 'not required' or 'complete' depending on whether it was assigned in the first place or not. All the googling I can muster has only pointed me to slipping IIF statements in to retrieve column names or update values. I need it to change the value of the status, depending on the value found in a field (not currently in the 'WHERE' parameter)

...Um... How?

The extra field is 'assigned'.

So in written english: (sorta)

If the record at [ID] = (collection item), [assigned] = "awaiting assignment", [archive] value is 0, THEN: set [status] to "not required", updated to today (as an int, eg 41524), and [archive] to -1

OTHERWISE...

If the record at [ID] = (collection item), [assigned] = "assigned", [archive] value is 0, THEN: set [status] to "complete", updated to today (as an in, eg 41524), and [archive] to -1


Basically, I don't want to have to write 2 SQL statements and execute them both - because this runs in a loop across 2-3000 records once a day.

Is it a PROCEDURE I'm after? If so, do I have to code that into the Accdb itself as a public function or something? Never done Procedures....

Thanks
C

 

[Norie]

What if archive <>0 or assigned is not equal to awaiting assignment or assigned?

 

[ClimoC]

What if archive <>0 or assigned is not equal to awaiting assignment or assigned?

Erm... well (heeeere we go... )

This is part of a synchronisation script. By the time I get to this line, I've already collected the ones that need to be removed. The assignment does not make up one of the parameters for deletion/archive. What it does do though, is determine whether it is 'not required' or 'completed'.

here's an example section:

For Each iKey In SAT.Keys
        Select Case CLng(isthere.Item(SAT.Item(iKey)))
            Case fdate To ldate
                        If Not (InBSS.Exists(CStr(iKey & "SAT"))) And Not (ARCHSAT.Exists(iKey)) Then
                            UserForm1.TextBox1.Value = UserForm1.TextBox1.Value & "...Archiving iD:" & SAT.Item(iKey) & " at TitleID:" & iKey & vbCrLf
                            strsql = "UPDATE [work] SET status = 'not required', archive = -1 WHERE (ID = " & SAT.Item(iKey) & " AND archive = 0 AND assigned = 'not assigned')"
                            cnImportConn.Execute (strsql)
                            ys = ys + 1
                        End If
    End Select
Next

To try and answer your question Norie, if archive <> 0, then it doesn't need to be archived (as it will already be archived). The assignment field as I said is meaningless except for how it appears in the archive - e.g., was it removed because someone had started the work order ('assigned') and it's no longer in the job list (therefore, because it was assigned, it is now 'complete'), OR, was it 'not assigned', therefore, it can be removed and archived from the job list because it is 'not required'

I hope this makes sense...

C

 

[ClimoC]

What if archive <>0 or assigned is not equal to awaiting assignment or assigned?

Erm... well (heeeere we go... )

This is part of a synchronisation script. By the time I get to this line, I've already collected the ones that need to be removed. The assignment does not make up one of the parameters for deletion/archive. What it does do though, is determine whether it is 'not required' or 'completed'.

here's an example section:

For Each iKey In SAT.Keys
        Select Case CLng(isthere.Item(SAT.Item(iKey)))
            Case fdate To ldate
                        If Not (InBSS.Exists(CStr(iKey & "SAT"))) And Not (ARCHSAT.Exists(iKey)) Then
                            UserForm1.TextBox1.Value = UserForm1.TextBox1.Value & "...Archiving iD:" & SAT.Item(iKey) & " at TitleID:" & iKey & vbCrLf
                            strsql = "UPDATE [work] SET status = 'not required', archive = -1 WHERE (ID = " & SAT.Item(iKey) & " AND archive = 0 AND assigned = 'not assigned')"
                            cnImportConn.Execute (strsql)
                            ys = ys + 1
                        End If
    End Select
Next

To try and answer your question Norie, if archive <> 0, then it doesn't need to be archived (as it will already be archived). The assignment field as I said is meaningless except for how it appears in the archive - e.g., was it removed because someone had started the work order ('assigned') and it's no longer in the job list (therefore, because it was assigned, it is now 'complete'), OR, was it 'not assigned', therefore, it can be removed and archived from the job list because it is 'not required'

I hope this makes sense...

C

 

[ClimoC]

Can I not just do this?

strsql = "UPDATE [work] SET status = IIf([assigned] = 'not assigned', 'not required', 'complete'), archive = -1, updated = clng(int(now())) WHERE (ID = " & SAT.Item(iKey) & " AND archive = 0)"