Union can go against nothing. What if you use UNION ALL?
Never met this ODBC error. Try to search MSDN Library for exact phrase "Error in assignment". It returned 10 articles for me.

>>Try to use UNION like:
>>
>>*----------------------------
>>SELECT Parent.Username, Parent.Phone, Child.KidFirstName, Child.KidLastName,
>>StatusLookup.Status, PriorityLookup.Priority
>>FROM Parent, Child, StatusLookup, PriorityLookup
>>WHERE Parent.ItemKey = Child.ItemKey AND
>>Parent.StatusKey = StatusLookup.StatusKey AND
>>Child.PriorityKey = PriorityLookup.PriorityKey AND
>>Parent.Username = 'SMITHBOB'
>>UNION
>>SELECT Parent.Username, Parent.Phone, ' ', ' ',
>>StatusLookup.Status, ' '
>>FROM Parent, StatusLookup
>>WHERE Parent.ItemKey NOT IN (select Child.ItemKey from Child) AND
>>Parent.StatusKey = StatusLookup.StatusKey AND
>>Parent.Username = 'SMITHBOB'
>>*---------------------------------
>>
>>ODBC may have problem with double ", so I would use single ' instead.
>>UNION need related field sizes equal. So, include as many spaces as needed in the second statement above between ' '.
>>
>>Good luck
>
>I spoke to soon on success. I tried the above with a filter of data I know did not have a child and the ODBC SQL statement works OK. But now I tested the same SQL statement with a filter of data I know does have children and I get the following:
>
>ODBC Error Code = 22005 (Error in assignment)
>
>
>[Microsoft][ODBC Microsoft Access Driver] Internal OLE Automation error
>
>So what could be causing the problem? If the parent has children they should be picked up in the first select and the second should find nothing. Can an UNION go against nothing (no records satisfy the conditions)?