Access Query to Excel - Doesn't like functions?! 1

[Spidy6123]

Hey guys,

I have a Excel doc pulling data from an Access database where it does all of my heavy sorting..

The access query uses a function.. when I try to import the query to the excel doc it gives me an error.
"Undefined function in Expression"

The query works fine on its own in access...
I'm guessing I have to somehow share the module from access as well.

I should mention that the database is configured under my datasources(ODBC) as a local database...

Any help is appreciated.

 

[dhookom]

The query in Excel just won't support some user-defined and othe functions from Access. You may need to write your own expressions/functions in Excel or change your Access function.

It might help to know the specifics of your function.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[Spidy6123]

well they're not to complicated I suppose..

sort record by last 12 FULL months..
so July and -12 from there.. access sux and can't do it anyway on it's own.. so it requires a function.. as far as I know anyways

 

[lespaul]

I don't see any issues with sorting by the last 12 full months. What did you try that didn't work and can you post the actual function so we can see what you're doing?


Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi

 

[Spidy6123]

http://office.microsoft.com/en-us/assistance/HP030840601033.aspx

says I can attach functions to a query.. where the heck is my functions list?! in my vba editor..


here's the function anyways:
Public Function FindLastMonth(MonthStartFlag As Boolean) As Date
Dim dtMonth As Date
Dim stMyDate As String

'------------------------------------------------------
'- Get the first day of the current month as a string -
'------------------------------------------------------
stMyDate = "01/" & Format$(Date, "mm/yyyy")

'------------------------------------------------------
'- Subtract 1 (day) from this date, and store in date -
'- variable -
'------------------------------------------------------
dtMonth = CDate(Format$(stMyDate, "dd/mm/yyyy")) - 1

'------------------------------------------------------
'- To find the first day of the previous month, -
'- repeat the process -
'------------------------------------------------------
If MonthStartFlag = True Then
stMyDate = "01/" & Format$(dtMonth, "mm/yyyy")
myyear = Format$(Date, "yyyy") - 1
dtMonth = CDate(Format$(stMyDate, "dd/mm/" & myyear))
End If

'------------------------------------------------------
'- Return the required value as a date -
'------------------------------------------------------
FindLastMonth = dtMonth

End Function

 

[dhookom]

I think I got the same results as you without using a user-defined function. I created a query in Northwind with SQL like:

SELECT Employees.EmployeeID, Employees.LastName, DateSerial(Year(Date()),Month(Date())-1,1) AS BofMth, DateSerial(Year(Date()),Month(Date()),0) AS EofMth, IIf([EmployeeID]<4,DateSerial(Year(Date()),Month(Date())-1,1),DateSerial(Year(Date()),Month(Date()),0)) AS BorEofMth
FROM Employees;

I linked to this query from Excel with no problems.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[Spidy6123]

my next try would be to do it with sql.. which Im not so strong in..

works like a charm.. thank you kindly