From External Database

[DS]

Is this right. I'm pulling records from an external database. Do I
have the IN parts in the right place?
Thanks
DS


Me.RecordSource = "SELECT tblChecks.ChkBizDay, [EmpFirstName] & "" "" &
[EmpLastName] AS EMP, " & _
"tblChecks.ChkDate, tblChecks.ChkTime, tblChecks.CheckID, " & _
"tblChecks.ChkAlias, tblChecks.ChkCancelled, tblChecks.ChkKillID,
tblChecks.ChkTotal " & _
"FROM tblChecks IN ""//BACKOFFICE/WAREHOUSE/HISTORY.mdb"" " & _
"LEFT JOIN tblEmployees IN ""//BACKOFFICE/WAREHOUSE/HISTORY.mdb"" ON
tblChecks.ChkServer = tblEmployees.EmployeeID " & _
"WHERE (((tblChecks.ChkBizDay) >= [Forms]![frmReportDates]![TxtStart] " & _
"And (tblChecks.ChkBizDay) <= [Forms]![frmReportDates]![TxtEnd]) " & _
"And ((tblChecks.ChkCancelled) = -1) And ((tblChecks.ChkKillID) = 0)) " & _
"ORDER BY [EmpFirstName] & "" "" & [EmpLastName], tblChecks.ChkDate,
tblChecks.ChkTime;"

 

[DS]

OK i know it's not right! I'm getting a syntax error!
Thanks
DS

 

[DS]

I think perhaps the syntax is different when you use the recordsource
property..... and the join thing is also confusing...Any help
appreciated. I'll keep trying.
Thanks
DS


Me.RecordSource = "SELECT tblChecks.ChkBizDay, [EmpFirstName] & "" "" &
[EmpLastName] AS EMP, " & _
"tblChecks.ChkDate, tblChecks.ChkTime, tblChecks.CheckID, " & _
"tblChecks.ChkAlias, tblChecks.ChkCancelled, tblChecks.ChkKillID,
tblChecks.ChkTotal " & _
"FROM tblChecks IN '"& \\Backoffice\Warehouse\History.mdb & "' " & _
"LEFT JOIN tblEmployees ON tblChecks.ChkServer = tblEmployees.EmployeeID
" & _
"WHERE (((tblChecks.ChkBizDay) >= [Forms]![frmReportDates]![TxtStart] " & _
"And (tblChecks.ChkBizDay) <= [Forms]![frmReportDates]![TxtEnd]) " & _
"And ((tblChecks.ChkCancelled) = -1) And ((tblChecks.ChkKillID) = 0)) " & _
"ORDER BY [EmpFirstName] & "" "" & [EmpLastName], tblChecks.ChkDate,
tblChecks.ChkTime;"

 

[DS]

Well I'm down to Syntax Error in from clause...

Me.RecordSource = "SELECT tblChecks.ChkBizDay, [EmpFirstName] & "" "" &
[EmpLastName] AS EMP, " & _
"tblChecks.ChkDate, tblChecks.ChkTime, tblChecks.CheckID, " & _
"tblChecks.ChkAlias, tblChecks.ChkCancelled, tblChecks.ChkKillID,
tblChecks.ChkTotal " & _
"FROM tblChecks IN '\\Backoffice\Warehouse\History.mdb' " & _
"LEFT JOIN tblEmployees IN '\\Backoffice\Warehouse\History.mdb' " & _
"ON tblChecks.ChkServer = tblEmployees.EmployeeID " & _
"WHERE ((tblChecks.ChkCancelled) = -1) And ((tblChecks.ChkKillID) = 0) " & _
"ORDER BY [EmpFirstName] & "" "" & [EmpLastName], tblChecks.ChkDate,
tblChecks.ChkTime;"

DS

 

[DS]

DS wrote:
OK I removed the join and it works fine, so I'm guessing that my problem
lies in the join part. Does it need to be written differently?
Thanks
DS

 

[DS]

This seems to work. Could this be right?
DS

Me.RecordSource = "SELECT tblChecks.ChkBizDay, [EmpFirstName] & "" "" &
[EmpLastName] AS EMP, " & _
"tblChecks.ChkDate, tblChecks.ChkTime, tblChecks.CheckID, " & _
"tblChecks.ChkAlias, tblChecks.ChkCancelled, tblChecks.ChkKillID,
tblChecks.ChkTotal " & _
"FROM tblChecks LEFT JOIN tblEmployees ON tblChecks.ChkServer =
tblEmployees.EmployeeID IN '//Backoffice/Warehouse/History.mdb' " & _
"WHERE (((tblChecks.ChkBizDay) >= [Forms]![frmReportDates]![TxtStart] " & _
"And (tblChecks.ChkBizDay) <= [Forms]![frmReportDates]![TxtEnd]) " & _
"And ((tblChecks.ChkCancelled) = -1) And ((tblChecks.ChkKillID) = 0)) " & _
"ORDER BY [EmpFirstName] & "" "" & [EmpLastName], tblChecks.ChkDate,
tblChecks.ChkTime;"