Update Query using Boolean field

[MMach via AccessMonster.com]

This is more of a syntax question. An excerpt from my query is:
Dim MyQ2 As String
Dim vTest As Long

[vTest]=[Forms]![Main]![Test]

MyQ2 = "UPDATE Materials SET Materials.[Test] = '" & [vTes] & "' WHERE (
(Materials.[Order Number])= 1000)))"
DoCmd.RunSQL MyQ2

I know that what is not working is the syntax for '" & [vTes] & "'. I do not
know the syntax of how to pass a Boolean field to the query. The value for
[vTest] is obviously being brought in from the form [Main] and I want to
include this value in the query.

Any help is GREATLY appreciated!

 

[Jaazaniah]

This is more of a syntax question.  An excerpt from my query is:
Dim MyQ2 As String
Dim vTest As Long

[vTest]=[Forms]![Main]![Test]

MyQ2 = "UPDATE Materials SET Materials.[Test] = '" & [vTes] & "'  WHERE (
(Materials.[Order Number])= 1000)))"
DoCmd.RunSQL MyQ2

I know that what is not working is the syntax for '" & [vTes] & "'.  I do not
know the syntax of how to pass a Boolean field to the query.  The valuefor
[vTest] is obviously being brought in from the form [Main] and I want to
include this value in the query.

Any help is GREATLY appreciated!

I think, if this code is copied directly, that this is a simple case
of Variable names. You don't need to bracket procedure variables, and
"vTest" in your SQL line should probably be changed to "vTest". VB
Does support the Boolean type as well, but for future reference
True=-1 and False=0. Try this.

Dim MyQ2 As String
Dim vTest As Boolean

vTest=[Forms]![Main]![Test]


MyQ2 = "UPDATE Materials SET Materials.[Test] = '" & vTest & "' WHERE
(
(Materials.[Order Number])= 1000)))"
DoCmd.RunSQL MyQ2

 

[MMach via AccessMonster.com]

This is more of a syntax question.  An excerpt from my query is:
Dim MyQ2 As String

[quoted text clipped - 17 lines]

Message posted viahttp://www.accessmonster.com

I think, if this code is copied directly, that this is a simple case
of Variable names. You don't need to bracket procedure variables, and
"vTest" in your SQL line should probably be changed to "vTest". VB
Does support the Boolean type as well, but for future reference
True=-1 and False=0. Try this.

Dim MyQ2 As String
Dim vTest As Boolean

vTest=[Forms]![Main]![Test]

MyQ2 = "UPDATE Materials SET Materials.[Test] = '" & vTest & "' WHERE
(
(Materials.[Order Number])= 1000)))"
DoCmd.RunSQL MyQ2

Thanks for the reply. I tried it but it did not work. In other words, '" &
vTest & "' is not transferring the Boolean equivalent of -1 or 0 to Materials.
[Test]. There must be a way. Any other help is appreciated.

 

[Piet Linden]

Dim MyQ2 As String
Dim vTest As Boolean

vTest=[Forms]![Main]![Test]

MyQ2 = "UPDATE Materials SET Materials.[Test] = '" & vTest & "' WHERE
(
(Materials.[Order Number])= 1000)))"

One simple question...
Why are you delimiting a boolean with single quotes? Booleans are
expressed as *numbers* in Access, so there's no doubt what you're
trying won't work.

Shouldn't you use
MyQ2 = "UPDATE Materials SET Materials.[Test] = " & vTest & " WHERE
Materials.[Order Number] = 1000
?

 

[MMach via AccessMonster.com]

Dim MyQ2 As String
Dim vTest As Boolean

vTest=[Forms]![Main]![Test]

MyQ2 = "UPDATE Materials SET Materials.[Test] = '" & vTest & "' WHERE
(
(Materials.[Order Number])= 1000)))"

One simple question...
Why are you delimiting a boolean with single quotes? Booleans are
expressed as *numbers* in Access, so there's no doubt what you're
trying won't work.

Shouldn't you use
MyQ2 = "UPDATE Materials SET Materials.[Test] = " & vTest & " WHERE
Materials.[Order Number] = 1000
?

Good job Piet!

This does work!!!

Thanks.