Solved

using external filepath within the DCOUNT

Posted on 2014-02-28
2
600 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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

707 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

18 Experts available now in Live!

Get 1:1 Help Now