SQL Update

[Guest]

Hi there,

I will try and explain this as clearly as possible. If you have any
questions, just ask away.

I have a table, that looks like this:

ID CAT COMMENT CLOSE DATE
2006-001 1 testing.... 4/25/2006
2006-001 1 CLOSED CLOSED 4/27/2006
2006-001 1 ACCEPT ACCEPT 4/26/2006
2006-001 1 test..1..2 4/23/2006
2006-001 1 more to do. 4/25/2006

What I need to do is take all the blank fields in CLOSE and replace them
with "CLOSED"
If it says ACCEPT, keep it like that. Below is the code i am building on,
but it replaces ALL of the fields.

Thanks.
-State


Dim ssql As String
Dim stClosed As String
Dim stDir As String
Dim id As String

stDir = "1"
id = "1"

stClosed = "CLOSED"

ssql = "UPDATE tblActions Set LINE='" & stClosed & "' WHERE
tblActions.DIR='" & stDir & "' AND tblActions.CAT='" & id & "'"

CurrentDb.Execute ssql

 

[Guest]

Hi.

Try:

ssql = "UPDATE tblActions " & _
"SET Line = IIF(ISNULL(Line), '" & stClosed & "', Line) " & _
"WHERE (Dir = '" & stDir & "') AND (Cat = '" & id & "'); "

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact info.

 

[John Griffiths]

ssql = "UPDATE tblActions " & _
"Set LINE='" & stClosed & "' ," & _
"[DATE] = " & Format(Now(), "#m/dd/yyyy#") & _
"WHERE DIR='" & stDir & "'" & _
" AND CAT='" & id & "'" & _
" AND Close IS NULL "

Date is a bad choice of SQL field name due to conflict with Access function
Date().
LINE is not described in your commentry.
DIR is not described in your commentry.

Regards John

 

['69 Camaro]

Hi, John.

Thanks for that, but I doubt State Troopers wants to change this date field
in these records to Now().

You make a good point about Date being a Reserved word, as is Close. These
field names should be changed to something else in order to avoid bugs. For
State Troopers, here are lists of Reserved words to avoid on the following
Web pages:

http://support.microsoft.com/default.aspx?id=321266

http://support.microsoft.com/default.aspx?scid=286335

And State Troopers' query was so different from the structure described that
I went with the query and altered it in my suggested code, but forgot to
mention that. Thanks for pointing out the incongruency, too.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.

ssql = "UPDATE tblActions " & _
"Set LINE='" & stClosed & "' ," & _
"[DATE] = " & Format(Now(), "#m/dd/yyyy#") & _
"WHERE DIR='" & stDir & "'" & _
" AND CAT='" & id & "'" & _
" AND Close IS NULL "

Date is a bad choice of SQL field name due to conflict with Access
function
Date().
LINE is not described in your commentry.
DIR is not described in your commentry.

Regards John

message

Hi there,

I will try and explain this as clearly as possible. If you have any
questions, just ask away.

I have a table, that looks like this:

What I need to do is take all the blank fields in CLOSE and replace them
with "CLOSED"
If it says ACCEPT, keep it like that. Below is the code i am building on,
but it replaces ALL of the fields.

Thanks.
-State


Dim ssql As String
Dim stClosed As String
Dim stDir As String
Dim id As String

stDir = "1"
id = "1"

stClosed = "CLOSED"

ssql = "UPDATE tblActions Set LINE='" & stClosed & "' WHERE
tblActions.DIR='" & stDir & "' AND tblActions.CAT='" & id & "'"

CurrentDb.Execute ssql