FastEddieB
Programmer
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"
dtEnd = DateValue(DateAdd("d", 1, pdtDate)) + TimeValue("3:00AM"
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("3:00AM" Then
ShiftDate = DateAdd("d", -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 "ShiftDate" 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.
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"
dtEnd = DateValue(DateAdd("d", 1, pdtDate)) + TimeValue("3:00AM"
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("3:00AM" Then
ShiftDate = DateAdd("d", -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 "ShiftDate" 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.