Transfer of Data from MS Access using MS Query in Excel

[FastEddieB]

In MS Access, I have a large group of queries that use a module I wrote called IsInDay. We collect data by shifts, but our 2nd shift runs a couple of hours after midnight, so when you query data using a shift and a date, it misses the entries after midnight.

The IsInDay module, looks at the time stamps of each entry and then gives them a "Shiftdate" value of the date before.(Anything between midnight and 3am)

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

This works great in Access and gives us exactly what we are looking for.

Now, I am attempting to create sreadsheets that automatically update in Excel. When I use MS Query to set up the transfer of data, and I select a query to use, I get the following error message.

&quot;Undefined Function 'ShiftDate' in Expression&quot;

I tried inserting a copy of this module, as a module in the spreadsheet, and this still doesn't work. Does anyone have any clues. Am I doing something wrong?

Thank ahead of time for any and all suggestions

 

[rss01]

I use this module for quite a few queries that I need the data in excel for.

This one is for if you already have an existing workbook but you just want the data updated. With a few modifications you can create a new workbook.

Option Compare Database
Option Explicit

'The path of your spreadsheet
Const strPath1 = &quot;c:\file.xls&quot;

Public Sub SendExcel()

Dim dbase As DAO.Database
Dim rs As DAO.Recordset
Dim objXL As Excel.Application
Dim objWKB As Excel.Workbook
Dim objWSHT As Excel.Worksheet

Const ConWKBK = strPath1

Set dbase = CurrentDb
Set objXL = New Excel.Application
With objXL
.Visible = False
Set objWKB = .Workbooks.Open(ConWKBK)

'Insert your query name
Set rs = dbase.OpenRecordset(&quot;Your Query Name&quot;, dbOpenForwardOnly)

'Sheet1 is the name of the worksheet that will be updated
Set objWSHT = objWKB.Worksheets(&quot;Sheet1&quot
objWSHT.Activate

'A2 is the cell that this will start inserting you data in
objWSHT.Range(&quot;A2&quot.CopyFromRecordset rs
objWKB.Save

End With


'CLEAN UP SECTION
On Error Resume Next

objXL.Quit


Set objWSHT = Nothing
Set objWKB = Nothing
Set objXL = Nothing
Set rs = Nothing
Set dbase = Nothing



End Sub