I am working on 2 databases that are linked together: Action Items List and UserInfo. I have imported the Action item List from my Sharepoint site.
I am trying to display only the "actions" that match with the criteria [Enter your name]. In order to do so, I have looked at the relationship of the 2 databases and we can see that "Owner.Value" (text attribut of the "Action Items list" database) is connected to "ID" (the foreign key of UserInfo database). In order to get the expected results, I run the querry and I enter my name but I get the following error:
Maybe someone could help me. I rely on your expertise. Thank you in advance for your support!
1 Answer
Firstly, to correct your DLookup expression: since the criteria is filtering the value of a text field (Name), the value used for the filter must be enclosed within single or double-quotes, else the result will be:
Name = Thomas Orvain
With Thomas and Orvain treated as separate fields in the resulting SQL.
To remedy this, you could change the DLookup expression to:
DLookup("Name", "UserInfo", "Name = '" & [Enter your name] & "'")
However, since you have already constructed an INNER JOIN between the Action Items List table & UserInfo table, the DLookup expression is not required at all.
Instead, you can apply selection criteria to the Name field directly, e.g. copy the following to the SQL view of your query:
select
[Action Items List].Action,
[Action Items List].Status,
[Action Items List].Owner.Value
from
[Action Items List] inner join UserInfo on
[Action Items List].Owner.Value = UserInfo.ID
where
UserInfo.Name = [Enter your name]
