DoCmd.RunSQL sql

[JGR]

To all,

I have tried all I can think of to get this code to work.
No Luck. I have numbered the changes and list the erros to
the side.

Any help would be greatly appreciated.

Thanks

Dim sql As String

sql = "Update tblBBPNo" & _
*ysnAvaliable is a Yes/No Field
(1)" Set tblBBPNo.ysnAvaliable = '-1'" & _ (Err 3464
Data type Mismatch in criteria Expression)
(2)" Set tblBBPNo.ysnAvaliable = -1" & _ (Err 3464 Data
type Mismatch in criteria Expression)
(3) " Set tblBBPNo.ysnAvaliable =" - 1 & _ (Err 13 Type
Mismatch)
(4) " Set tblBBPNo.ysnAvaliable =" & -1 & _ (Err 3464 Data
type Mismatch in criteria Expression)

*ysnAvaliable is a Yes/No Field and is formatted
y\es/n\o;"Yes";"No"
(5) " Set tblBBPNo.ysnAvaliable = Yes" & _ (Err 3464 Data
type Mismatch in criteria Expression)
(6) " Set tblBBPNo.ysnAvaliable = 'Yes'" & _ (Err 3464
Data type Mismatch in criteria Expression)
(7) " Set tblBBPNo.ysnAvaliable =" & "Yes" & _ (Err 3464
Data type Mismatch in criteria Expression)
(8) " Set tblBBPNo.ysnAvaliable = True" & _ (Err 3464 Data
type Mismatch in criteria Expression)
(9) " Set tblBBPNo.ysnAvaliable = 'True'" & _ (Err 3464
Data type Mismatch in criteria Expression)
(10) " Set tblBBPNo.ysnAvaliable =" & True & _(Err 3464
Data type Mismatch in criteria Expression)

" Where intBBP_No_Id = '" & Me.intBBP_No_Id.OldValue & "'"
DoCmd.RunSQL sql

 

[Allen Browne]

Should work with:
"UPDATE tblBBPNo SET ysnAvailable = True;"

BTW, if ysnAvailable is an Access Yes/No field, it will not be able to hold
the value "Not Available", since the JET Yes/No field cannot be null.

 

[JGR]

Thanks for the help!
I changed the code to

sql = "Update tblBBPNo" & " Set tblBBPNo.ysnAvaliable =
True;" & _
" Where intBBP_No_Id = '" & Me.intBBP_No_Id.OldValue & "'"
DoCmd.RunSQL sql

This changed the Error to:
3142 Characters found after end of SQL statment.

Is this a typo or because it is part of an IF statment and
there is another "sql=" below the DoCmd.RunSQL sql?

 

[Douglas J. Steele]

Get rid of the semicolon. There's actually never a need for the semicolon in
SQL statements, even if Access persists in installing it. (FWIW, Allen
obviously didn't realize that you have a Where clause, or I'm sure he
wouldn't have put the semicolon in where he did)