Solved

using external filepath within the DCOUNT

Posted on 2014-02-28
2
621 Views
Last Modified: 2014-02-28
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")
0
Comment
Question by:Karen Schaefer
2 Comments
 
LVL 61

Accepted Solution

by:
mbizup earned 500 total points
ID: 39896297
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
 

Author Closing Comment

by:Karen Schaefer
ID: 39896346
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

Featured Post

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

815 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now