If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Update Query
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 & "'));" |
#2
|
|||
|
|||
Update Query
On Thu, 27 May 2010 14:41:39 -0700, SageOne
wrote: 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] |
#3
|
|||
|
|||
Update Query
On Thu, 27 May 2010 20:12:01 -0700, SageOne
wrote: 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. -- John W. Vinson [MVP] |
Thread Tools | |
Display Modes | |
|
|