A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Access » General Discussion
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Update Query



 
 
Thread Tools Display Modes
  #1  
Old May 27th, 2010, 10:41 PM posted to microsoft.public.access
SageOne
external usenet poster
 
Posts: 38
Default 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  
Old May 28th, 2010, 01:58 AM posted to microsoft.public.access
John W. Vinson
external usenet poster
 
Posts: 18,261
Default 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  
Old May 28th, 2010, 04:32 AM posted to microsoft.public.access
John W. Vinson
external usenet poster
 
Posts: 18,261
Default 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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 08:34 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.