The expression you entered has a function name that Microsoft Office Access cant

[bladu via AccessMonster.com]

Hi,

I have a form, which has the following two field camps:
- Number of plane
- Sheet of plane

The aim of the form is to show how much revisions are with these two
information fields. For this reason , when you click a button to show the
results, it executes a query which gives the results. This query is executed
via Macro.

The query is the following, and runs well:

SELECT T1.Revision, T1.Fecha_ult_revision
FROM Control AS T1 LEFT JOIN [SELECT TOP 1 *
FROM Control
WHERE (((Control.Numero)=[Num]) AND ((Control.Hoja)=[hoj]))
ORDER BY Control.Revision DESC]. AS RecordToExclude ON (T1.Hoja =
RecordToExclude.Hoja) AND (T1.Fecha_ult_revision = RecordToExclude.
Fecha_ult_revision) AND (T1.Numero = RecordToExclude.Numero)
WHERE (((T1.Numero)=[Num]) AND ((T1.Hoja)=[hoj]) AND ((RecordToExclude.Numero)
Is Null) AND ((RecordToExclude.Fecha_ult_revision) Is Null) AND (
(RecordToExclude.Hoja) Is Null))
ORDER BY T1.Revision;


The problem is that when I click the button of the form , with the data in
the filled in the field camps, Access shows me the following message error:

The expression you entered has a function name that Microsoft Office Access
can't find

and after that shows me the result properly.

I would be very grateful if anybody could tell how can I quit that error
message.

Thanks with anticipation

Regards

 

[Steve Sanford]

There is a left bracket "[" in front of the second "SELECT". Try changing it
to a left parenthesis.

Change from:

.......AS T1 LEFT JOIN [SELECT TOP ......

to

.......AS T1 LEFT JOIN (SELECT TOP ......

^^^

HTH

 

[bladu via AccessMonster.com]

Hi Steve

If I change the left bracket by a parenthesis , the query doesn`t run.

Note that the left parenthesis matches with the right parenthesis of ....
ORDER BY Control.Revision DESC]

Regards

 

[Steve Sanford]

I looked and looked and couldn't see that closing bracket..

So I tried making a table and running the query. Every time I try to execute
the query, I get the error "Syntax Error in FROM clause" and it highlights
the ")" after "[Num]" in the "WHERE" clause. (?????)

If I make a saved query from the "[SELECT.......]" and use it in the main
query to replace the "[SELECT.........DESC]", I don't get an error.


Since the query seems to be returning the expected results, maybe something
else is raising the error. After re-reading your first post again, it sounds
like there might be a control on the form that has a bad function name, not
the query.

Are there controls in the form with a function for the the control source?

 

[bladu via AccessMonster.com]

Hi Steve

The left bracket and the right bracket

SELECT T1.Revision, T1.Fecha_ult_revision
FROM Control AS T1 LEFT JOIN [SELECT TOP 1 *
FROM Control
WHERE (((Control.Numero)=[Num]) AND ((Control.Hoja)=[hoj]))
ORDER BY Control.Revision DESC]. AS RecordToExclude ON (T1.Hoja =
RecordToExclude.Hoja) AND (T1.Fecha_ult_revision = RecordToExclude.
Fecha_ult_revision) AND (T1.Numero = RecordToExclude.Numero)
WHERE (((T1.Numero)=[Num]) AND ((T1.Hoja)=[hoj]) AND ((RecordToExclude.Numero)

Is Null) AND ((RecordToExclude.Fecha_ult_revision) Is Null) AND (
(RecordToExclude.Hoja) Is Null))
ORDER BY T1.Revision;

Yes there are controls (buttons, that clicking in it execute a macro, I think
that in the macro is the problem)

Regards

 

[Steve Sanford]

If you think it is the macro that is causing the error, looking at the SQL of
a query won't fix the problem.

You could make a copy of your mdb, then remove one line (starting from the
bottom) of the macro and run the macro. Keep removing one line at a time and
running the macro until the error goes away. That line will be the one you
need to fix in the "real" mdb.