Linking Access Query to an Excel Spreadsheet

[FastEddieB]

I want to link several Access queries to an Excel spreadsheet, instead of using DDE.......

In my queries, I am using a Module called "IsInDay, so that any data time stamped between the hours of midnight and 3 AM, is considered to be the previous days data.

Here is the code:

Option Compare Database
Public Function IsInDay(ByVal pdtDate As Date) As Boolean

Dim dtStart As Date
Dim dtEnd As Date

dtStart = DateValue(pdtDate) + TimeValue("3:00AM&quot
dtEnd = DateValue(DateAdd("d", 1, pdtDate)) + TimeValue("3:00AM&quot

IsInDay = ((pdtDate >= dtStart) And (pdtDate <= dtEnd))

End Function

Public Function ShiftDate(ByVal pdtDate As Date, _
ByVal pdtTime As Date) As Date

Dim dtStart As Date

If pdtTime < TimeValue(&quot;3:00AM&quot Then
ShiftDate = DateAdd(&quot;d&quot;, -1, pdtDate)
Else
ShiftDate = pdtDate
End If

End Function


When I go to use Microsoft Query in Excel to set up the Link to the Access queries,and I select the query I want, get the following error

Undefined function &quot;ShiftDate&quot; in Expression

Does anyone know how I can get Excel to recognize this ShiftDate function that I use in Access. Any assistance would be greatly appreciated.

 

[SkipVought]

FastEddieB,

Why not copy the function into an Excel module?

Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[FastEddieB]

Well, I tried copying the module to Excel, and I am still getting the error &quot;undefined function &quot;ShiftDate&quot; in Expression&quot; whenever I try to select a query that is using this function in access...which is all of my queries by the way.

I am not a very experienced Excel user, but I would imgaine that the modules work the same.

After copying the module word for word, it is still not working.

I will continue to poke around with this.

Thank you again for your time.


Ed

 

[GPOTony]

I don't know if this will meet your requirements, but I have done the following:

1. Use MS Query to write and save an SQL query. You have to have an ODBC connection to the database for this.
2. Open Excel, select Data/Import External Data and select the query. This will return data to Excel.

Tony

 

[FastEddieB]

Hi GPOTony,

Thanks for the idea. The problem is I am using MS Query. When I use MS Query, create the obdc connection, and select the ShiftDate field, then I receive the error that &quot;Undefined function &quot;ShiftDate&quot; in Expression&quot;

Somehow I have to get Excel to recognize that module. I tried to copy and past it into Excel, but it still is giving me the same error.

I am still plugging away.

Thanks for your time.....

 

[MisterC]

It sounds like Excel can't find your function. Are you sure you put it in a module? That is, not as a function of &quot;Sheet1&quot; or &quot;ThisWorkbook&quot;.
If not, just use INSERT | MODULE and paste your function there.

 

[cityboy3]

I have run into the same problem. My Access query uses a custom function which generates the "undefined function" error when I try to base a pivot table on that query. I tried pasting the function into an Excel module, but that didn't help. I'm guessing that I would need to rewrite the query to return the original data and then use the function in excel to get the resukts that I want. Unfortunately, I need to do the calculation in Access because these results are part of a sub-query that needs to be processed further before returning results to the pivot table. Any ideas?

thanks

 

[SkipVought]

cityboy3,

Please post your question in a NEW THREAD.

Skip,
[sub]
[red]Sign above the facsimile apparatus at the music publisher:[/red]
If it ain't baroque...
Don't FAX it![/sub]