Apend Query with Yes/No field ?

[DahMurf]

I tried to run an Apend Query in which one of the fields is a Yes/No datatype. The yes values are not carried from one table to the other. Any ideas how to make this work in a query? I can probably whip up some VBA code but an apend should be simple enough.

Tia for any help.

 

[cmmrfrds]

Please paste in the SQL for the query.

 

[scriverb]

An append query should certain pass the Yes/No or True/False values from one table to another. I would check to make sure that the field in the Append To box is in fact a Yes/No data type.
EXAMPLE:
INSERT INTO tblEmpStatus ( EmpID, Status, YesNoField )
SELECT tblEmp.EmpID, tblEmp.Status, Yes AS Expr1
FROM tblEmp;

Let me know if you need any further assistance.


Bob Scriver

 

[DahMurf]

Both the from & two tables have the data type set to yes/no.
In your example above all of the values get set to yes. I want to maintain the existing value when I do an apend so some would be yes, some no. Any other ideas?
Thanks!

 

[scriverb]

Your original post mentioned something about the "yes" values were not being passed in the append process. This is why I thought you were appending records and forcing a "Yes" value in the new record. This should work for you if your fields are both set to Yes/No data type:
EXAMPLE:
INSERT INTO tblEmpStatus ( EmpID, Status, YesNoField )
SELECT tblEmp.EmpID, tblEmp.Status, tblEmp.YesNoField
FROM tblEmp;

Let me know if this works.


Bob Scriver

 

[r937]

INSERT INTO tblEmpStatus ( EmpID, Status, YesNoField )
SELECT tblEmp.EmpID, tblEmp.Status, IIF(Yes,-1,0)
FROM tblEmp;

rudy

 

[scriverb]

r937, your IIF will certainly work but agreeably redundant and unncessary if the YesNoField is a Yes or a NO. Right?
This one sounds a little strange. A straight assignment from one table to the other should work.

Bob Scriver

 

[r937]

you're right, bob, it should

but DahMurf said it wasn't happening

 

[DahMurf]

hmmmmm ? I just created copies of my tables & created a new query & this time it worked. I was running this on a different computer when it didn't work so I'll try it again tomorrow to see if it works or not. I "should" be running the same version of Access on both. Only other thing I can think is I believe I changed an existing field to yes/no then updated an existing query to move that field, maybe it was somehow stuck with the old data type.... things that make you go hmmmmm.

Thanks so much for your quick responses. This is a kewl place!
Murf (yeah, for Murphy's Law - fits doesn't it?)