From External Database

  • Thread starter Thread starter DS
  • Start date Start date
D

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;"
 
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;"
 
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 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
 
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;"
 
Back
Top