DLookup on a SQL statement

[VSolano]

Hello,

I am looking for some guidance on the following sql statement which I am using as a record source of a form.

I am not sure if I can use the DLookup in this way. Some of the issue is the comma after the Dlookup. when I have the comma is indicating error "3141 the SELECT statement incudes a reserved word or an argument name that is misspelled or missing, or the punctuation is incorrect

When I remove the comma does not show any error but the field that I am trying to lookup shows #Error.

any help will be greatly appretiated

SQL = "SELECT tblTaskAssignment.taskAssignmentID, tblTaskDefinition.TaskDescription, tblTaskAssignment.TaskAssignmentStatusUpdated, tbluser.username, tbluser.userid, tbluser.UserTypeID, tblTaskAssignment.TaskAssignmentStatus, tblTaskStatus.TaskStatus, tblTaskAssignment.TaskAssignedtoID, tblTaskAssignment.TaskAssignedBackupID, tblTaskAssignment.TaskApproverID, tblTaskAssignment.TaskAssignmentStartDate, tblTaskAssignment.TaskAssignmentEndDate, tblTaskAssignment.TaskFirstDueDate, tblTaskAssignment.TaskAssignedtoID, tblTaskAssignment.TaskAssignedBackupID, tblTaskAssignment.TaskApproverID, " _
                & "DLookUp([username],""tbluser"","" userID ="" & tblTaskAssignment.TaskAssignedBackupID) AS ApproverBackup," _
                & " FROM ((tblTaskAssignment INNER JOIN tblTaskDefinition ON tblTaskAssignment.TaskID = tblTaskDefinition.taskID) INNER JOIN tbluser ON tblTaskAssignment.TaskAssignedtoID = tbluser.userid) INNER JOIN tblTaskStatus ON tblTaskAssignment.TaskAssignmentStatusUpdated = tblTaskStatus.TaskStatusID " _
                & " WHERE (((tblTaskAssignment.TaskAssignmentStatusUpdated)=2))" _
                & " ORDER BY tblTaskDefinition.TaskDescription;"

 

[arnelgp]

don't use Dlookup.
use Query designer and drag those 4 tables and join them on their appropriate related fields.
test the query and use it instead of your SQL string.

SELECT tblTaskAssignment.taskAssignmentID, 
tblTaskDefinition.TaskDescription, 
tblTaskAssignment.TaskAssignmentStatusUpdated, 
tbluser.username, tbluser.UserID, 
tbluser.UserTypeID, 
tblTaskAssignment.TaskAssignmentStatus, 
tblTaskStatus.TaskStatus, 
tblTaskAssignment.TaskAssignedtoID, 
tblTaskAssignment.TaskAssignedBackupID, 
tblTaskAssignment.TaskApproverID, 
tblTaskAssignment.TaskAssignmentStartDate, 
tblTaskAssignment.TaskAssignmentEndDate, 
tblTaskAssignment.TaskFirstDueDate
FROM ((tblTaskAssignment LEFT JOIN tblTaskDefinition ON tblTaskAssignment.TaskID = tblTaskDefinition.TaskID) 
LEFT JOIN tbluser ON tblTaskAssignment.TaskAssignedtoID = tbluser.UserID) 
LEFT JOIN tblTaskStatus ON tblTaskAssignment.TaskAssignmentStatusUpdated = tblTaskStatus.TaskStatusID;

 

[plog]

Agree with arnelgp, DLookup has no use in a SELECT query. But what are you trying to do?

Let's suppose that Dlookup worked, what did you expect to happen in your SQL statement? I mean, the Dlookup is part of the SELECT, so it would have looked for a field named whatever your Dlookup returned and would have broken there in a different way (Field not found). Let's say the Dlookup returned 'plog' your SQL would have been like this:

SELECT ID, Description, ..., plog AS ApproverBackup, FROM...

I'm pretty sure you don't have a field named [plog] in any table, so what did you hope to accomplish?

 

[VSolano]

I just using the DLookup to return a user name/approver from the same table

 

[arnelgp]

I just using the DLookup to return a user name/approver from the same table

you drag tblUser, 2 times and join them on appropriate field:

SELECT
tblTaskAssignment.taskAssignmentID,
tblTaskDefinition.TaskDescription,
tblTaskAssignment.TaskAssignmentStatusUpdated,
tbluser.username,
tbluser.UserID, tbluser.UserTypeID,
tblTaskAssignment.TaskAssignmentStatus,
tblTaskStatus.TaskStatus,
tblTaskAssignment.TaskAssignedtoID,
tblTaskAssignment.TaskAssignedBackupID,
tblTaskAssignment.TaskApproverID,
tblTaskAssignment.TaskAssignmentStartDate,
tblTaskAssignment.TaskAssignmentEndDate,
tblTaskAssignment.TaskFirstDueDate,
tbluser_1.username AS ApproverBackup
FROM (((tblTaskAssignment
LEFT JOIN tblTaskDefinition ON tblTaskAssignment.TaskID = tblTaskDefinition.TaskID)
LEFT JOIN tbluser ON tblTaskAssignment.TaskAssignedtoID = tbluser.UserID)
LEFT JOIN tblTaskStatus ON tblTaskAssignment.TaskAssignmentStatusUpdated = tblTaskStatus.TaskStatusID)
LEFT JOIN tbluser AS tbluser_1 ON tblTaskAssignment.TaskAssignedBackupID = tbluser_1.UserID;

 

[Pat Hartman]

@arnelgp has shown you the correct way to solve this problem. It is always better to use left joins instead of the dLookup. That allows the data engine to create the most efficient retrieval of the data. You use Left joins to solve the problem of the record you are trying to retrieve not being there. Left join and join return the same data if the id is found in the second table. Always using left join in this situation is simply a safety play to avoid unanticipated errors

Regarding why your existing statemement doesn't work - can't say for sure without the actual database but since the dLookup is the last attribute selected in the Select statement, it should NOT be followed by coma.

There is nothing logically "invalid" about using domain functions in queries. Sometimes you can't avoid them. However when you can use a join instead, the join is ALWAYS the most efficient solution so you should always try using a join first. Sometimes, if the join produces a non-updateable recordset, you will have to fall back on the domain function but only in that case should you even consider using a domain function in a query or inside a vba code loop. Why? each execution of the domain function - one per row of data retrieved - spawns another query so if you retrieve 10,000 records, you must run 10,000 queries to do it and that is very much more expensive than the join.

 

[VSolano]

Thanks all
I was able to go around this issue by adding a table multiple times so I can link it to the proper Id