Create a function that is visible to a query

[MikeB]

Hi.
I'm trying to create a function to get the median value of a series
of observations. I've found an excellent reference on a MS website on
how to construct a query to obtain the median value, but I'm having a
bit of difficulty getting the query "visible" to access. Whenever I put
the function into the design grid it fails "undefined function 'Median'
in expression."

I created the function by clicking Insert -> Module. Access then
provided me with a Module1 (how do I rename it?) and I pasted the text
for the function into the module.

I tried running it and initially I had some difficulties with
mismatching parameters (I passed a string instead of a table name).

Once I got the errors ironed out, there are now other objects in my
Visual Basic Object list under a tree of "Utility".

Basically, I have the function Median coded as it is demonstrated in
http://support.microsoft.com/?id=210581.

I then have a query to extract a relevant list of records from the
table:

SELECT DateValue(Fix([EndDate])) AS [End], Fix(Date())-Fix([EndDate])
AS Age
FROM Listing
WHERE (((Listing.Status)="Pending Verification"))
ORDER BY DateValue(Fix([EndDate]));


Then I have the query that fails but is supposed to obtain the median
value:

SELECT Median([Listings Pending Verification Age List],[Age]) AS mEnd
FROM [Listings Pending Verification Age List];

I'd appreciate any help someone can give me.

Thanks.

 

[MikeB]

Thanks! That helped, although it turned out I had to lose the square
brackets entirely.

Now I get a table with multiple rows (one for each row in the table)
each row having the same value for the median age. Is there a way I
can write the query so that a single row (a single value) is returned?

Mike

Hi Mike,

The write-up of the Median function in the article you posted wants the
parameters as strings... you will need to call it like this if you are using
the same function:

Median("[Listings Pending Verification Age List]","[Age]")

Damian.

Hi.
I'm trying to create a function to get the median value of a series
of observations. I've found an excellent reference on a MS website on
how to construct a query to obtain the median value, but I'm having a
bit of difficulty getting the query "visible" to access. Whenever I put
the function into the design grid it fails "undefined function 'Median'
in expression."

I created the function by clicking Insert -> Module. Access then
provided me with a Module1 (how do I rename it?) and I pasted the text
for the function into the module.

I tried running it and initially I had some difficulties with
mismatching parameters (I passed a string instead of a table name).

Once I got the errors ironed out, there are now other objects in my
Visual Basic Object list under a tree of "Utility".

Basically, I have the function Median coded as it is demonstrated in
http://support.microsoft.com/?id=210581.

I then have a query to extract a relevant list of records from the
table:

SELECT DateValue(Fix([EndDate])) AS [End], Fix(Date())-Fix([EndDate])
AS Age
FROM Listing
WHERE (((Listing.Status)="Pending Verification"))
ORDER BY DateValue(Fix([EndDate]));


Then I have the query that fails but is supposed to obtain the median
value:

SELECT Median([Listings Pending Verification Age List],[Age]) AS mEnd
FROM [Listings Pending Verification Age List];

I'd appreciate any help someone can give me.

Thanks.

 

[Damian S]

Hi Mike,

Any time you call a function in a query, you will receive one result for
every row returned by the query. If you want a single result returned, use a
query that returns a single row, or simply remove the from line (since you
aren't returning any other fields from the table in your example):

select Median("[Listings Pending Verification Age List]","[Age]") as mEnd

Hope this helps.

Damian.

Thanks! That helped, although it turned out I had to lose the square
brackets entirely.

Now I get a table with multiple rows (one for each row in the table)
each row having the same value for the median age. Is there a way I
can write the query so that a single row (a single value) is returned?

Mike

Hi Mike,

The write-up of the Median function in the article you posted wants the
parameters as strings... you will need to call it like this if you are using
the same function:

Median("[Listings Pending Verification Age List]","[Age]")

Damian.

Hi.
I'm trying to create a function to get the median value of a series
of observations. I've found an excellent reference on a MS website on
how to construct a query to obtain the median value, but I'm having a
bit of difficulty getting the query "visible" to access. Whenever I put
the function into the design grid it fails "undefined function 'Median'
in expression."

I created the function by clicking Insert -> Module. Access then
provided me with a Module1 (how do I rename it?) and I pasted the text
for the function into the module.

I tried running it and initially I had some difficulties with
mismatching parameters (I passed a string instead of a table name).

Once I got the errors ironed out, there are now other objects in my
Visual Basic Object list under a tree of "Utility".

Basically, I have the function Median coded as it is demonstrated in
http://support.microsoft.com/?id=210581.

I then have a query to extract a relevant list of records from the
table:

SELECT DateValue(Fix([EndDate])) AS [End], Fix(Date())-Fix([EndDate])
AS Age
FROM Listing
WHERE (((Listing.Status)="Pending Verification"))
ORDER BY DateValue(Fix([EndDate]));


Then I have the query that fails but is supposed to obtain the median
value:

SELECT Median([Listings Pending Verification Age List],[Age]) AS mEnd
FROM [Listings Pending Verification Age List];

I'd appreciate any help someone can give me.

Thanks.

 

[James A. Fortune]

Hi.
I'm trying to create a function to get the median value of a series
of observations. I've found an excellent reference on a MS website on
how to construct a query to obtain the median value, but I'm having a
bit of difficulty getting the query "visible" to access. Whenever I put
the function into the design grid it fails "undefined function 'Median'
in expression."

I created the function by clicking Insert -> Module. Access then
provided me with a Module1 (how do I rename it?) and I pasted the text
for the function into the module.

I tried running it and initially I had some difficulties with
mismatching parameters (I passed a string instead of a table name).

Once I got the errors ironed out, there are now other objects in my
Visual Basic Object list under a tree of "Utility".

Basically, I have the function Median coded as it is demonstrated in
http://support.microsoft.com/?id=210581.

I then have a query to extract a relevant list of records from the
table:

SELECT DateValue(Fix([EndDate])) AS [End], Fix(Date())-Fix([EndDate])
AS Age
FROM Listing
WHERE (((Listing.Status)="Pending Verification"))
ORDER BY DateValue(Fix([EndDate]));


Then I have the query that fails but is supposed to obtain the median
value:

SELECT Median([Listings Pending Verification Age List],[Age]) AS mEnd
FROM [Listings Pending Verification Age List];

I'd appreciate any help someone can give me.

Thanks.

In:

http://groups.google.com/group/comp.databases.ms-access/msg/7fb6eb508f22fa9d

Titled "Median of GROUP BY values," I show a SQL method for obtaining
the medians of query groups that does not involve calling a user-defined
VBA function. Also, the links refer back to the non-grouped case.

James A. Fortune
(e-mail address removed)

 

[MikeB]

Thanks, I ended up placing the median query in a field in another,
related query that calculated the average and then I got the single
field I wanted.

Mike

Hi Mike,

Any time you call a function in a query, you will receive one result for
every row returned by the query. If you want a single result returned, use a
query that returns a single row, or simply remove the from line (since you
aren't returning any other fields from the table in your example):

select Median("[Listings Pending Verification Age List]","[Age]") as mEnd

Hope this helps.

Damian.

Thanks! That helped, although it turned out I had to lose the square
brackets entirely.

Now I get a table with multiple rows (one for each row in the table)
each row having the same value for the median age. Is there a way I
can write the query so that a single row (a single value) is returned?

Mike

Hi Mike,

The write-up of the Median function in the article you posted wants the
parameters as strings... you will need to call it like this if you are using
the same function:

Median("[Listings Pending Verification Age List]","[Age]")

Damian.


:

Hi.
I'm trying to create a function to get the median value of a series
of observations. I've found an excellent reference on a MS website on
how to construct a query to obtain the median value, but I'm having a
bit of difficulty getting the query "visible" to access. Whenever I put
the function into the design grid it fails "undefined function 'Median'
in expression."

I created the function by clicking Insert -> Module. Access then
provided me with a Module1 (how do I rename it?) and I pasted the text
for the function into the module.

I tried running it and initially I had some difficulties with
mismatching parameters (I passed a string instead of a table name).

Once I got the errors ironed out, there are now other objects in my
Visual Basic Object list under a tree of "Utility".

Basically, I have the function Median coded as it is demonstrated in
http://support.microsoft.com/?id=210581.

I then have a query to extract a relevant list of records from the
table:

SELECT DateValue(Fix([EndDate])) AS [End], Fix(Date())-Fix([EndDate])
AS Age
FROM Listing
WHERE (((Listing.Status)="Pending Verification"))
ORDER BY DateValue(Fix([EndDate]));


Then I have the query that fails but is supposed to obtain the median
value:

SELECT Median([Listings Pending Verification Age List],[Age]) AS mEnd
FROM [Listings Pending Verification Age List];

I'd appreciate any help someone can give me.

Thanks.

 

[MikeB]

Hi James,
I actually found the article you referred to prior to my first post,
but the SQL is a tad too much for me, I'm at a very basic level of SQL.

Thanks anyway.

Mike

Hi.
I'm trying to create a function to get the median value of a series
of observations. I've found an excellent reference on a MS website on
how to construct a query to obtain the median value, but I'm having a
bit of difficulty getting the query "visible" to access. Whenever I put
the function into the design grid it fails "undefined function 'Median'
in expression."

I created the function by clicking Insert -> Module. Access then
provided me with a Module1 (how do I rename it?) and I pasted the text
for the function into the module.

I tried running it and initially I had some difficulties with
mismatching parameters (I passed a string instead of a table name).

Once I got the errors ironed out, there are now other objects in my
Visual Basic Object list under a tree of "Utility".

Basically, I have the function Median coded as it is demonstrated in
http://support.microsoft.com/?id=210581.

I then have a query to extract a relevant list of records from the
table:

SELECT DateValue(Fix([EndDate])) AS [End], Fix(Date())-Fix([EndDate])
AS Age
FROM Listing
WHERE (((Listing.Status)="Pending Verification"))
ORDER BY DateValue(Fix([EndDate]));


Then I have the query that fails but is supposed to obtain the median
value:

SELECT Median([Listings Pending Verification Age List],[Age]) AS mEnd
FROM [Listings Pending Verification Age List];

I'd appreciate any help someone can give me.

Thanks.

In:

http://groups.google.com/group/comp.databases.ms-access/msg/7fb6eb508f22fa9d

Titled "Median of GROUP BY values," I show a SQL method for obtaining
the medians of query groups that does not involve calling a user-defined
VBA function. Also, the links refer back to the non-grouped case.

James A. Fortune
(e-mail address removed)

 

[James A. Fortune]

Hi James,
I actually found the article you referred to prior to my first post,
but the SQL is a tad too much for me, I'm at a very basic level of SQL.

Thanks anyway.

Mike

I understand. I had to pull a rabbit out of a hat with the SQL in order
to get a solution that didn't require a user-defined function (UDF).
I'm glad you got a UDF to work.

James A. Fortune
(e-mail address removed)