Append From A Query With Iif Expression.

Hey folks,

I'll try to be succinct.


I have a short module using the transferspreadsheet command importing an excel worksheet into a table. (blahblah_import).
Before I can append those records to the permanent table (blahblah) I need to pull the correct # values from various lookup tables.
I have a query written which does the lookup. For the user name / user id lookup I have an IIf statement so if the lookup finds nothing, it will use "UNKNOWN" (no quotes).
When used as a select query, it works great. Where it can find a user id it puts that in the user field, where it can't it puts "UNKNOWN" (no quotes).
When I set it up as an Append query, it appears to work fine (appends correct # of records, no violations, etc. BUT wherever it did find a user id - the field in the appended to table (blahblah) is blank. This is even a required field (if i click in on the the blank fields, i then have to hit ok a bunch of times for each empty field)
Here is the IIf statement
user: IIf([lookup_user.value] Is Not Null,[lookup_user.value],"UNKNOWN")


Any help is appreciated, I've tried using a Make-Table query instead, but with similar results, which leads me to believe that the Iif is where the problem lies.

Would changing any of the query properties help?

thanks