ODBC microsoft access driver undefine Function

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I created a field in my query using the following function:

Dealer to Install: HolidayWorkDayDiff([Date Dealer Rec'd Equipment],[Install
Date])

When I run the query it returns exactly what I am looking for, but when I
return to the excel sheet and try to refresh the spreadsheet it tells me:

[Microsoft][ODBC Microsoft Access Driver] Undefined function
'HolidayWorkDayDiff' in expression.

Can anyone help me?
 
Are you saying you're using the function in your query, but trying to call
that query from within Excel?

Unfortunately, you can't. When you're running queries from outside of
Access, you're strictly going through Jet, and Jet doesn't know anything
about user-defined VBA functions.
 
Exactly. The query is needed to refresh an excel sheet which shows the
metrics of how many days it is currently taking from oder to install. Would
like to keep a metrics of this to record time frame. Is there a way to work
around this. Is there a formula in excel that calculates the work day minus
holiday in one field since excel does not recognise define VBA function. I
have tried network days but that is not providing me with acurate number of
days if the date from is less then date to. I need to record negative days as
well and I believe your VBA function did exactly what I needed in the query.

Douglas J. Steele said:
Are you saying you're using the function in your query, but trying to call
that query from within Excel?

Unfortunately, you can't. When you're running queries from outside of
Access, you're strictly going through Jet, and Jet doesn't know anything
about user-defined VBA functions.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


KrisN said:
I created a field in my query using the following function:

Dealer to Install: HolidayWorkDayDiff([Date Dealer Rec'd
Equipment],[Install
Date])

When I run the query it returns exactly what I am looking for, but when I
return to the excel sheet and try to refresh the spreadsheet it tells me:

[Microsoft][ODBC Microsoft Access Driver] Undefined function
'HolidayWorkDayDiff' in expression.

Can anyone help me?
Click to expand...
Click to expand...
 
You should be able to adapt the Access function for use in Excel.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


KrisN said:
Exactly. The query is needed to refresh an excel sheet which shows the
metrics of how many days it is currently taking from oder to install.
Would
like to keep a metrics of this to record time frame. Is there a way to
work
around this. Is there a formula in excel that calculates the work day
minus
holiday in one field since excel does not recognise define VBA function. I
have tried network days but that is not providing me with acurate number
of
days if the date from is less then date to. I need to record negative days
as
well and I believe your VBA function did exactly what I needed in the
query.

Douglas J. Steele said:
Are you saying you're using the function in your query, but trying to
call
that query from within Excel?

Unfortunately, you can't. When you're running queries from outside of
Access, you're strictly going through Jet, and Jet doesn't know anything
about user-defined VBA functions.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


KrisN said:
I created a field in my query using the following function:

Dealer to Install: HolidayWorkDayDiff([Date Dealer Rec'd
Equipment],[Install
Date])

When I run the query it returns exactly what I am looking for, but when
I
return to the excel sheet and try to refresh the spreadsheet it tells
me:

[Microsoft][ODBC Microsoft Access Driver] Undefined function
'HolidayWorkDayDiff' in expression.

Can anyone help me?
Click to expand...
Click to expand...
Click to expand...
 
Hi Doug, how would I go about adapting this function to excel?

Douglas J. Steele said:
You should be able to adapt the Access function for use in Excel.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


KrisN said:
Exactly. The query is needed to refresh an excel sheet which shows the
metrics of how many days it is currently taking from oder to install.
Would
like to keep a metrics of this to record time frame. Is there a way to
work
around this. Is there a formula in excel that calculates the work day
minus
holiday in one field since excel does not recognise define VBA function. I
have tried network days but that is not providing me with acurate number
of
days if the date from is less then date to. I need to record negative days
as
well and I believe your VBA function did exactly what I needed in the
query.

Douglas J. Steele said:
Are you saying you're using the function in your query, but trying to
call
that query from within Excel?

Unfortunately, you can't. When you're running queries from outside of
Access, you're strictly going through Jet, and Jet doesn't know anything
about user-defined VBA functions.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


I created a field in my query using the following function:

Dealer to Install: HolidayWorkDayDiff([Date Dealer Rec'd
Equipment],[Install
Date])

When I run the query it returns exactly what I am looking for, but when
I
return to the excel sheet and try to refresh the spreadsheet it tells
me:

[Microsoft][ODBC Microsoft Access Driver] Undefined function
'HolidayWorkDayDiff' in expression.

Can anyone help me?
Click to expand...
Click to expand...
Click to expand...
 
Unfortunately, writing Excel functions that communicate with Access isn't my
forté...

If the issue is negative days, write a wrapper function that determines
which date is less, swap them so Date From is always less, use NetworkDays,
and reverse the sign if you swapped the dates before calling the function.

If the issue is holidays, write a function that opens a recordset to query
the Holidays table in Access. SQL like "SELECT Count(*) AS HolidayCount FROM
Holidays WHERE HolidayDate >= DateFrom and HolidayDate <= DateTo" will give
you the total number of holidays within your range: simply subtract that
from what NetworkDays returns.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


KrisN said:
Hi Doug, how would I go about adapting this function to excel?

Douglas J. Steele said:
You should be able to adapt the Access function for use in Excel.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


KrisN said:
Exactly. The query is needed to refresh an excel sheet which shows the
metrics of how many days it is currently taking from oder to install.
Would
like to keep a metrics of this to record time frame. Is there a way to
work
around this. Is there a formula in excel that calculates the work day
minus
holiday in one field since excel does not recognise define VBA
function. I
have tried network days but that is not providing me with acurate
number
of
days if the date from is less then date to. I need to record negative
days
as
well and I believe your VBA function did exactly what I needed in the
query.

:

Are you saying you're using the function in your query, but trying to
call
that query from within Excel?

Unfortunately, you can't. When you're running queries from outside of
Access, you're strictly going through Jet, and Jet doesn't know
anything
about user-defined VBA functions.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


I created a field in my query using the following function:

Dealer to Install: HolidayWorkDayDiff([Date Dealer Rec'd
Equipment],[Install
Date])

When I run the query it returns exactly what I am looking for, but
when
I
return to the excel sheet and try to refresh the spreadsheet it
tells
me:

[Microsoft][ODBC Microsoft Access Driver] Undefined function
'HolidayWorkDayDiff' in expression.

Can anyone help me?
Click to expand...
Click to expand...
Click to expand...
 
Back
Top