SLQ in VBA

[Afrosheen via AccessMonster.com]

I have a situation where I need to update my table using slq. The information
to be updated has two conditions.

Here is what I have so far.

Dim awk As ADODB.Connection, sql5 As String
150 Set awk = CurrentProject.AccessConnection
170 sql5 = "update tbl_roster set location = '' " & "where shift = 'a-
days'"
190 awk.Execute sql5, , adCmdText + adExecuteNoRecords
220 Set awk = Nothing

What I want to do is to update the tbl_roster [location] = " " & where
[shift] = 'a-days' and [position] = 'custody' The brackets around the
words are the actual fields.

The first part as written is working. It's the added [position] after the 'a-
days' that I don't know how to program to equal two conditions.

Another question is how can I make this a shorter line: stwhere = "([position]
= 'custody'or[position]='sgt'or[position]='lieutenant'or[position]='captain')
and ([shift]='b-days' or [shift] = 'day shift')"


Thanks for your help and reading this post.

I hope this is enough information.

 

[Ken Snell \(MVP\)]

sql5 = "update tbl_roster set location = '' " & " where shift = 'a-days' and
position = 'custody'"

To do multiple positions, use IN:

sql5 = "update tbl_roster set location = '' " & " where shift IN ('a-days',
'b-days', 'day shift') and position IN ('custody', 'sgt', 'lieutenant',
'captain')"

 

[Afrosheen via AccessMonster.com]

Thanks for the quick reply Ken. I may nor have made my self clear. I have
that problem every once in a while.

The problem I need fixed first is: sql5 = "update tbl_roster set location =
'' " & "where shift = 'a-days'"

I want to add a second condition after the {a-days} I tried continuing the
statement with: & "where position = 'custody'". The complete line would read:

sql5 = "update tbl_roster set location = '' " & "where shift = 'a-days'" &
"where position = 'custody'".

When I did it came up with an error. "Syntax error in query expression".

Thanks again

sql5 = "update tbl_roster set location = '' " & " where shift = 'a-days' and
position = 'custody'"

To do multiple positions, use IN:

sql5 = "update tbl_roster set location = '' " & " where shift IN ('a-days',
'b-days', 'day shift') and position IN ('custody', 'sgt', 'lieutenant',
'captain')"

I have a situation where I need to update my table using slq. The
information

[quoted text clipped - 27 lines]

I hope this is enough information.

 

[John W. Vinson]

that problem every once in a while.

The problem I need fixed first is: sql5 = "update tbl_roster set location =
'' " & "where shift = 'a-days'"

I want to add a second condition after the {a-days} I tried continuing the
statement with: & "where position = 'custody'". The complete line would read:

sql5 = "update tbl_roster set location = '' " & "where shift = 'a-days'" &
"where position = 'custody'".

When I did it came up with an error. "Syntax error in query expression".

Thanks again

The WHERE keyword should appear only once. Multiple criteria need to be linked
using AND and OR to create a logical expression which is either true or false.
I think you want something like

sql5 = "update tbl_roster set location = Null where shift = 'a-days'" _
& " AND position = 'custody'".

Your " " in the SET is incorrect, since each quotemark will either end or
start a new text string constant.

 

[Afrosheen via AccessMonster.com]

Thanks John,
I'll try it out.

that problem every once in a while.

[quoted text clipped - 10 lines]

Thanks again

The WHERE keyword should appear only once. Multiple criteria need to be linked
using AND and OR to create a logical expression which is either true or false.
I think you want something like

sql5 = "update tbl_roster set location = Null where shift = 'a-days'" _
& " AND position = 'custody'".

Your " " in the SET is incorrect, since each quotemark will either end or
start a new text string constant.

 

[Afrosheen via AccessMonster.com]

Thanks again John. It works the way I wanted it to.

Thanks John,
I'll try it out.
[quoted text clipped - 11 lines]

Your " " in the SET is incorrect, since each quotemark will either end or
start a new text string constant.

 

[Ken Snell \(MVP\)]

Your " " in the SET is incorrect, since each quotemark will either end or
start a new text string constant.

Thanks for catching that, John, I completely missed it when typing my reply.