syntax error in JOIN operation

  • Thread starter Thread starter javablood
  • Start date Start date
J

javablood

Hi,

I am not even sure if this can be done. I am trying to reference a table in
a query. The table is defined in a form. Here is the query I am working
with and I get the syntax error in JOIN operation.

SQL = "SELECT Me.tbotbl.Param_Name, " & _
"Me.tbotbl.N, Me.tbotbl.S, " & _
"[MK_Var(S)].[Var(S)], " & _

"IIf(=0,0,IIf(>0,(-1)/([Var(S)]^(1/2)),(+1)/([Var(S)]^(1/2)))) AS
Z INTO tblMK_Z " & _
"FROM [MK_Var(S)] INNER JOIN Me.tbotbl " & _
"ON ([MK_Var(S)].PARAM_NAME = Me.tbotbl.Param_Name) " & _
"AND ([MK_Var(S)].STATION_ID = Me.tbotbl.Station_ID);"

I have also tried substituting [Forms]![frmMK]![tbotbl] for Me.tbotbl but I
get the same error.

Any help is appreciated. Thanks,
 
A table name cannot be a parameter, so Forms!formName!ControlName cannot be
use to hold a table name.

Try:

" FROM [MK_Var(S)] INNER JOIN [" & Me.tbotbl & "] AS a ON
([MK_Var(S)].PARAM_NAME = a.Param_Name) AND ... "

where using an alias, here a, simplify further on any reference to a field
from that table.


Note that if many tables can be used at that place, probably it would have
been preferable to make just one larger table, with an extra field supplying
the information now hold by the fact that a record is in one of the many
possible tables (example, which YEAR, if you have on table for each
different year).


Vanderghast, Access MVP
 
wow! that worked! I do not know why. Was it the alias you referenced?
Where can I find more about aliases?

The table is created for a different set of data so a larger table would not
help. And I wanted the user to input the period during which the data were
generated, e.g., 2009Q3Q4. I then concanated that info with the beginning
name of the table to create tbotbl and use that in the query to make the
table for export into Excel for further evaluation.

Thanks again!

-
javablood


vanderghast said:
A table name cannot be a parameter, so Forms!formName!ControlName cannot be
use to hold a table name.

Try:

" FROM [MK_Var(S)] INNER JOIN [" & Me.tbotbl & "] AS a ON
([MK_Var(S)].PARAM_NAME = a.Param_Name) AND ... "

where using an alias, here a, simplify further on any reference to a field
from that table.


Note that if many tables can be used at that place, probably it would have
been preferable to make just one larger table, with an extra field supplying
the information now hold by the fact that a record is in one of the many
possible tables (example, which YEAR, if you have on table for each
different year).


Vanderghast, Access MVP


javablood said:
Hi,

I am not even sure if this can be done. I am trying to reference a table
in
a query. The table is defined in a form. Here is the query I am working
with and I get the syntax error in JOIN operation.

SQL = "SELECT Me.tbotbl.Param_Name, " & _
"Me.tbotbl.N, Me.tbotbl.S, " & _
"[MK_Var(S)].[Var(S)], " & _

"IIf(=0,0,IIf(>0,(-1)/([Var(S)]^(1/2)),(+1)/([Var(S)]^(1/2))))
AS
Z INTO tblMK_Z " & _
"FROM [MK_Var(S)] INNER JOIN Me.tbotbl " & _
"ON ([MK_Var(S)].PARAM_NAME = Me.tbotbl.Param_Name) " & _
"AND ([MK_Var(S)].STATION_ID = Me.tbotbl.Station_ID);"

I have also tried substituting [Forms]![frmMK]![tbotbl] for Me.tbotbl but
I
get the same error.

Any help is appreciated. Thanks,
Click to expand...
Click to expand...
 
An alias is another name. Here, instead of having to 'insert' multiple times
the ..." & Me.tbotbl & " , the SQL statement add another name for it
and then only have to use that name in the same SQL statement:

"SELECT ... FROM veryLongNameToBeTypeAndProneToTypo AS yo WHERE
yo.fieldName > 44 "

Here, the alias is yo and replace the table name (for obvious reason).


As alias can also be added to expressions:

SELECT unitPrice * quantity AS totalPrice FORM ...


here, using totalPrice.


Sometimes, an alias is used to bring the same table twice (with two
different 'working' names, in that SQL statement). As example, if you
bring twice the same table, in a query, the query designer automatically
append an _1 to the name of one of them.



Vanderghast, Access MVP



javablood said:
wow! that worked! I do not know why. Was it the alias you referenced?
Where can I find more about aliases?

The table is created for a different set of data so a larger table would
not
help. And I wanted the user to input the period during which the data
were
generated, e.g., 2009Q3Q4. I then concanated that info with the beginning
name of the table to create tbotbl and use that in the query to make the
table for export into Excel for further evaluation.

Thanks again!

-
javablood


vanderghast said:
A table name cannot be a parameter, so Forms!formName!ControlName cannot
be
use to hold a table name.

Try:

" FROM [MK_Var(S)] INNER JOIN [" & Me.tbotbl & "] AS a ON
([MK_Var(S)].PARAM_NAME = a.Param_Name) AND ... "

where using an alias, here a, simplify further on any reference to a
field
from that table.


Note that if many tables can be used at that place, probably it would
have
been preferable to make just one larger table, with an extra field
supplying
the information now hold by the fact that a record is in one of the many
possible tables (example, which YEAR, if you have on table for each
different year).


Vanderghast, Access MVP


javablood said:
Hi,

I am not even sure if this can be done. I am trying to reference a
table
in
a query. The table is defined in a form. Here is the query I am
working
with and I get the syntax error in JOIN operation.

SQL = "SELECT Me.tbotbl.Param_Name, " & _
"Me.tbotbl.N, Me.tbotbl.S, " & _
"[MK_Var(S)].[Var(S)], " & _

"IIf(=0,0,IIf(>0,(-1)/([Var(S)]^(1/2)),(+1)/([Var(S)]^(1/2))))
AS
Z INTO tblMK_Z " & _
"FROM [MK_Var(S)] INNER JOIN Me.tbotbl " & _
"ON ([MK_Var(S)].PARAM_NAME = Me.tbotbl.Param_Name) " & _
"AND ([MK_Var(S)].STATION_ID = Me.tbotbl.Station_ID);"

I have also tried substituting [Forms]![frmMK]![tbotbl] for Me.tbotbl
but
I
get the same error.

Any help is appreciated. Thanks,
Click to expand...
Click to expand...
Click to expand...
 
thanks
--
javablood


vanderghast said:
An alias is another name. Here, instead of having to 'insert' multiple times
the ..." & Me.tbotbl & " , the SQL statement add another name for it
and then only have to use that name in the same SQL statement:

"SELECT ... FROM veryLongNameToBeTypeAndProneToTypo AS yo WHERE
yo.fieldName > 44 "

Here, the alias is yo and replace the table name (for obvious reason).


As alias can also be added to expressions:

SELECT unitPrice * quantity AS totalPrice FORM ...


here, using totalPrice.


Sometimes, an alias is used to bring the same table twice (with two
different 'working' names, in that SQL statement). As example, if you
bring twice the same table, in a query, the query designer automatically
append an _1 to the name of one of them.



Vanderghast, Access MVP



javablood said:
wow! that worked! I do not know why. Was it the alias you referenced?
Where can I find more about aliases?

The table is created for a different set of data so a larger table would
not
help. And I wanted the user to input the period during which the data
were
generated, e.g., 2009Q3Q4. I then concanated that info with the beginning
name of the table to create tbotbl and use that in the query to make the
table for export into Excel for further evaluation.

Thanks again!

-
javablood


vanderghast said:
A table name cannot be a parameter, so Forms!formName!ControlName cannot
be
use to hold a table name.

Try:

" FROM [MK_Var(S)] INNER JOIN [" & Me.tbotbl & "] AS a ON
([MK_Var(S)].PARAM_NAME = a.Param_Name) AND ... "

where using an alias, here a, simplify further on any reference to a
field
from that table.


Note that if many tables can be used at that place, probably it would
have
been preferable to make just one larger table, with an extra field
supplying
the information now hold by the fact that a record is in one of the many
possible tables (example, which YEAR, if you have on table for each
different year).


Vanderghast, Access MVP


Hi,

I am not even sure if this can be done. I am trying to reference a
table
in
a query. The table is defined in a form. Here is the query I am
working
with and I get the syntax error in JOIN operation.

SQL = "SELECT Me.tbotbl.Param_Name, " & _
"Me.tbotbl.N, Me.tbotbl.S, " & _
"[MK_Var(S)].[Var(S)], " & _

"IIf(=0,0,IIf(>0,(-1)/([Var(S)]^(1/2)),(+1)/([Var(S)]^(1/2))))
AS
Z INTO tblMK_Z " & _
"FROM [MK_Var(S)] INNER JOIN Me.tbotbl " & _
"ON ([MK_Var(S)].PARAM_NAME = Me.tbotbl.Param_Name) " & _
"AND ([MK_Var(S)].STATION_ID = Me.tbotbl.Station_ID);"

I have also tried substituting [Forms]![frmMK]![tbotbl] for Me.tbotbl
but
I
get the same error.

Any help is appreciated. Thanks,
Click to expand...
Click to expand...
Click to expand...
 
Back
Top