Prev: Using IIF with checkbox?
Next: Access 2007 this database has been opened read only
From: Jose Garcia Jose on 20 Sep 2009 11:30
Hi,

Using Microsoft Access 2003.

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
From: Steve Sanford limbim53 at yahoo dot on 20 Sep 2009 12:20
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
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


"bladu via AccessMonster.com" wrote:

> 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
>
> --
> Message posted via AccessMonster.com
> http://www.accessmonster.com/Uwe/Forums.aspx/access-forms/200909/1
>
>
From: bladu via AccessMonster.com on 20 Sep 2009 12:44
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

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-forms/200909/1

 | 
Pages: 1
Prev: Using IIF with checkbox?
Next: Access 2007 this database has been opened read only