table name from list box

J

javablood

I am trying to use the table name chosen from a list box in an SQL. I know I
am getting the table name from the list box because I can open the table from
the code but I keep getting errors (Form clause, JOIN operation) when trying
to run the SQL. Any ideas? TIA

SQL = "SELECT [Forms]![Generate Weather Data]![Me.tablename].[Calendar
Date], " & _
"Round(Avg([Forms]![Generate Weather Data]![Me.tablename].WS),2) AS
AvgOfWS, " & _
"Round(Avg([Forms]![Generate Weather Data]![Me.tablename].WD),0) AS
AvgOfWD, " & _
"Round(Avg([Forms]![Generate Weather Data]![Me.tablename].sigma),2) AS
AvgOfsigma, " & _
"Round(Avg([Forms]![Generate Weather Data]![Me.tablename].ws_max),2) AS
AvgOfws_max, " & _
"Round(Avg([Forms]![Generate Weather Data]![Me.tablename].temp_c),2) " & _
"AS AvgOftemp_c, Round(Avg([Forms]![Generate Weather
Data]![Me.tablename].bar_press),0) " & _
"AS AvgOfbar_press, Round(Sum([Forms]![Generate Weather
Data]![Me.tablename].rain),2) " & _
"AS SumOfrain INTO [Temp review data] FROM [Forms]![Generate Weather
Data]![Me.tablename] " & _
"GROUP BY [Forms]![Generate Weather Data]![Me.tablename].[Calendar Date]
" & _
"HAVING ((([Forms]![Generate Weather Data]![Me.tablename].[Calendar
Date]) " & _
"Between [Forms]![Generate Weather Data]![StartDate] " & _
"And [Forms]![Generate Weather Data]![EndDate])) " & _
"ORDER BY [Forms]![Generate Weather Data]![Me.tablename].[Calendar Date];"
 
A

Allen Browne

Concatenate the name from the list box into the string:
SQL ="SELECT ... FROM [" & [Forms]![Generate Weather
Data]![Me.tablename] & "] GROUP BY ...

The square brackets are needed if the table name contains non-standard
characters (e.g. a space.)
 
J

javablood

Allen,

I tried that (only in the From clause correct?) but now I get an error "...
cannot find field Me.tablename ireferred to in your expression".
--
javablood


Allen Browne said:
Concatenate the name from the list box into the string:
SQL ="SELECT ... FROM [" & [Forms]![Generate Weather
Data]![Me.tablename] & "] GROUP BY ...

The square brackets are needed if the table name contains non-standard
characters (e.g. a space.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

javablood said:
I am trying to use the table name chosen from a list box in an SQL. I know
I
am getting the table name from the list box because I can open the table
from
the code but I keep getting errors (Form clause, JOIN operation) when
trying
to run the SQL. Any ideas? TIA

SQL = "SELECT [Forms]![Generate Weather Data]![Me.tablename].[Calendar
Date], " & _
"Round(Avg([Forms]![Generate Weather Data]![Me.tablename].WS),2) AS
AvgOfWS, " & _
"Round(Avg([Forms]![Generate Weather Data]![Me.tablename].WD),0) AS
AvgOfWD, " & _
"Round(Avg([Forms]![Generate Weather Data]![Me.tablename].sigma),2) AS
AvgOfsigma, " & _
"Round(Avg([Forms]![Generate Weather Data]![Me.tablename].ws_max),2) AS
AvgOfws_max, " & _
"Round(Avg([Forms]![Generate Weather Data]![Me.tablename].temp_c),2) "
& _
"AS AvgOftemp_c, Round(Avg([Forms]![Generate Weather
Data]![Me.tablename].bar_press),0) " & _
"AS AvgOfbar_press, Round(Sum([Forms]![Generate Weather
Data]![Me.tablename].rain),2) " & _
"AS SumOfrain INTO [Temp review data] FROM [Forms]![Generate Weather
Data]![Me.tablename] " & _
"GROUP BY [Forms]![Generate Weather Data]![Me.tablename].[Calendar
Date]
" & _
"HAVING ((([Forms]![Generate Weather Data]![Me.tablename].[Calendar
Date]) " & _
"Between [Forms]![Generate Weather Data]![StartDate] " & _
"And [Forms]![Generate Weather Data]![EndDate])) " & _
"ORDER BY [Forms]![Generate Weather Data]![Me.tablename].[Calendar
Date];"
Click to expand...
Click to expand...
 
A

Allen Browne

Add the line:
Debug.Print SQL
after that large line of yours.

When it fails, open the Immediate Window (Ctrl+G) and see what came out.
Does it look right? Does it work as a query?

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

javablood said:
Allen,

I tried that (only in the From clause correct?) but now I get an error
"...
cannot find field Me.tablename ireferred to in your expression".
--
javablood


Allen Browne said:
Concatenate the name from the list box into the string:
SQL ="SELECT ... FROM [" & [Forms]![Generate Weather
Data]![Me.tablename] & "] GROUP BY ...

The square brackets are needed if the table name contains non-standard
characters (e.g. a space.)

javablood said:
I am trying to use the table name chosen from a list box in an SQL. I
know
I
am getting the table name from the list box because I can open the
table
from
the code but I keep getting errors (Form clause, JOIN operation) when
trying
to run the SQL. Any ideas? TIA

SQL = "SELECT [Forms]![Generate Weather Data]![Me.tablename].[Calendar
Date], " & _
"Round(Avg([Forms]![Generate Weather Data]![Me.tablename].WS),2) AS
AvgOfWS, " & _
"Round(Avg([Forms]![Generate Weather Data]![Me.tablename].WD),0) AS
AvgOfWD, " & _
"Round(Avg([Forms]![Generate Weather Data]![Me.tablename].sigma),2)
AS
AvgOfsigma, " & _
"Round(Avg([Forms]![Generate Weather Data]![Me.tablename].ws_max),2)
AS
AvgOfws_max, " & _
"Round(Avg([Forms]![Generate Weather Data]![Me.tablename].temp_c),2)
"
& _
"AS AvgOftemp_c, Round(Avg([Forms]![Generate Weather
Data]![Me.tablename].bar_press),0) " & _
"AS AvgOfbar_press, Round(Sum([Forms]![Generate Weather
Data]![Me.tablename].rain),2) " & _
"AS SumOfrain INTO [Temp review data] FROM [Forms]![Generate Weather
Data]![Me.tablename] " & _
"GROUP BY [Forms]![Generate Weather Data]![Me.tablename].[Calendar
Date]
" & _
"HAVING ((([Forms]![Generate Weather Data]![Me.tablename].[Calendar
Date]) " & _
"Between [Forms]![Generate Weather Data]![StartDate] " & _
"And [Forms]![Generate Weather Data]![EndDate])) " & _
"ORDER BY [Forms]![Generate Weather Data]![Me.tablename].[Calendar
Date];"
Click to expand...
Click to expand...
Click to expand...
 
J

javablood

Allen,

there is nothing in the Immediate Window. I am not sure why it thinks
Me.tablename is a field when it is in the form [Forms]![Generate Weather
Data]![Me.tablename]. is the syntax correct?
--
javablood


Allen Browne said:
Add the line:
Debug.Print SQL
after that large line of yours.

When it fails, open the Immediate Window (Ctrl+G) and see what came out.
Does it look right? Does it work as a query?

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

javablood said:
Allen,

I tried that (only in the From clause correct?) but now I get an error
"...
cannot find field Me.tablename ireferred to in your expression".
--
javablood


Allen Browne said:
Concatenate the name from the list box into the string:
SQL ="SELECT ... FROM [" & [Forms]![Generate Weather
Data]![Me.tablename] & "] GROUP BY ...

The square brackets are needed if the table name contains non-standard
characters (e.g. a space.)

I am trying to use the table name chosen from a list box in an SQL. I
know
I
am getting the table name from the list box because I can open the
table
from
the code but I keep getting errors (Form clause, JOIN operation) when
trying
to run the SQL. Any ideas? TIA

SQL = "SELECT [Forms]![Generate Weather Data]![Me.tablename].[Calendar
Date], " & _
"Round(Avg([Forms]![Generate Weather Data]![Me.tablename].WS),2) AS
AvgOfWS, " & _
"Round(Avg([Forms]![Generate Weather Data]![Me.tablename].WD),0) AS
AvgOfWD, " & _
"Round(Avg([Forms]![Generate Weather Data]![Me.tablename].sigma),2)
AS
AvgOfsigma, " & _
"Round(Avg([Forms]![Generate Weather Data]![Me.tablename].ws_max),2)
AS
AvgOfws_max, " & _
"Round(Avg([Forms]![Generate Weather Data]![Me.tablename].temp_c),2)
"
& _
"AS AvgOftemp_c, Round(Avg([Forms]![Generate Weather
Data]![Me.tablename].bar_press),0) " & _
"AS AvgOfbar_press, Round(Sum([Forms]![Generate Weather
Data]![Me.tablename].rain),2) " & _
"AS SumOfrain INTO [Temp review data] FROM [Forms]![Generate Weather
Data]![Me.tablename] " & _
"GROUP BY [Forms]![Generate Weather Data]![Me.tablename].[Calendar
Date]
" & _
"HAVING ((([Forms]![Generate Weather Data]![Me.tablename].[Calendar
Date]) " & _
"Between [Forms]![Generate Weather Data]![StartDate] " & _
"And [Forms]![Generate Weather Data]![EndDate])) " & _
"ORDER BY [Forms]![Generate Weather Data]![Me.tablename].[Calendar
Date];"
Click to expand...
Click to expand...
Click to expand...
 
J

javablood

Allen,

I did the following and it works:

SQL = "SELECT [" & tablename & "].[Calendar Date], Round(Avg([" & tablename
& "].WS),2) AS AvgOfWS, " & _
"Round(Avg([" & tablename & "].WD),0) AS AvgOfWD, Round(Avg([" &
tablename & "].sigma),2) AS AvgOfsigma, " & _
"Round(Avg([" & tablename & "].ws_max),2) AS AvgOfws_max, Round(Avg([" &
tablename & "].temp_c),2) " & _
"AS AvgOftemp_c, Round(Avg([" & tablename & "].bar_press),0) AS
AvgOfbar_press, " & _
"Round(Sum([" & tablename & "].rain),2) AS SumOfrain INTO [Temp review
data] FROM [" & tablename & "] " & _
"GROUP BY [" & tablename & "].[Calendar Date] HAVING ((([" & tablename &
"].[Calendar Date]) " & _
"Between [Forms]![Generate Weather Data]![StartDate] " & _
"And [Forms]![Generate Weather Data]![EndDate])) ORDER BY [" & tablename
& "].[Calendar Date];"

But I do not know why except maybe it was a syntax thing. Thank you for
your help.
--
javablood


Allen Browne said:
Add the line:
Debug.Print SQL
after that large line of yours.

When it fails, open the Immediate Window (Ctrl+G) and see what came out.
Does it look right? Does it work as a query?

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

javablood said:
Allen,

I tried that (only in the From clause correct?) but now I get an error
"...
cannot find field Me.tablename ireferred to in your expression".
--
javablood


Allen Browne said:
Concatenate the name from the list box into the string:
SQL ="SELECT ... FROM [" & [Forms]![Generate Weather
Data]![Me.tablename] & "] GROUP BY ...

The square brackets are needed if the table name contains non-standard
characters (e.g. a space.)

I am trying to use the table name chosen from a list box in an SQL. I
know
I
am getting the table name from the list box because I can open the
table
from
the code but I keep getting errors (Form clause, JOIN operation) when
trying
to run the SQL. Any ideas? TIA

SQL = "SELECT [Forms]![Generate Weather Data]![Me.tablename].[Calendar
Date], " & _
"Round(Avg([Forms]![Generate Weather Data]![Me.tablename].WS),2) AS
AvgOfWS, " & _
"Round(Avg([Forms]![Generate Weather Data]![Me.tablename].WD),0) AS
AvgOfWD, " & _
"Round(Avg([Forms]![Generate Weather Data]![Me.tablename].sigma),2)
AS
AvgOfsigma, " & _
"Round(Avg([Forms]![Generate Weather Data]![Me.tablename].ws_max),2)
AS
AvgOfws_max, " & _
"Round(Avg([Forms]![Generate Weather Data]![Me.tablename].temp_c),2)
"
& _
"AS AvgOftemp_c, Round(Avg([Forms]![Generate Weather
Data]![Me.tablename].bar_press),0) " & _
"AS AvgOfbar_press, Round(Sum([Forms]![Generate Weather
Data]![Me.tablename].rain),2) " & _
"AS SumOfrain INTO [Temp review data] FROM [Forms]![Generate Weather
Data]![Me.tablename] " & _
"GROUP BY [Forms]![Generate Weather Data]![Me.tablename].[Calendar
Date]
" & _
"HAVING ((([Forms]![Generate Weather Data]![Me.tablename].[Calendar
Date]) " & _
"Between [Forms]![Generate Weather Data]![StartDate] " & _
"And [Forms]![Generate Weather Data]![EndDate])) " & _
"ORDER BY [Forms]![Generate Weather Data]![Me.tablename].[Calendar
Date];"
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Queries in VBA 6
Query from Combo Box for Report 1
Creating a query using multiselect list box and text boxes 20
How to select all months from a table to include in a report 3
Verifying table structure... 8
Can I link a list box from one from to another? 3
List box as filter 1
Table headers in a list box (Custom Report) 3

Top