Programming Question

[Afrosheen via AccessMonster.com]

I have this code listed below where I want to change information in a field,
but it has to match the criteria. I don't know if I'm doing it correct. All
of it needs to be changed. For example:

Dim cn As ADODB.Connection, sql As String
10 Set cn = CurrentProject.AccessConnection
20 sql = "update tbl_roster set status = 'off day' where shift = 'B-
Days'" & "or shift = 'Day Shift'" & "or shift = 'Afternoon Shift'" & "or
shift = 'B-Nights' And Not (tbl_roster.archive) = true"

'************ Executes the ADODB commands ******
30 cn.Execute sql, , adCmdText + adExecuteNoRecords
40 Set cn = Nothing


What I need is line 20 to say "set status = off day". I need
B-Days,Day Shift, Afternoon Shift, and B-Nights changed but not if the
archive field = true.

My question is that with the "or shift = " will it do the above or when it
gets to that value will it kick me out?

I'm sorry about this question, but I'm confused.

Thanks for reading my post.

 

[vanderghast]

It is probably a question of order of evaluation. Try:

20 sql = "update tbl_roster set status = 'off day' where ( shift =
'B-
Days' or shift = 'Day Shift' or shift = 'Afternoon Shift' or
shift = 'B-Nights' ) And Not (tbl_roster.archive) = true"


or, even better:


20 sql = "update tbl_roster set status = 'off day' where ( shift
IN( 'B-
Days' , 'Day Shift' , 'Afternoon Shift', 'B-Nights' )) And Not
(tbl_roster.archive) = true"




where the list of or have been replaced by a single IN list.


Vanderghast, Access MVP

 

[Dirk Goldgar]

I have this code listed below where I want to change information in a
field,
but it has to match the criteria. I don't know if I'm doing it correct.
All
of it needs to be changed. For example:

Dim cn As ADODB.Connection, sql As String
10 Set cn = CurrentProject.AccessConnection
20 sql = "update tbl_roster set status = 'off day' where shift =
'B-
Days'" & "or shift = 'Day Shift'" & "or shift = 'Afternoon Shift'" & "or
shift = 'B-Nights' And Not (tbl_roster.archive) = true"

'************ Executes the ADODB commands ******
30 cn.Execute sql, , adCmdText + adExecuteNoRecords
40 Set cn = Nothing


What I need is line 20 to say "set status = off day". I need
B-Days,Day Shift, Afternoon Shift, and B-Nights changed but not if the
archive field = true.

My question is that with the "or shift = " will it do the above or when it
gets to that value will it kick me out?

I'm sorry about this question, but I'm confused.

Although you ought to have a few more spaces in your SQL statement,
basically this is a question of operator precedence, isn't it? You want to
know what will be the order of evaluation of the Or operators and the And
operator. If you were to search long enough -- or do some testing -- I'm
sure you could find out the answer.

However, it's better to use parentheses if necessary to ensure the order of
evaluation you intend. That makes it clear both to the SQL engine and to
anyone who reads your code. You could do it like this:

sql = _
"UPDATE tbl_roster SET status = 'off day' WHERE " & _
"(shift = 'B-Days' Or shift = 'Day Shift' Or " & _
"shift = 'Afternoon Shift' or shift = 'B-Nights') " & _
"And tbl_roster.archive <> True"

Alternatively, you could use the In() operator to avoid using the "Or" logic
at all. This might be a bit cleaner:

sql = _
"UPDATE tbl_roster SET status = 'off day' WHERE " & _
"tbl_roster.archive <> True " & _
"AND shift In('B-Days', 'Day Shift', 'Afternoon Shift',
'B-Nights')"

 

[Afrosheen via AccessMonster.com]

Thanks to the both of you. I did try out your routine and it worked the way I
thought it should. It was old programming when I first started out so it got
me confused. I really appreciate the help and direction.

I have this code listed below where I want to change information in a
field,

[quoted text clipped - 21 lines]

I'm sorry about this question, but I'm confused.

Although you ought to have a few more spaces in your SQL statement,
basically this is a question of operator precedence, isn't it? You want to
know what will be the order of evaluation of the Or operators and the And
operator. If you were to search long enough -- or do some testing -- I'm
sure you could find out the answer.

However, it's better to use parentheses if necessary to ensure the order of
evaluation you intend. That makes it clear both to the SQL engine and to
anyone who reads your code. You could do it like this:

sql = _
"UPDATE tbl_roster SET status = 'off day' WHERE " & _
"(shift = 'B-Days' Or shift = 'Day Shift' Or " & _
"shift = 'Afternoon Shift' or shift = 'B-Nights') " & _
"And tbl_roster.archive <> True"

Alternatively, you could use the In() operator to avoid using the "Or" logic
at all. This might be a bit cleaner: