I wanted to give back to the forum and check out my latest insight. I’ve searched around (and worked out my recent understanding) on the forum but I haven’t found a single thread that laid it out so I could fully grok the issue/solution. So I’m hoping to produce a searchable thread to help those who come after.
I have a Contacts db where I need to limit the Contacts to those who match the person who is logged in, unless the person is the Adminstrator, who should see everybody. So I was thinking I needed an IIF statement in the criteria of the LoggedIn column of a SELECT query that would read something like: IIF(LoggedIn=Admin, “*”, LoggedIn). But Access refused to accept the solution, giving a “Data type mismatch in criteria expression” error. I tried double quotes instead of “*”, and “ “, but got the same message.
Searching, I found entries where people spoke of somehow doing it in the Field definition and getting a field of True/False for the column and then put “True” or “False” as the criterion for the column. Funny how it took so long to get my little head around it. <<sigh>> Finally I got it. So I added a new column to my query as such: Administrator: IIF(LoggedIn = Admin, “True”, “False”). This essentially tagged every returned record in the query as either being True or False depending on whether the Administrator is the person who is logged in. Then I added DLookup criteria to the other field that needed criteria (the one to pick the records belonging to the logged in person) along with “False” in the Administrator column in an “AND” statement and “True” for the Administrator as an “OR” statement without any additional criteria limiting the person logged in (since the Administrator doesn’t own any records). So it was like this:
It all works great. Thanks everyone for your patience over the years in explaining this over and over again. Hopefully this helps someone else trying to do this in the future – and, naturally, if anyone has a correction/improvement to this, I trust that you’ll chime in.
Best,
I have a Contacts db where I need to limit the Contacts to those who match the person who is logged in, unless the person is the Adminstrator, who should see everybody. So I was thinking I needed an IIF statement in the criteria of the LoggedIn column of a SELECT query that would read something like: IIF(LoggedIn=Admin, “*”, LoggedIn). But Access refused to accept the solution, giving a “Data type mismatch in criteria expression” error. I tried double quotes instead of “*”, and “ “, but got the same message.
Searching, I found entries where people spoke of somehow doing it in the Field definition and getting a field of True/False for the column and then put “True” or “False” as the criterion for the column. Funny how it took so long to get my little head around it. <<sigh>> Finally I got it. So I added a new column to my query as such: Administrator: IIF(LoggedIn = Admin, “True”, “False”). This essentially tagged every returned record in the query as either being True or False depending on whether the Administrator is the person who is logged in. Then I added DLookup criteria to the other field that needed criteria (the one to pick the records belonging to the logged in person) along with “False” in the Administrator column in an “AND” statement and “True” for the Administrator as an “OR” statement without any additional criteria limiting the person logged in (since the Administrator doesn’t own any records). So it was like this:
Code:
LoggedInPerson | Administrator
Dlookup(…x) |”False”
|”True”It all works great. Thanks everyone for your patience over the years in explaining this over and over again. Hopefully this helps someone else trying to do this in the future – and, naturally, if anyone has a correction/improvement to this, I trust that you’ll chime in.
Best,
