If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
syntax error in JOIN operation
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 |
#2
|
|||
|
|||
syntax error in JOIN operation
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" wrote in message news 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 |
#3
|
|||
|
|||
syntax error in JOIN operation
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" wrote in message news 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 |
#4
|
|||
|
|||
syntax error in JOIN operation
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" wrote in message ... 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" wrote in message news 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 |
#5
|
|||
|
|||
syntax error in JOIN operation
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" wrote in message ... 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" wrote in message news 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 |
Thread Tools | |
Display Modes | |
|
|