Need help with SQL statement in VBA

[fearmichaele]

I have a front end Access Form that is linked to a SQL Table. one the form when the user presses a button i want to ensure that the field named IncompleteDoc is not null but has a 1 in it. In SQL i would use the following query statement to accomplish this.

update [MMDB].[Intake].[TblIntakePatient] set [IncompleteDoc]=1 where IncompleteDoc is null

i want to do this in VBA because if that field is NULL then none of the users can update or delete any records. I need the value of 1 to be placed in that field to signify TRUE.

in my VBA project i have placed the following line

DoCmd.RunSQL "UPDATE [MMDB].[Intake].[TblIntakePatient] SET [IncompleteDoc]= 1 WHERE IncompleteDoc is Null"

I am getting
VBA Error
3144
Syntax error in UPDATE statement.

i have tried several iterations of this code.

DoCmd.RunSQL ("UPDATE [MMDB].[Intake].[TblIntakePatient] SET [IncompleteDoc]= 1 WHERE IncompleteDoc is Null")

DoCmd.RunSQL "UPDATE [MMDB].[Intake].[TblIntakePatient] SET [IncompleteDoc]= 1 WHERE IncompleteDoc = Null"

DoCmd.RunSQL ("UPDATE [MMDB].[Intake].[TblIntakePatient] SET [IncompleteDoc]= 1 WHERE IncompleteDoc =Null")

even in my WHERE statement i have [] around the field name and still get the same error.

I am using Access 2016 as the front end and i am using SQL Server 2016 to create my tables. i have looked at several syntax example on the web and even followed Microsoft examples

SQL = "UPDATE Employees " &; _
"SET Employees.Title = 'Regional Sales Manager' " &; _
"WHERE Employees.Title = 'Sales Manager'"

DoCmd.RunSQL SQL

when i try to put in the &;_ VBA doesnt like that and produces a compile error

I have tried trial and error and now I am asking you, the experts, what the heck am i doing wrong?

Thank you in advance for your help.

 

[isladogs]

If IncompleteDoc is a boolean (Yes/No) field then use True or -1 instead of 1
If its a text field use '1'

You do NOT need the () brackets

The placing of the semicolons in the later code is incorrect.
There should be just ONE semicolon at the end:

SQL = "UPDATE Employees " & _ 
"SET Employees.Title = 'Regional Sales Manager' " & _ 
"WHERE Employees.Title = 'Sales Manager';"

Personally, I find it easier to write the space at the starty of each line so its easier to see

SQL = "UPDATE Employees" & _ 
" SET Employees.Title = 'Regional Sales Manager'" & _ 
" WHERE Employees.Title = 'Sales Manager';"

 

[fearmichaele]

thank you for the quick response.

so i adjusted my code to read

DoCmd.RunSQL "UPDATE [MMDB].[Intake].[TblIntakePatient] SET [IncompleteDoc]= -1 WHERE IncompleteDoc is Null"

I am still getting VBA Error 3144 Syntax error in UPDATE Statement.

 

[Mark_]

What is [IncompleteDoc] declared as?

Also, where is the trailing semicolon? Should it end with ";"?

 

[isladogs]

If it is a boolean field then its true or false only - it won't be null!

DoCmd.RunSQL "UPDATE TblIntakePatient SET IncompleteDoc= True WHERE IncompleteDoc = False;"

I've removed unnecessary [] - only needed if fields contain spaces or special characters
I've also removed MMDB. Intake. which I'm fairly sure should not be there. The correct syntax is :

[B][I]UPDATE TableName SET FieldName= SomeValue WHERE FieldName = SomeOtherValue[/I][/B]

I've added the TRAILING semicolon as that is the default method

@Mark_
... but I only found out recently it isn't actually REQUIRED

 

[fearmichaele]

[incompletedoc] is a BIT field in my [TBLintakepatient] table. i have figured out what I was doing wrong.

in my statement

DoCmd.RunSQL "UPDATE [MMDB].[Intake].[TblIntakePatient] SET [IncompleteDoc]= -1 WHERE IncompleteDoc is Null"

I was using the SQL table name. In access the linked table has a slightly different name. In access the table is called TblPatient which is linked to TblIntakePatient.

so i changed my syntax to the table in access and it now works.

DoCmd.RunSQL "UPDATE [TblPatient] SET [IncompleteDoc]= -1 WHERE IncompleteDoc is Null"

thank you all for your help.

 

[isladogs]

See my last post where I did much the same thing
Is your table name tblPatient or tblIntakePatient? You've used both

I still say it should be

DoCmd.RunSQL "UPDATE TblIntakePatient SET IncompleteDoc = True WHERE IncompleteDoc = False;"

or if you prefer
DoCmd.RunSQL "UPDATE TblIntakePatient SET IncompleteDoc= -1 WHERE IncompleteDoc = 0;"

Background info
Bit or boolean or yes/no fields have only 2 states in Access - True or False. They cannot be NULL

Confusingly in SQL Server, boolean fields have 3 states - True/False/Null
However if you use those as linked Access tables you MUST set the default value to True or False. If you fail to do so, you will get write conflict errors when you try to update the table in Access as it doesn't know how to resolve any null values

 

[fearmichaele]

Ridders, in Sql Server 2016 the table is named TblIntakePatient but in Access it is named TblPatient. the VBA statement wanted me to use TblPatient (Access name) instead of TblIntakePatient (SQL name). That is what i did to fix my error.

And yes after I did that, I did realize that I needed to set a default value on the table column because the NULL value was creating a Write conflict. So my ultimate fix was setting a default value of true (-1) when the record was created. Since I did that, I didn't need to add the line in my VBA which was doing the same thing, changing a NULL value to a True in that field.