Append from a Query with Iif expression.

[natsirtm]

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

 

[gemma-the-husky]

the syntax may not be quite right - you don't need the .value qualifier

try

user: IIf(isnull([lookup_user]),"UNKNOWN",[lookup_user])

isnull should be one word. the opposite is "not isnull", and possibly not as you have put "is not null"

 

[natsirtm]

.value is actually the fieldname, I believe it needs to be included - but I will check, thanks.

the syntax may not be quite right - you don't need the .value qualifier

try

user: IIf(isnull([lookup_user]),"UNKNOWN",[lookup_user])

isnull should be one word. the opposite is "not isnull", and possibly not as you have put "is not null"

 

[neileg]

value is a reserved word. Using reserved words can cause all sorts of problems in code, such as you are experiencing!

 

[natsirtm]

thanks, I tried changing the fieldname value to val, but am getting the same results.