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([S]=0,0,IIf([S]>0,([S]-1)/([Var(S)]^(1/2)),([S]+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,
-- 
javablood

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" <javablood@discussions.microsoft.com> wrote in message 
news:D8630E31-8C91-43A3-86B0-6C88BF0B694F@microsoft.com...
> 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([S]=0,0,IIf([S]>0,([S]-1)/([Var(S)]^(1/2)),([S]+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,
> -- 
> javablood 

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" wrote:

> 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" <javablood@discussions.microsoft.com> wrote in message 
> news:D8630E31-8C91-43A3-86B0-6C88BF0B694F@microsoft.com...
> > 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([S]=0,0,IIf([S]>0,([S]-1)/([Var(S)]^(1/2)),([S]+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,
> > -- 
> > javablood 
> 

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" <javablood@discussions.microsoft.com> wrote in message 
news:60F34E83-67D3-4501-A4AE-C4F34910625A@microsoft.com...
> 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" wrote:
>
>> 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" <javablood@discussions.microsoft.com> wrote in message
>> news:D8630E31-8C91-43A3-86B0-6C88BF0B694F@microsoft.com...
>> > 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([S]=0,0,IIf([S]>0,([S]-1)/([Var(S)]^(1/2)),([S]+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,
>> > -- 
>> > javablood
>> 

thanks
-- 
javablood


"vanderghast" wrote:

> 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" <javablood@discussions.microsoft.com> wrote in message 
> news:60F34E83-67D3-4501-A4AE-C4F34910625A@microsoft.com...
> > 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" wrote:
> >
> >> 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" <javablood@discussions.microsoft.com> wrote in message
> >> news:D8630E31-8C91-43A3-86B0-6C88BF0B694F@microsoft.com...
> >> > 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([S]=0,0,IIf([S]>0,([S]-1)/([Var(S)]^(1/2)),([S]+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,
> >> > -- 
> >> > javablood
> >> 
>