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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

UNION QUERY (Sorting) 2
UNION SELECT Problem 2
Same data from 2 sources 2
DCount In Otherdb 27
Syntax Error 4
Are Nulls Covered? 2
Joining 2 Queries 3
Not returning a value 7

Back
Top