Type conversion in an append query

[Guest]

Original field (isSenior) is text255 but is used as a Y/N (the value is Y or
blank). In New table field type is Y/N. When I run an append query to
transfer the data, I get a conversion error and no data is transferred. How
can I transfer the data?

I tried changing the type on the original table, but I get a message that
the data will be deleted. suggestions?

tia.

HB

 

[Rick B]

Try changing your query. Add a new column and make it...

IsSeniorYN: Iif([isSenior]="Y", -1,0)


And append from that. It will show -1 (True) if the record contains a "yes"
and it will show 0 (false) if the record does not contain a "yes".



Rick B

 

[Guest]

Actually, it just occurred to me I don't want an append query, but an update
query. However, I don't know how to write an update query based on the
original table's values.

I imagine it's something like update set value to where (select from
original table), though in design view I;m not sure.

 

[Guest]

Thanks. I was already along those lines. I got it. Here it is.

UPDATE Mem1104_2, MEMBER SET MEMBER.isSenior2 = 1
WHERE (((Mem1104_2.SENIOR_2)="Y") AND
((Mem1104_2.PAGE_ONE_M)=[Member].[MemberNumber]));

Thanks.

HB

Try changing your query. Add a new column and make it...

IsSeniorYN: Iif([isSenior]="Y", -1,0)


And append from that. It will show -1 (True) if the record contains a "yes"
and it will show 0 (false) if the record does not contain a "yes".



Rick B

Original field (isSenior) is text255 but is used as a Y/N (the value is Y or
blank). In New table field type is Y/N. When I run an append query to
transfer the data, I get a conversion error and no data is transferred. How
can I transfer the data?

I tried changing the type on the original table, but I get a message that
the data will be deleted. suggestions?

tia.

HB