By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,081 Members | 1,885 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,081 IT Pros & Developers. It's quick & easy.

INSERT INTO won't work on boolean (yes/no) field

P: n/a
Good afternoon,

I am having troubles with an insert into statement in Access 2002 that
I am performing through VBA. I am inserting a single record into a
table with one yes/no field. For some reason, I cannot seem to set the
boolean to yes. Here are the various strings I have tried:

"INSERT INTO tblWorkOrderPartRequirements (WorkOrderID, PartNumberID,
fldQuantity, fldNeedWorkOrder) " _
& " VALUES (" & lngWorkOrderID & ", " & .Fields("PartNumberID") & ",
" & lngRequiredQuantity & ", " & strChild & ")"
in the above snippit strChild is either "yes" or "no" depending on
decisions from other code. An example of the actual query would be:

INSERT INTO tblWorkOrderPartRequirements (WorkOrderID, PartNumberID,
fldQuantity, fldNeedWorkOrder) VALUES
(200706001,686,40,yes)

All the other fields are inserted okay, but no matter what I try (I
even had a boolean type before I used the strChild variable) I cannot
seem to get the fldNeedWorkOrder to become true.

After completing this insert statement I put an update SQL statement
after to try to get it to change, but alas that did not work either.

Is there a field ppty setting that may have caused this?

Thanks for the help.

Tim

Jun 5 '07 #1
Share this Question
Share on Google+
3 Replies


P: n/a
Hi Tim,

I put together a quicky sample using the DoCmd object in VBA that you
can run in a standard code module

Sub InsertInto1()
DoCmd.SetWarnings False
DoCmd.RunSQL "Insert Into tblz(fld1,fld2,fld3) Select 'aaa', 'bbb',
true"
DoCmd.RunSQL "Insert Into tblz(fld1,fld2,fld3) Select 'ddd', 'eee',
false"
DoCmd.SetWarnings True
End Sub

I created a simple table with 3 fields - tblz with fld1, fld2, fld3.
fld1 and fld2 and basic text fields and fld3 is a Yes/No field. This
worked fine. So create a simple table as above and try this code out.
Then you can implement it into your project.

Rich

*** Sent via Developersdex http://www.developersdex.com ***
Jun 5 '07 #2

P: n/a
On Jun 5, 2:36 pm, Rich P <rpng...@aol.comwrote:
Hi Tim,

I put together a quicky sample using the DoCmd object in VBA that you
can run in a standard code module

Sub InsertInto1()
DoCmd.SetWarnings False
DoCmd.RunSQL "Insert Into tblz(fld1,fld2,fld3) Select 'aaa', 'bbb',
true"
DoCmd.RunSQL "Insert Into tblz(fld1,fld2,fld3) Select 'ddd', 'eee',
false"
DoCmd.SetWarnings True
End Sub

I created a simple table with 3 fields - tblz with fld1, fld2, fld3.
fld1 and fld2 and basic text fields and fld3 is a Yes/No field. This
worked fine. So create a simple table as above and try this code out.
Then you can implement it into your project.

Rich

*** Sent via Developersdexhttp://www.developersdex.com***
As Rich outed in his example, boolean fields hold True, False, -1 or 0
as valid values. Yes/No are just human interpretation that access uses
in its interfaces.

Some quick exercises to get what I'm meaning you can try in the
immediate window:

? true, false
? yes, no
? iif(true, "yes", "no")

Jun 5 '07 #3

P: n/a
On Jun 5, 1:36 pm, Rich P <rpng...@aol.comwrote:
Hi Tim,

I put together a quicky sample using the DoCmd object in VBA that you
can run in a standard code module

Sub InsertInto1()
DoCmd.SetWarnings False
DoCmd.RunSQL "Insert Into tblz(fld1,fld2,fld3) Select 'aaa', 'bbb',
true"
DoCmd.RunSQL "Insert Into tblz(fld1,fld2,fld3) Select 'ddd', 'eee',
false"
DoCmd.SetWarnings True
End Sub

I created a simple table with 3 fields - tblz with fld1, fld2, fld3.
fld1 and fld2 and basic text fields and fld3 is a Yes/No field. This
worked fine. So create a simple table as above and try this code out.
Then you can implement it into your project.

Rich

*** Sent via Developersdexhttp://www.developersdex.com***
Hey Rich and Spier,

Thanks for the tips. It turns out it was just an idiotic programming
flow problem on my end :). I fixed it all up though and everything
seems to work fine now.

Tim

Jun 7 '07 #4

This discussion thread is closed

Replies have been disabled for this discussion.