using external filepath within the DCOUNT

Karen Schaefer
Karen Schaefer used Ask the Experts™
on
is it possible to use something like an "In" statement within the DCOUNT funciton, if so what is the proper syntax

        i = DCount("RecordLock", "tblInvoice", "ContractNumber =" & Chr(34) & _
                    gContractID & Chr(34) & " and RecordLock = 0")

I want to change the database source from local to an external mdb.
        i = DCount("RecordLock", "tblInvoice IN '\\filepath\mdbname.mdb", "ContractNumber =" & Chr(34) & _
                    gContractID & Chr(34) & " and RecordLock = 0")
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Nerd
Most Valuable Expert 2012
Top Expert 2013
Commented:
That won't work in a DCount afaik.

However, you can set up and save a query as qryInvoice:

Select * From tblInvoice IN '\\filepath\mdbname.mdb'

Open in new window


And run your dCount against that query:

 
i = DCount("RecordLock", "qryInvoice", "ContractNumber =" & Chr(34) & _
                    gContractID & Chr(34) & " and RecordLock = 0")

Open in new window


Alternatively, embed the select statement in VBA and use recordset code instead of DCount.
Karen SchaeferBI ANALYST

Author

Commented:
THANKS FOR GREAT ASSIST, that did it simplified my code - since I need to change all my recordsets and sql strings to look at either local tables or external tables.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial