Problem with query returning one record

[Aivars]

Hello,
I have a stored query like this called VAL_REZERVE:
SELECT "Currency revaluation reserve" AS ITEMNAME, ValRezerve() AS
AMOUNTINT_LVL;
ValRezerve() is VBA UDF. This query works fine when I run it in by
clicking in Navigation pane - it gives the Currency revaluation
reserve and amount calculated by the UDF

But when I try to use it like: select * from VAL_REZERVE Access gives
an error saying that query input must contain at least one table or
query.

I am using ACCESS 2007.

Is it supposed to be that a query returning one record cannot be
called as I am trying to do?

Thanks
Aivars

 

[Allen Browne]

Correct: JET is not able to handle a query based on a query that has no
source table.

(IIRC, JET 3.x didn't handle the the first level query.)

 

['69 Camaro]

Hi Aivars.

Is it supposed to be that a query returning one record cannot be
called as I am trying to do?

Your VAL_REZERVE query doesn't return _any_ records that are stored in a
table. It displays a value returned from a user-defined function. The FROM
clause requires either at least one table or at least one query that uses a
table in its FROM clause. Your VAL_REZERVE query doesn't qualify as a valid
data source for Jet or ACE FROM clauses.

I'd suggest writing the ValRezerve( ) function name within any query that
you want to display its returned value.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
Blogs: www.DataDevilDog.BlogSpot.com, www.DatabaseTips.BlogSpot.com
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.

 

[Aivars]

Thanks, Allen and '69 Camaro,
I understand it now.

I need to think of work-around since I thought that i will be able to
UNION (append) this single line to another query which are returning
records.

I am new to Access, but have some knowledge of Excel and VBA, so my
Access questions are from a noobee.

Thanks again,

Aivars

 

['69 Camaro]

Hi Aivars.

I need to think of work-around since I thought that i will be able to
UNION (append) this single line to another query which are returning
records.

Not unless you store the calculated value in a table first, and storing
calculated values isn't usually advised, because the values the calculations
depend upon can change and then the calculated value would be wrong until
it's updated, but there's no guaranteed way to update it without a trigger,
which Jet and ACE don't support.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
Blogs: www.DataDevilDog.BlogSpot.com, www.DatabaseTips.BlogSpot.com
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.