Field indentifiers as criteria

[kilobyte]

I am wondering if it is possible to use a table/field identifier as part of a criteria expression in a query.

The field that I need to use in the expression is not a part of the focus table, so I think I need to use a full identifier, if that is possible. I have seen such identifiers used for controls on forms, but never fields in tables.

For example, something like the following as a criteria expression:

<>[Tables]![tblUserData]![UserId]

Obviously, that didn't work. My question is, is it possible to identify a field from another table as part of a criteria in a query. If so, how?

Just in case it helps, the reason I am doing this is I am trying to create an append query that only appends data that is not already there.

 

[Murli]

Use this in your criteria section:

Not In (select UserId from tblUserData)

Murli

 

[kilobyte]

Beautiful Murli. Worked like a charm!

Thanks!

 

[kilobyte]

I just want to make sure I understand this right.


Not In is a function and (select UserId from tblUserdata) is a query? So am I correct in assuming I could use more complex sql statements as part of functions?

 

[Murli]

Not In or In is used instead of <> or = with SQL statements.

You could use them anywhere in your sql statements.

 

[kilobyte]

You can kinda see that I don't know what I am talking about. Anyway, done and learned.

Thanks!