SQL - Update field to null gives error 3085, undefined function

[scottian]

When i close a form i have an sql command run which clears certain fields in a record setting them to null. But when i try it it gives the above error, "Undefined function 'nullWhere' in expression".

is there a way to overcome this?

I appreciate any help and advice.

DoCmd.RunSQL "UPDATE TBL_NewRequests SET txtAssigned = Null, txtActions = Null, txtName = null" & _
"WHERE (((TBL_NewRequests.MyLink)=[Forms]![FRM_UserWorkForm]![MyLink]));"

"Children are smarter than any of us. Know how I know that? I don't know one child with a full time job and children."...Bill Hicks

 

[jrbarnett]

Put a space between the word "NULL" at the end of the first line and the word "WHERE" at the start of the second line.

John

 

[Melagan]

CurrentDb.Execute "UPDATE TBL_NewRequests SET txtAssigned = Null, txtActions = Null, txtName = null " & _
"WHERE (((TBL_NewRequests.MyLink)=[Forms]![FRM_UserWorkForm]![MyLink]));"

..and as suggested by jrbarnett, a space is needed between the "= null_" and "WHERE " (my suggested space is highlighted in blue)

CurrentDb.Execute will allow you to execute SQL statements whether or not SetWarnings = True. It basically saves you from having to do this:

With Docmd
.SetWarnings = False
.RunSQL "UPDATE etc."
.SetWarnings = True
End With

~Melagan
______
"It's never too late to become what you might have been.

 

[dhookom]

I usually store the SQL in a variable prior to executing/running it. I also prefer to remove the form/control and use the value.

Dim strSQL As String
strSQL = "UPDATE TBL_NewRequests SET txtAssigned = Null, " & _
   "txtActions = Null, txtName = null " & _
   "WHERE MyLink=""" & [Forms]![FRM_UserWorkForm]![MyLink] & """" 
'either
    Currentdb.Execute strSQL, dbFailOnError
'or
' DoCmd.RunSQL strSQL

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[scottian]

thank you. some new stuff here that i was unaware of. I think ill start using the 'CurrentDb.Execute' as it looks simpler without the need for 'setwarnings'.. cheers

"Children are smarter than any of us. Know how I know that? I don't know one child with a full time job and children."...Bill Hicks