Custom Function DCount

[Mudbutt]

I'm using Office 2007, I have a query in Access that is calculating NetWorkingDays. This query accounts for Holidays, in which I reference in a table. However, in order to account for that, I have to use DCount in my formula. This query needs to be linked to an Excel report via a QueryTable. However, it doesn't seem Excel likes the DCount formula because it will not let me connect the query saying that it can't find my "tblHolidays" table.

Does anyone know how to get around this? I tried creating a custom function as well and using the function, but then Excel didn't recognize the function because it was created in Access. Here is my formula that I use to calculate the networking days.

WorkDayDiff: -(([Req#dlv#dt]-[Act Date])-(DateDiff("ww",[Act Date],[Req#dlv#dt])*2))-DCount("[Holidays]","tblHolidays","[Holidays] > #" & [Act Date] & "# and [Holidays] < #" & [Req#dlv#dt] & "#")

 

[Joe4]

Please be sure to provide links in your cross-posts, pursuant to rule #10 here: http://www.mrexcel.com/forum/showthread.php?t=99490

Cross-post: http://www.mrexcel.com/forum/showthread.php?t=582318

 

[Mudbutt]

Thanks for informing me of this! I wasn't sure which section it was more appropriate in considering it relates to both Excel and Access.

 

[Joe4]

That's fine. We just ask that you mention it in both thread (and provide links) so people can see that it might be answered elsewhere.