Update Query

[SageOne]

How can I change this code to make the "Occupied" field null? This is just a
small part of a larger peice of code but this is whats causing my issue.

DoCmd.RunSQL "UPDATE [On Hold Records Table 2] SET [On Hold Records Table
2].Occupied is null" & _
"WHERE ((([On Hold Records Table 2].Identifier)='" & Identifier & "'));"

 

[John W. Vinson]

How can I change this code to make the "Occupied" field null? This is just a
small part of a larger peice of code but this is whats causing my issue.

It's confusing and perhaps inconsistant, but you must use the IS NULL syntax
in criteria (= NULL will NOT work); but you must use = NULL in an Update
clause (IS NULL will NOT work). Try

DoCmd.RunSQL "UPDATE [On Hold Records Table 2] SET [On Hold Records Table
2].Occupied = null" & _
"WHERE ((([On Hold Records Table 2].Identifier)='" & Identifier & "'));"

This assumes that Identifier is a text type field; it doesn't matter what
datatype Occupied is, if it's nullable (i.e. not Required and not a Yes/No
field).

 

[SageOne]

How can I change this code to make the "Occupied" field null? This is just a
small part of a larger peice of code but this is whats causing my issue.

It's confusing and perhaps inconsistant, but you must use the IS NULL syntax
in criteria (= NULL will NOT work); but you must use = NULL in an Update
clause (IS NULL will NOT work). Try

DoCmd.RunSQL "UPDATE [On Hold Records Table 2] SET [On Hold Records Table
2].Occupied = null" & _
"WHERE ((([On Hold Records Table 2].Identifier)='" & Identifier & "'));"

This assumes that Identifier is a text type field; it doesn't matter what
datatype Occupied is, if it's nullable (i.e. not Required and not a Yes/No
field).
--

John W. Vinson [MVP]
.
It gave a a 'NullWhere' error.

Here is my complete code.

--------------------------------------------------------------------

Private Sub Command108_Click()

On Error GoTo Err_Command108_Click

Dim Identifier As String

If Not IsNull(Me.Status) Then
MsgBox ("You must leave the status blank for this command!")
Else


Identifier = Me![Identifier]
DoCmd.SetWarnings False

DoCmd.Echo False, ""
DoCmd.Close acForm, "FrmUnworkedHolds"
DoCmd.OpenForm "FrmMenu", acNormal, "", "", , acNormal

DoCmd.RunSQL "UPDATE [On Hold Records Table 2] SET [On Hold Records
Table 2].Occupied = null" & _
"WHERE ((([On Hold Records Table 2].Identifier)='" & Identifier & "'));"





DoCmd.Echo True, ""

End If



Exit_Command108_Click:
Exit Sub

Err_Command108_Click:
MsgBox Err.Description
Resume Exit_Command108_Click

 

[John W. Vinson]

DoCmd.RunSQL "UPDATE [On Hold Records Table 2] SET [On Hold Records
Table 2].Occupied = null" & _
"WHERE ((([On Hold Records Table 2].Identifier)='" & Identifier & "'));"

Blanks are important! This will try to set Occupied to

nullWHERE ((([On Hold...

I'd suggest building up a SQL string from components - that makes it easier to
debug:

Dim strSQL As String
strSQL = "UPDATE [On Hold Records Table 2].Occupied = NULL " _
& "WHERE ((([On Hold Records Table 2].Identifier)='" _
& Identifier & "'));"
CurrentDb.Execute strSQL, dbFailOnError

Note the blank between the word NULL and the quote... it's relevant!

The Execute method lets you trap errors and doesn't prompt for confirmation.