Aug 25, 2009 07:50 AM|hans_v|LINK
UserID is the name of the field in the Access table. It gets is value from a Profile Parameter when the user makes a CHOICE and the record is created - so the value of both "UserID" and "Profile.UserName" are strings.
The error clearly tells that somewhere there's a datatype mismatch. Did you test in Access if
SELECT ChoiceID FROM PAST-CHOICES WHERE (UserID = 'aUserName')
is really returning a set of Long Integers? (replace aUserName with an existing username)
And what about
SELECT * FROM ALL-OPTIONS WHERE ChoiceID NOT IN (SELECT ChoiceID FROM PAST-CHOICES WHERE (UserID = 'aUserName'))
Is that returning the records that you expect?
That's what I was thinking . . .but I was hoping that there might be a quick-n-dirty way of doing it so I tried the nested query. Do you have any ideas/suggestions for excluding the results of one query from another query? And then displaying the results in
The nested query is one option, and you could also do it with a join as explained by Alok Arora. The third option is to do it in code, but as the values come from another table, the first two options are preferred.