I'm using Access 2002. Where / how do I find the DAO library??
:
These lines:
Dim rst As DAO.Recordset
Dim DB As DAO.Database
require a reference to a DAO library.
Check your VBA references. It should include
Microsoft DAO 3.6 Object Library
What version of Access are you using?
--
Dave Hargis, Microsoft Access MVP
:
Yes. I have tblHolidays with the field name HolidayDate.. I have a data
table tblTest with the fields StartDate and EndDate.
When I run the query I get a Compile error: Method or data member not found.
:
Do you have a table in your database with the name specificed in the function?
Does it have the field name specified in the function?
--
Dave Hargis, Microsoft Access MVP
:
I get complie errors at those locations. What I meant was as you type in
code, at certain points you get a "drop Down" of choices for the code. Those
choices were not available in those drop downs
Hope this is more clear.
:
I don't understand what you mean by selecting a FindFirst or a NoMatch. You
don't select them.
It would be helpful to know what error you got on that line.
--
Dave Hargis, Microsoft Access MVP
:
Ok, That made some progress. I had saved the module with thte same name as
the function.
Now the code is interrupting at the r"st.FindFirst" (there is no "first" for
me to select) and at "If rst.NoMatch" (there is no "NoMatch" for me to
select).
Are there other options or how do I edit the code to achieve the same
results??
Working with the "workingdays2" function
:
Check these things:
1. Be sure the function and the module the function is in are not named the
same name. You cannot name a sub or function in the module the same as the
module name.
2. The query and the function are both in the same mdb.
--
Dave Hargis, Microsoft Access MVP
:
Then I am really confused. Maybe it's the way I created the "Function".
I've tried it both ways keeping in and removing the "Option compare Database"
line. I've also tried your "CalcWorkDays" function and get the same results.
Im in the Modules section, I click New and enter the information exactly as
it is written. The titles show in the module "Window" and when I "build" my
expression in the query they show up in functions... They just aren't being
recognized.
Seems like it should be straightforward but it isnt....
:
There is nothing else to set. If the function is defined as a Public
function and it is in a standard module in the mdb where the query is being
executed, it should run.
--
Dave Hargis, Microsoft Access MVP
:
I created the "function in the module section. In the query i have set the
calculated field as you describe. Still coming up undefined. Is there a
process for "Defining" this function that I am missing??
:
If the function is specified as Public Function and it is in a Standard
module (not a report or form module), then to use it in a query, you create a
calculated field and pass the two dates to the function
DaysWorked: WorkingDays2([StartDate, EndDate)
--
Dave Hargis, Microsoft Access MVP
:
It does, just as outlined from the mvp/access site. How do I encorporate it
in my query I guess is the thing I'm having issues with.
Is there something in the function that is rendering it "Undefined"? Is
there something special I need to do in the querey to make it work??
Thanx Again
:
You should have created the function in a standard module. I would expect
your function might have started like:
Public Function WorkingDays2(datStart As Date, datEnd As Date) As Integer
....
You could then use the function almost anywhere you would use any other
function. Make sure you have date values in both fields (no nulls) unless
your function can handle nulls.
--
Duane Hookom
Microsoft Access MVP
:
First time I've tried this. I have created the "WorkingDays2" function from
the mvp/access site. I Hope I did it correctly as it was my first.
Now, I want to use this function in a query to determine the working days
between two dates. I have my startdate and enddate fields and when I try to
calculate the difference the error message that returns tells me I have an
"Undefined Function Workingdays2 in Expression".
So, how do I define this function? What should I look for as an error in
the writing of the expression?.... Where did I go wrong??
Thanx for your help
