using external filepath within the DCOUNT

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")
Karen SchaeferAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
mbizupConnect With a Mentor 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.
0
 
Karen SchaeferAuthor 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.
0
All Courses

From novice to tech pro — start learning today.