DCount In Otherdb

  • Thread starter Thread starter DS
  • Start date Start date
OK This works....
Dim rs As DAO.Recordset

Set rs = CurrentDb.OpenRecordset("SELECT Count(*) FROM " & _
"[MS Access;Database=\\Backoffice\C$\Warehouse\History.mdb].tblVoidDetails "
& _
"WHERE CDBizDay BETWEEN " & Format(Forms!frmReportDates!TxtStart,
"\#yyyy\-mm\-dd\#") & _
"AND " & Format(Forms!frmReportDates!TxtEnd, "\#yyyy\-mm\-dd\#"),
dbOpenSnapshot)

Me.TxtVoids = rs(0)
rs.Close

This Doesn't...Anything to do with the Format Missing?
Set rs = CurrentDb.OpenRecordset("SELECT Count(*) FROM " & _
"[MS Access;Database=\\Backoffice\C$\Warehouse\History.mdb].tblVoidDetails "
& _
"WHERE CDBizDay BETWEEN " & Forms!frmReportDates!TxtStart & "AND " &
Forms!frmReportDates!TxtEnd, dbOpenSnapshot)
Me.TxtVoids = rs(0)
rs.Close


Thanks
DS
 
This is what I finally ended up with...

Dim rs As DAO.Recordset
Dim rs2 As DAO.Recordset

Set rs = CurrentDb.OpenRecordset("SELECT Count(*) FROM " & _
"[MS Access;Database=\\Backoffice\C$\Warehouse\History.mdb].tblVoidDetails "
& _
"WHERE CDBizDay BETWEEN " & Format(Forms!frmReportDates!TxtStart,
"\#yyyy\-mm\-dd\#") & _
"AND " & Format(Forms!frmReportDates!TxtEnd, "\#yyyy\-mm\-dd\#"),
dbOpenSnapshot)

Set rs2 = CurrentDb.OpenRecordset("SELECT Count(*) FROM " & _
"tblVoidDetails " & _
"WHERE CDBizDay BETWEEN " & Format(Forms!frmReportDates!TxtStart,
"\#yyyy\-mm\-dd\#") & _
"AND " & Format(Forms!frmReportDates!TxtEnd, "\#yyyy\-mm\-dd\#"),
dbOpenSnapshot)

Me.TxtVoids = rs(0) + rs2(0)
rs.Close
rs2.Close

Thanks
DS
 
DS said:
OK This works....
Dim rs As DAO.Recordset

Set rs = CurrentDb.OpenRecordset("SELECT Count(*) FROM " & _
"[MS Access;Database=\\Backoffice\C$\Warehouse\History.mdb].tblVoidDetails
" & _
"WHERE CDBizDay BETWEEN " & Format(Forms!frmReportDates!TxtStart,
"\#yyyy\-mm\-dd\#") & _
"AND " & Format(Forms!frmReportDates!TxtEnd, "\#yyyy\-mm\-dd\#"),
dbOpenSnapshot)

Me.TxtVoids = rs(0)
rs.Close

This Doesn't...Anything to do with the Format Missing?
Click to expand...

Yes. Date fields MUST be delimited with # characters and must be in a format
Access will treat correctly. The Format statement does both of those for
you.

Set rs = CurrentDb.OpenRecordset("SELECT Count(*) FROM " & _
"[MS Access;Database=\\Backoffice\C$\Warehouse\History.mdb].tblVoidDetails
" & _
"WHERE CDBizDay BETWEEN " & Forms!frmReportDates!TxtStart & "AND " &
Forms!frmReportDates!TxtEnd, dbOpenSnapshot)
Me.TxtVoids = rs(0)
rs.Close
Click to expand...
 
Note that your second SQL statement is different than what you were trying
to do in the UNION query.

The following should work for you:

Set rs = CurrentDb.OpenRecordset("SELECT Count(*) FROM " & _
"[MS
Access;Database=\\Backoffice\C$\Warehouse\History.mdb].tblVoidDetails " & _
"WHERE CDBizDay BETWEEN " &
Format(Forms!frmReportDates!TxtStart, "\#yyyy\-mm\-dd\#") & _
" AND " & Format(Forms!frmReportDates!TxtEnd, "\#yyyy\-mm\-dd\#") & _
" UNION ALL " & _
"SELECT Count(*) FROM tblVoidDetails " & _
"WHERE CDBizDay BETWEEN " & _
Format(Forms!frmReportDates!TxtStart, "\#yyyy\-mm\-dd\#") & _
" AND " & Format(Forms!frmReportDates!TxtEnd,
"\#yyyy\-mm\-dd\#"),dbOpenSnapshot)
 
DS said:
Dirk,
First I tried it and I got the message that the column count wasn't equal.
Then I moved the code around and now I get a wrong criteria order!

Dim strCriteria As String
Dim rs As DAO.Recordset

strCriteria = "WHERE CDBizDay BETWEEN " &
Format(Forms!frmReportDates!TxtStart, "\#mm/dd/yyyy\#") And
Format(Forms!frmReportDates!TxtEnd, "\#mm/dd/yyyy\#")

Set rs = CurrentDb.OpenRecordset("SELECT Count(*) FROM [MS
Access;Database=\\Backoffice\C$\Warehouse\History.mdb].tblVoidDetails " &
strCriteria & _
"UNION ALL " & _
"SELECT * FROM tblVoidDetails " & strCriteria, dbOpenSnapshot)

Thanks
DS

I think we are close!
Click to expand...


I hadn't noticed before that you had changed my code in such a way as to
make the union query invalid. And now I see that you've changed the code I
gave you for strCriteria, too. So there's lots to fix. Please try this
code as
written, and post back what results you get:

'----- start of suggested code ----
strCriteria = "WHERE CDBizDay BETWEEN " & _
Format(Forms!frmReportDates!TxtStart, "\#mm/dd/yyyy\#") & _
" And " & _
Format(Forms!frmReportDates!TxtEnd, "\#mm/dd/yyyy\#")

Set rs = CurrentDb.OpenRecordset( _
"SELECT Count(*) FROM " & _
"(" & _
"SELECT CBizDay FROM " & _
"[MS Access;Database=\\Backoffice\C$\Warehouse\History.mdb]" & _
".tblVoidDetails " & strCriteria & _
"UNION ALL " & _
"SELECT CBizDay FROM tblVoidDetails " & strCriteria & _
")", _
dbOpenSnapshot)

Me.TxtVoids = rs(0)
rs.Close
'----- end of suggested code ----
 
Thanks Doug. I tried it and I got the Error message that it could not find
the installable ISAM.
Not aclue what this means!
 
The only thing that occurs to me is that it might have something to do with
the fact that the table has the same name in both subqueries, but they're in
different databases.
 
Back
Top