Also Myrna,
I am working on trying to implement your example. I am definatly way newer
to ADO than you are....

Thanks,

"PSKelligan" wrote:

Thanks Jamie and Myrna for your responses.

I did not understand that about Jet -vs- Access. Very enlightening.

My function which follows is designed to generate a reporting period the
start date is on the 16th of each month and it ends on the 15th of the
follwing month. So... on each day I run the query, it will show the period
just past. For example if I run the query on August 12th 2004, the RepPeriod
wil be 16-June-2004 to 15-July-2004 but on Aug 16th the period will advance 1
month to 16-Jul-2004 to 15-August-2004. If there is a way to accomplish this
I would be very greatful for the help. Se function below:

Public Function ReportPeriod() As Variant

'Instantiate (define) all variables
Dim cDay As Integer 'Current day (sys clock).
Dim cMonth As Integer 'Current month (sys clock).
Dim cYear As Integer 'Current year (sys clock).
Dim sDay As Integer 'Reporting period start day of month
(literal).
Dim sMonth As Integer 'Reporting period start month (variant).
Dim eDay As Integer 'Reporting period end day of month (literal).
Dim eMonth As Integer 'Reporting period end month (variant).
Dim sDate, eDate As Date 'Start and end of current reporting period
(variant).
'Initialize all variables
cDay = Format(Date, "dd") 'Sets day of the current date to an
interger.
cMonth = Format(Date, "mm") 'Sets month of the current date to an
interger.
cYear = Format(Date, "yyyy") 'Sets year of the current date to an
interger.
sDay = "16" 'Day of month that reporting period
starts.
eDay = "15" 'Day of month that reporting period ends.
'Generate start date based on the system clock.
If cDay < sDay Then
sMonth = (cMonth - 2)
Else
sMonth = (cMonth - 1)
End If
sDate = (sMonth & "/" & sDay & "/" & cYear)
'Generate end date based on system clock.
If cDay < sDay Then
eMonth = (cMonth - 1)
Else
eMonth = (cMonth)
End If
eDate = (eMonth & "/" & eDay & "/" & cYear)
End Function


"Jamie Collins" wrote:

"PSKelligan" wrote ...

I am trying to retrieve records from 2 queries in an Access 2003 database to
Excel. The first one works fine but the 2nd is a parameter query and the
parameter is answered by a stored function. It defines a reporting period
start date based on the system clock. The function works in both access and
excel but when the query is run in the follwing code it returns an error
state the function is "undefined.

You need to understand that your .mdb file is a Jet database. MS
Access is not a database; rather it is an application development
environment. Certain settings and elements used by the MS Access app
are stored in Jet tables in the .mdb file e.g. forms, reports, UDFs,
etc but are not otherwise visible. Generally, the only elements
visible from outside the MS Access UI are the schema (tables, views,
stored procedures, etc), the data in the tables and security elements
(users, groups, etc).

The only functions that Jet can 'see' are the ones built in, being the
VBA5 functions (as distinct from methods). Jet has no knowledge of the
user defined functions in the .mdb nor any functions provided by the
MS Access UI, including those in the MS Access library (e.g. NZ,
EuroConvert, etc) and the VBA6 functions in Access2000 and above (e.g.
Replace and StrReverse, etc). Similarly, Jet cannot see Excel library
functions nor the UDF functions in an Excel workbook.

What does your function do? Post the code and someone may be able to
suggest a way of achieving the same with sql. You could then wrap in a
stored procedure and call the proc with parameters from Excel, using
code as posted by Myrna. Actually, the more efficient way may be to
call using e.g.

.Open("EXECUTE Prices_as_of '" & _
Format$(TheDate, "yyyy-mm-dd hh:mm:ss") & "';")

Jamie.

--