DLookup on a SQL statement (1 Viewer)

[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;

 

[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