Update SQL through a form combo box

S

shiro

Hi All,
I have a SQL like below;

SELECT TOP 10 Format([Date],"mmm") AS [Month], Format([Date],"yyyy") AS
[Year], [DC Fan Information Centre].Model, Sum([DC Fan Information
Centre].Vibration) AS SumOfVibration INTO [The Worst 10 by Reject Item tbl]
FROM [DC Fan Information Centre]
GROUP BY Format([Date],"mmm"), Format([Date],"yyyy"), [DC Fan Information
Centre].Model
HAVING (((Format([Date],"mmm"))=[Forms]![fltr Reject Rank By Reject
Item]![cboMonth]) AND ((Format([Date],"yyyy"))=[Forms]![fltr Reject Rank By
Reject Item]![cboYear]))
ORDER BY Sum([DC Fan Information Centre].Vibration) DESC
WITH OWNERACCESS OPTION;

I want the field 'Vibration' can be replaced with one of another 10 fields
through
a combo box ( cbo box contains all of the fields name ) in form 'fltr
Reject Rank By Reject Item'
and then execute the SQL with a command button.Is it possible?

Hope somebody would like to advice.
 
J

John Spencer

Probably the best way to do this would be to build the SQL statement on the
fly.

And then use the button to build the SQL Statement and execute it.. The
following VERY ROUGH UNTESTED code snippet should get you started.

strSql = "SELECT TOP 10 " & vbCrLf & _
" Format([Date],""mmm"") AS [Month] " & vbCrLf & _
" , Format([Date],""yyyy"") AS [Year] " & vbCrLf & _
" , Model " & vbCrLf & _
" , Sum([" & Me.cboFieldName & _
"]) AS [SumOf" & Me.cboFieldName & "]" & vbCrLf & _
" INTO [The Worst 10 by Reject Item tbl] " & vbCrLf & _
" FROM [DC Fan Information Centre] " & vbCrLf & _
" WHERE Format([Date],""mmm"")=""" & Me.[cboMonth]) & """" & vbCrLf & _
" AND Format([Date],""yyyy"")=""" & me.[cboYear] & """ " & vbCrLf & _
" GROUP BY Format([Date],""mmm"") " & vbCrLf & _
" , Format([Date],""yyyy"") " & vbCrLf & _
" , Model " & vbCrLf & _
" ORDER BY Sum([Vibration]) DESC " & vbCrLf & _
" WITH OWNERACCESS OPTION;"

Currentdb().Execute strSQL, dbFailOnError

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
S

shiro

I had modified the code like below

Private Sub CmdOK_Click()
If Frame7 = 1 Then
Me.Visible = False
DoCmd.SetWarnings False
DoCmd.RunSQL ""

strSql = "SELECT TOP 10 " & vbCrLf & _
" Format([Date],""mmm"") AS [Month] " & vbCrLf & _
" , Format([Date],""yyyy"") AS [Year] " & vbCrLf & _
" , Model " & vbCrLf & _
" , Sum([" & Me.CboRejectItem & _
"]) AS [SumOf" & Me.CboRejectItem & "]" & vbCrLf & _
" INTO [The Worst 10 by Reject Item tbl] " & vbCrLf & _
" FROM [DC Fan Information Centre] " & vbCrLf & _
" WHERE Format([Date],""mmm"")=""" & Me.[cboMonth] & """" & vbCrLf & _
" AND Format([Date],""yyyy"")=""" & Me.[cboYear] & """ " & vbCrLf & _
" GROUP BY Format([Date],""mmm"") " & vbCrLf & _
" , Format([Date],""yyyy"") " & vbCrLf & _
" , Model " & vbCrLf & _
" ORDER BY Sum([Vibration]) DESC " & vbCrLf & _
" WITH OWNERACCESS OPTION;"

CurrentDb().Execute strSql, dbFailOnError

DoCmd.SetWarnings True
DoCmd.OpenTable " The Worst 10 by Reject Item tbl", acNormal

But,VB told me
Compile Error:
Variable not defined !

What's wrong John?


John Spencer said:
Probably the best way to do this would be to build the SQL statement on the
fly.

And then use the button to build the SQL Statement and execute it.. The
following VERY ROUGH UNTESTED code snippet should get you started.

strSql = "SELECT TOP 10 " & vbCrLf & _
" Format([Date],""mmm"") AS [Month] " & vbCrLf & _
" , Format([Date],""yyyy"") AS [Year] " & vbCrLf & _
" , Model " & vbCrLf & _
" , Sum([" & Me.cboFieldName & _
"]) AS [SumOf" & Me.cboFieldName & "]" & vbCrLf & _
" INTO [The Worst 10 by Reject Item tbl] " & vbCrLf & _
" FROM [DC Fan Information Centre] " & vbCrLf & _
" WHERE Format([Date],""mmm"")=""" & Me.[cboMonth]) & """" & vbCrLf & _
" AND Format([Date],""yyyy"")=""" & me.[cboYear] & """ " & vbCrLf & _
" GROUP BY Format([Date],""mmm"") " & vbCrLf & _
" , Format([Date],""yyyy"") " & vbCrLf & _
" , Model " & vbCrLf & _
" ORDER BY Sum([Vibration]) DESC " & vbCrLf & _
" WITH OWNERACCESS OPTION;"

Currentdb().Execute strSQL, dbFailOnError

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
.

shiro said:
Hi All,
I have a SQL like below;

SELECT TOP 10 Format([Date],"mmm") AS [Month], Format([Date],"yyyy") AS
[Year], [DC Fan Information Centre].Model, Sum([DC Fan Information
Centre].Vibration) AS SumOfVibration INTO [The Worst 10 by Reject Item
tbl]
FROM [DC Fan Information Centre]
GROUP BY Format([Date],"mmm"), Format([Date],"yyyy"), [DC Fan Information
Centre].Model
HAVING (((Format([Date],"mmm"))=[Forms]![fltr Reject Rank By Reject
Item]![cboMonth]) AND ((Format([Date],"yyyy"))=[Forms]![fltr Reject Rank
By
Reject Item]![cboYear]))
ORDER BY Sum([DC Fan Information Centre].Vibration) DESC
WITH OWNERACCESS OPTION;

I want the field 'Vibration' can be replaced with one of another 10 fields
through
a combo box ( cbo box contains all of the fields name ) in form 'fltr
Reject Rank By Reject Item'
and then execute the SQL with a command button.Is it possible?

Hope somebody would like to advice.
Click to expand...
Click to expand...
 
J

John Spencer

If Frame7 = 1 Then
Me.Visible = False

'Comment out the line for now
'DoCmd.SetWarnings False

'Comment out this line, it does nothing DoCmd.RunSQL ""

Dim strSQL as String 'Always declare your variables.


strSql = "SELECT TOP 10 " & vbCrLf & _
" Format([Date],""mmm"") AS [Month] " & vbCrLf & _
" , Format([Date],""yyyy"") AS [Year] " & vbCrLf & _
" , Model " & vbCrLf & _
" , Sum([" & Me.CboRejectItem & _
"]) AS [SumOf" & Me.CboRejectItem & "]" & vbCrLf & _
" INTO [The Worst 10 by Reject Item tbl] " & vbCrLf & _
" FROM [DC Fan Information Centre] " & vbCrLf & _
" WHERE Format([Date],""mmm"")=""" & Me.[cboMonth] & """" & vbCrLf & _
" AND Format([Date],""yyyy"")=""" & Me.[cboYear] & """ " & vbCrLf & _
" GROUP BY Format([Date],""mmm"") " & vbCrLf & _
" , Format([Date],""yyyy"") " & vbCrLf & _
" , Model " & vbCrLf & _
" ORDER BY Sum([Vibration]) DESC " & vbCrLf & _
" WITH OWNERACCESS OPTION;"

'Add the following to debug things.
Debug.Print StrSQL: STOP
'Check the sql string and see if it is valid.

CurrentDb().Execute strSql, dbFailOnError

DoCmd.SetWarnings True
DoCmd.OpenTable " The Worst 10 by Reject Item tbl", acNormal

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

I had modified the code like below

Private Sub CmdOK_Click()
If Frame7 = 1 Then
Me.Visible = False
DoCmd.SetWarnings False
DoCmd.RunSQL ""

strSql = "SELECT TOP 10 " & vbCrLf & _
" Format([Date],""mmm"") AS [Month] " & vbCrLf & _
" , Format([Date],""yyyy"") AS [Year] " & vbCrLf & _
" , Model " & vbCrLf & _
" , Sum([" & Me.CboRejectItem & _
"]) AS [SumOf" & Me.CboRejectItem & "]" & vbCrLf & _
" INTO [The Worst 10 by Reject Item tbl] " & vbCrLf & _
" FROM [DC Fan Information Centre] " & vbCrLf & _
" WHERE Format([Date],""mmm"")=""" & Me.[cboMonth] & """" & vbCrLf & _
" AND Format([Date],""yyyy"")=""" & Me.[cboYear] & """ " & vbCrLf & _
" GROUP BY Format([Date],""mmm"") " & vbCrLf & _
" , Format([Date],""yyyy"") " & vbCrLf & _
" , Model " & vbCrLf & _
" ORDER BY Sum([Vibration]) DESC " & vbCrLf & _
" WITH OWNERACCESS OPTION;"

CurrentDb().Execute strSql, dbFailOnError

DoCmd.SetWarnings True
DoCmd.OpenTable " The Worst 10 by Reject Item tbl", acNormal

But,VB told me
Compile Error:
Variable not defined !

What's wrong John?


John Spencer said:
Probably the best way to do this would be to build the SQL statement on the
fly.

And then use the button to build the SQL Statement and execute it.. The
following VERY ROUGH UNTESTED code snippet should get you started.

strSql = "SELECT TOP 10 " & vbCrLf & _
" Format([Date],""mmm"") AS [Month] " & vbCrLf & _
" , Format([Date],""yyyy"") AS [Year] " & vbCrLf & _
" , Model " & vbCrLf & _
" , Sum([" & Me.cboFieldName & _
"]) AS [SumOf" & Me.cboFieldName & "]" & vbCrLf & _
" INTO [The Worst 10 by Reject Item tbl] " & vbCrLf & _
" FROM [DC Fan Information Centre] " & vbCrLf & _
" WHERE Format([Date],""mmm"")=""" & Me.[cboMonth]) & """" & vbCrLf & _
" AND Format([Date],""yyyy"")=""" & me.[cboYear] & """ " & vbCrLf & _
" GROUP BY Format([Date],""mmm"") " & vbCrLf & _
" , Format([Date],""yyyy"") " & vbCrLf & _
" , Model " & vbCrLf & _
" ORDER BY Sum([Vibration]) DESC " & vbCrLf & _
" WITH OWNERACCESS OPTION;"

Currentdb().Execute strSQL, dbFailOnError

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
.

shiro said:
Hi All,
I have a SQL like below;

SELECT TOP 10 Format([Date],"mmm") AS [Month], Format([Date],"yyyy") AS
[Year], [DC Fan Information Centre].Model, Sum([DC Fan Information
Centre].Vibration) AS SumOfVibration INTO [The Worst 10 by Reject Item
tbl]
FROM [DC Fan Information Centre]
GROUP BY Format([Date],"mmm"), Format([Date],"yyyy"), [DC Fan Information
Centre].Model
HAVING (((Format([Date],"mmm"))=[Forms]![fltr Reject Rank By Reject
Item]![cboMonth]) AND ((Format([Date],"yyyy"))=[Forms]![fltr Reject Rank
By
Reject Item]![cboYear]))
ORDER BY Sum([DC Fan Information Centre].Vibration) DESC
WITH OWNERACCESS OPTION;

I want the field 'Vibration' can be replaced with one of another 10 fields
through
a combo box ( cbo box contains all of the fields name ) in form 'fltr
Reject Rank By Reject Item'
and then execute the SQL with a command button.Is it possible?

Hope somebody would like to advice.
Click to expand...
Click to expand...
Click to expand...
 
S

shiro

The code works fine untill debugging line.But now access
sent:
Run time Error 3061
Too few parameters.Expected 1.

What should I add in anymore.


John Spencer said:
If Frame7 = 1 Then
Me.Visible = False

'Comment out the line for now
'DoCmd.SetWarnings False

'Comment out this line, it does nothing DoCmd.RunSQL ""

Dim strSQL as String 'Always declare your variables.


strSql = "SELECT TOP 10 " & vbCrLf & _
" Format([Date],""mmm"") AS [Month] " & vbCrLf & _
" , Format([Date],""yyyy"") AS [Year] " & vbCrLf & _
" , Model " & vbCrLf & _
" , Sum([" & Me.CboRejectItem & _
"]) AS [SumOf" & Me.CboRejectItem & "]" & vbCrLf & _
" INTO [The Worst 10 by Reject Item tbl] " & vbCrLf & _
" FROM [DC Fan Information Centre] " & vbCrLf & _
" WHERE Format([Date],""mmm"")=""" & Me.[cboMonth] & """" & vbCrLf & _
" AND Format([Date],""yyyy"")=""" & Me.[cboYear] & """ " & vbCrLf & _
" GROUP BY Format([Date],""mmm"") " & vbCrLf & _
" , Format([Date],""yyyy"") " & vbCrLf & _
" , Model " & vbCrLf & _
" ORDER BY Sum([Vibration]) DESC " & vbCrLf & _
" WITH OWNERACCESS OPTION;"

'Add the following to debug things.
Debug.Print StrSQL: STOP
'Check the sql string and see if it is valid.

CurrentDb().Execute strSql, dbFailOnError

DoCmd.SetWarnings True
DoCmd.OpenTable " The Worst 10 by Reject Item tbl", acNormal

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

I had modified the code like below

Private Sub CmdOK_Click()
If Frame7 = 1 Then
Me.Visible = False
DoCmd.SetWarnings False
DoCmd.RunSQL ""

strSql = "SELECT TOP 10 " & vbCrLf & _
" Format([Date],""mmm"") AS [Month] " & vbCrLf & _
" , Format([Date],""yyyy"") AS [Year] " & vbCrLf & _
" , Model " & vbCrLf & _
" , Sum([" & Me.CboRejectItem & _
"]) AS [SumOf" & Me.CboRejectItem & "]" & vbCrLf & _
" INTO [The Worst 10 by Reject Item tbl] " & vbCrLf & _
" FROM [DC Fan Information Centre] " & vbCrLf & _
" WHERE Format([Date],""mmm"")=""" & Me.[cboMonth] & """" & vbCrLf & _
" AND Format([Date],""yyyy"")=""" & Me.[cboYear] & """ " & vbCrLf & _
" GROUP BY Format([Date],""mmm"") " & vbCrLf & _
" , Format([Date],""yyyy"") " & vbCrLf & _
" , Model " & vbCrLf & _
" ORDER BY Sum([Vibration]) DESC " & vbCrLf & _
" WITH OWNERACCESS OPTION;"

CurrentDb().Execute strSql, dbFailOnError

DoCmd.SetWarnings True
DoCmd.OpenTable " The Worst 10 by Reject Item tbl", acNormal

But,VB told me
Compile Error:
Variable not defined !

What's wrong John?


John Spencer said:
Probably the best way to do this would be to build the SQL statement on the
fly.

And then use the button to build the SQL Statement and execute it.. The
following VERY ROUGH UNTESTED code snippet should get you started.

strSql = "SELECT TOP 10 " & vbCrLf & _
" Format([Date],""mmm"") AS [Month] " & vbCrLf & _
" , Format([Date],""yyyy"") AS [Year] " & vbCrLf & _
" , Model " & vbCrLf & _
" , Sum([" & Me.cboFieldName & _
"]) AS [SumOf" & Me.cboFieldName & "]" & vbCrLf & _
" INTO [The Worst 10 by Reject Item tbl] " & vbCrLf & _
" FROM [DC Fan Information Centre] " & vbCrLf & _
" WHERE Format([Date],""mmm"")=""" & Me.[cboMonth]) & """" & vbCrLf & _
" AND Format([Date],""yyyy"")=""" & me.[cboYear] & """ " & vbCrLf & _
" GROUP BY Format([Date],""mmm"") " & vbCrLf & _
" , Format([Date],""yyyy"") " & vbCrLf & _
" , Model " & vbCrLf & _
" ORDER BY Sum([Vibration]) DESC " & vbCrLf & _
" WITH OWNERACCESS OPTION;"

Currentdb().Execute strSQL, dbFailOnError

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
.

Hi All,
I have a SQL like below;

SELECT TOP 10 Format([Date],"mmm") AS [Month], Format([Date],"yyyy") AS
[Year], [DC Fan Information Centre].Model, Sum([DC Fan Information
Centre].Vibration) AS SumOfVibration INTO [The Worst 10 by Reject Item
tbl]
FROM [DC Fan Information Centre]
GROUP BY Format([Date],"mmm"), Format([Date],"yyyy"), [DC Fan Information
Centre].Model
HAVING (((Format([Date],"mmm"))=[Forms]![fltr Reject Rank By Reject
Item]![cboMonth]) AND ((Format([Date],"yyyy"))=[Forms]![fltr Reject Rank
By
Reject Item]![cboYear]))
ORDER BY Sum([DC Fan Information Centre].Vibration) DESC
WITH OWNERACCESS OPTION;

I want the field 'Vibration' can be replaced with one of another 10 fields
through
a combo box ( cbo box contains all of the fields name ) in form 'fltr
Reject Rank By Reject Item'
and then execute the SQL with a command button.Is it possible?

Hope somebody would like to advice.
Click to expand...
Click to expand...
Click to expand...
 
J

John Spencer

That error means that there is something wrong with the sql statement.
Probably the Order by clause since I formgot to replace Vibration there;

When you got the error did you try copying the SQL statement into a
query and running it there? If you did you should have gotten a
parameter prompt for the unknown item.

Try the following

strSql = "SELECT TOP 10 " & vbCrLf & _
" Format([Date],""mmm"") AS [Month] " & vbCrLf & _
" , Format([Date],""yyyy"") AS [Year] " & vbCrLf & _
" , Model " & vbCrLf & _
" , Sum([" & Me.CboRejectItem & _
"]) AS [SumOf" & Me.CboRejectItem & "]" & vbCrLf & _
" INTO [The Worst 10 by Reject Item tbl] " & vbCrLf & _
" FROM [DC Fan Information Centre] " & vbCrLf & _
" WHERE Format([Date],""mmm"")=""" & Me.[cboMonth] & """" & vbCrLf & _
" AND Format([Date],""yyyy"")=""" & Me.[cboYear] & """ " & vbCrLf & _
" GROUP BY Format([Date],""mmm"") " & vbCrLf & _
" , Format([Date],""yyyy"") " & vbCrLf & _
" , Model " & vbCrLf & _
" ORDER BY Sum([" & Me.CboRejectItem & "]) DESC " & vbCrLf & _
" WITH OWNERACCESS OPTION;"



'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

The code works fine untill debugging line.But now access
sent:
Run time Error 3061
Too few parameters.Expected 1.

What should I add in anymore.


John Spencer said:
If Frame7 = 1 Then
Me.Visible = False

'Comment out the line for now
'DoCmd.SetWarnings False

'Comment out this line, it does nothing DoCmd.RunSQL ""

Dim strSQL as String 'Always declare your variables.


strSql = "SELECT TOP 10 " & vbCrLf & _
" Format([Date],""mmm"") AS [Month] " & vbCrLf & _
" , Format([Date],""yyyy"") AS [Year] " & vbCrLf & _
" , Model " & vbCrLf & _
" , Sum([" & Me.CboRejectItem & _
"]) AS [SumOf" & Me.CboRejectItem & "]" & vbCrLf & _
" INTO [The Worst 10 by Reject Item tbl] " & vbCrLf & _
" FROM [DC Fan Information Centre] " & vbCrLf & _
" WHERE Format([Date],""mmm"")=""" & Me.[cboMonth] & """" & vbCrLf & _
" AND Format([Date],""yyyy"")=""" & Me.[cboYear] & """ " & vbCrLf & _
" GROUP BY Format([Date],""mmm"") " & vbCrLf & _
" , Format([Date],""yyyy"") " & vbCrLf & _
" , Model " & vbCrLf & _
" ORDER BY Sum([Vibration]) DESC " & vbCrLf & _
" WITH OWNERACCESS OPTION;"

'Add the following to debug things.
Debug.Print StrSQL: STOP
'Check the sql string and see if it is valid.

CurrentDb().Execute strSql, dbFailOnError

DoCmd.SetWarnings True
DoCmd.OpenTable " The Worst 10 by Reject Item tbl", acNormal

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

I had modified the code like below

Private Sub CmdOK_Click()
If Frame7 = 1 Then
Me.Visible = False
DoCmd.SetWarnings False
DoCmd.RunSQL ""

strSql = "SELECT TOP 10 " & vbCrLf & _
" Format([Date],""mmm"") AS [Month] " & vbCrLf & _
" , Format([Date],""yyyy"") AS [Year] " & vbCrLf & _
" , Model " & vbCrLf & _
" , Sum([" & Me.CboRejectItem & _
"]) AS [SumOf" & Me.CboRejectItem & "]" & vbCrLf & _
" INTO [The Worst 10 by Reject Item tbl] " & vbCrLf & _
" FROM [DC Fan Information Centre] " & vbCrLf & _
" WHERE Format([Date],""mmm"")=""" & Me.[cboMonth] & """" & vbCrLf & _
" AND Format([Date],""yyyy"")=""" & Me.[cboYear] & """ " & vbCrLf & _
" GROUP BY Format([Date],""mmm"") " & vbCrLf & _
" , Format([Date],""yyyy"") " & vbCrLf & _
" , Model " & vbCrLf & _
" ORDER BY Sum([Vibration]) DESC " & vbCrLf & _
" WITH OWNERACCESS OPTION;"

CurrentDb().Execute strSql, dbFailOnError

DoCmd.SetWarnings True
DoCmd.OpenTable " The Worst 10 by Reject Item tbl", acNormal

But,VB told me
Compile Error:
Variable not defined !

What's wrong John?


Probably the best way to do this would be to build the SQL statement on
the
fly.

And then use the button to build the SQL Statement and execute it.. The
following VERY ROUGH UNTESTED code snippet should get you started.

strSql = "SELECT TOP 10 " & vbCrLf & _
" Format([Date],""mmm"") AS [Month] " & vbCrLf & _
" , Format([Date],""yyyy"") AS [Year] " & vbCrLf & _
" , Model " & vbCrLf & _
" , Sum([" & Me.cboFieldName & _
"]) AS [SumOf" & Me.cboFieldName & "]" & vbCrLf & _
" INTO [The Worst 10 by Reject Item tbl] " & vbCrLf & _
" FROM [DC Fan Information Centre] " & vbCrLf & _
" WHERE Format([Date],""mmm"")=""" & Me.[cboMonth]) & """" & vbCrLf & _
" AND Format([Date],""yyyy"")=""" & me.[cboYear] & """ " & vbCrLf & _
" GROUP BY Format([Date],""mmm"") " & vbCrLf & _
" , Format([Date],""yyyy"") " & vbCrLf & _
" , Model " & vbCrLf & _
" ORDER BY Sum([Vibration]) DESC " & vbCrLf & _
" WITH OWNERACCESS OPTION;"

Currentdb().Execute strSQL, dbFailOnError

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
.

Hi All,
I have a SQL like below;

SELECT TOP 10 Format([Date],"mmm") AS [Month], Format([Date],"yyyy") AS
[Year], [DC Fan Information Centre].Model, Sum([DC Fan Information
Centre].Vibration) AS SumOfVibration INTO [The Worst 10 by Reject Item
tbl]
FROM [DC Fan Information Centre]
GROUP BY Format([Date],"mmm"), Format([Date],"yyyy"), [DC Fan
Information
Centre].Model
HAVING (((Format([Date],"mmm"))=[Forms]![fltr Reject Rank By Reject
Item]![cboMonth]) AND ((Format([Date],"yyyy"))=[Forms]![fltr Reject Rank
By
Reject Item]![cboYear]))
ORDER BY Sum([DC Fan Information Centre].Vibration) DESC
WITH OWNERACCESS OPTION;

I want the field 'Vibration' can be replaced with one of another 10
fields
through
a combo box ( cbo box contains all of the fields name ) in form 'fltr
Reject Rank By Reject Item'
and then execute the SQL with a command button.Is it possible?

Hope somebody would like to advice.
Click to expand...
Click to expand...
Click to expand...
 
S

shiro

John,
It seems the code unable to pull the field defined from
cboRejectItem from the table.

Now it send
Run-time error '3075' :
Syntax error in query expression 'Sum ([1] ])'.

Is it still possible to achieve with this methode?
Or any better idea?


John Spencer said:
That error means that there is something wrong with the sql statement.
Probably the Order by clause since I formgot to replace Vibration there;

When you got the error did you try copying the SQL statement into a
query and running it there? If you did you should have gotten a
parameter prompt for the unknown item.

Try the following

strSql = "SELECT TOP 10 " & vbCrLf & _
" Format([Date],""mmm"") AS [Month] " & vbCrLf & _
" , Format([Date],""yyyy"") AS [Year] " & vbCrLf & _
" , Model " & vbCrLf & _
" , Sum([" & Me.CboRejectItem & _
"]) AS [SumOf" & Me.CboRejectItem & "]" & vbCrLf & _
" INTO [The Worst 10 by Reject Item tbl] " & vbCrLf & _
" FROM [DC Fan Information Centre] " & vbCrLf & _
" WHERE Format([Date],""mmm"")=""" & Me.[cboMonth] & """" & vbCrLf & _
" AND Format([Date],""yyyy"")=""" & Me.[cboYear] & """ " & vbCrLf & _
" GROUP BY Format([Date],""mmm"") " & vbCrLf & _
" , Format([Date],""yyyy"") " & vbCrLf & _
" , Model " & vbCrLf & _
" ORDER BY Sum([" & Me.CboRejectItem & "]) DESC " & vbCrLf & _
" WITH OWNERACCESS OPTION;"



'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

The code works fine untill debugging line.But now access
sent:
Run time Error 3061
Too few parameters.Expected 1.

What should I add in anymore.


John Spencer said:
If Frame7 = 1 Then
Me.Visible = False

'Comment out the line for now
'DoCmd.SetWarnings False

'Comment out this line, it does nothing DoCmd.RunSQL ""

Dim strSQL as String 'Always declare your variables.


strSql = "SELECT TOP 10 " & vbCrLf & _
" Format([Date],""mmm"") AS [Month] " & vbCrLf & _
" , Format([Date],""yyyy"") AS [Year] " & vbCrLf & _
" , Model " & vbCrLf & _
" , Sum([" & Me.CboRejectItem & _
"]) AS [SumOf" & Me.CboRejectItem & "]" & vbCrLf & _
" INTO [The Worst 10 by Reject Item tbl] " & vbCrLf & _
" FROM [DC Fan Information Centre] " & vbCrLf & _
" WHERE Format([Date],""mmm"")=""" & Me.[cboMonth] & """" & vbCrLf & _
" AND Format([Date],""yyyy"")=""" & Me.[cboYear] & """ " & vbCrLf & _
" GROUP BY Format([Date],""mmm"") " & vbCrLf & _
" , Format([Date],""yyyy"") " & vbCrLf & _
" , Model " & vbCrLf & _
" ORDER BY Sum([Vibration]) DESC " & vbCrLf & _
" WITH OWNERACCESS OPTION;"

'Add the following to debug things.
Debug.Print StrSQL: STOP
'Check the sql string and see if it is valid.

CurrentDb().Execute strSql, dbFailOnError

DoCmd.SetWarnings True
DoCmd.OpenTable " The Worst 10 by Reject Item tbl", acNormal

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================


shiro wrote:
I had modified the code like below

Private Sub CmdOK_Click()
If Frame7 = 1 Then
Me.Visible = False
DoCmd.SetWarnings False
DoCmd.RunSQL ""

strSql = "SELECT TOP 10 " & vbCrLf & _
" Format([Date],""mmm"") AS [Month] " & vbCrLf & _
" , Format([Date],""yyyy"") AS [Year] " & vbCrLf & _
" , Model " & vbCrLf & _
" , Sum([" & Me.CboRejectItem & _
"]) AS [SumOf" & Me.CboRejectItem & "]" & vbCrLf & _
" INTO [The Worst 10 by Reject Item tbl] " & vbCrLf & _
" FROM [DC Fan Information Centre] " & vbCrLf & _
" WHERE Format([Date],""mmm"")=""" & Me.[cboMonth] & """" & vbCrLf & _
" AND Format([Date],""yyyy"")=""" & Me.[cboYear] & """ " & vbCrLf & _
" GROUP BY Format([Date],""mmm"") " & vbCrLf & _
" , Format([Date],""yyyy"") " & vbCrLf & _
" , Model " & vbCrLf & _
" ORDER BY Sum([Vibration]) DESC " & vbCrLf & _
" WITH OWNERACCESS OPTION;"

CurrentDb().Execute strSql, dbFailOnError

DoCmd.SetWarnings True
DoCmd.OpenTable " The Worst 10 by Reject Item tbl", acNormal

But,VB told me
Compile Error:
Variable not defined !

What's wrong John?


Probably the best way to do this would be to build the SQL statement on
the
fly.

And then use the button to build the SQL Statement and execute it.. The
following VERY ROUGH UNTESTED code snippet should get you started.

strSql = "SELECT TOP 10 " & vbCrLf & _
" Format([Date],""mmm"") AS [Month] " & vbCrLf & _
" , Format([Date],""yyyy"") AS [Year] " & vbCrLf & _
" , Model " & vbCrLf & _
" , Sum([" & Me.cboFieldName & _
"]) AS [SumOf" & Me.cboFieldName & "]" & vbCrLf & _
" INTO [The Worst 10 by Reject Item tbl] " & vbCrLf & _
" FROM [DC Fan Information Centre] " & vbCrLf & _
" WHERE Format([Date],""mmm"")=""" & Me.[cboMonth]) & """" & vbCrLf
Click to expand...
&
_
" AND Format([Date],""yyyy"")=""" & me.[cboYear] & """ " & vbCrLf & _
" GROUP BY Format([Date],""mmm"") " & vbCrLf & _
" , Format([Date],""yyyy"") " & vbCrLf & _
" , Model " & vbCrLf & _
" ORDER BY Sum([Vibration]) DESC " & vbCrLf & _
" WITH OWNERACCESS OPTION;"

Currentdb().Execute strSQL, dbFailOnError

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
.

Hi All,
I have a SQL like below;

SELECT TOP 10 Format([Date],"mmm") AS [Month], Format([Date],"yyyy") AS
[Year], [DC Fan Information Centre].Model, Sum([DC Fan Information
Centre].Vibration) AS SumOfVibration INTO [The Worst 10 by Reject Item
tbl]
FROM [DC Fan Information Centre]
GROUP BY Format([Date],"mmm"), Format([Date],"yyyy"), [DC Fan
Information
Centre].Model
HAVING (((Format([Date],"mmm"))=[Forms]![fltr Reject Rank By Reject
Item]![cboMonth]) AND ((Format([Date],"yyyy"))=[Forms]![fltr Reject Rank
By
Reject Item]![cboYear]))
ORDER BY Sum([DC Fan Information Centre].Vibration) DESC
WITH OWNERACCESS OPTION;

I want the field 'Vibration' can be replaced with one of another 10
fields
through
a combo box ( cbo box contains all of the fields name ) in form 'fltr
Reject Rank By Reject Item'
and then execute the SQL with a command button.Is it possible?

Hope somebody would like to advice.
Click to expand...
Click to expand...
Click to expand...
 
J

John Spencer

What you posted has an extra space and bracket that are being included in
the SQL string.

What is the field Name? What is in the combobox?
It looks as if you may have more than one column in the combobox and are
referencing the wrong column. The combobox should be bound to the column
that is displaying the field names.

Beyond that I am stuck on what is causing your problem and therefore unable
to help you with any other suggestions.
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

shiro said:
John,
It seems the code unable to pull the field defined from
cboRejectItem from the table.

Now it send
Run-time error '3075' :
Syntax error in query expression 'Sum ([1] ])'.

Is it still possible to achieve with this methode?
Or any better idea?


John Spencer said:
That error means that there is something wrong with the sql statement.
Probably the Order by clause since I formgot to replace Vibration there;

When you got the error did you try copying the SQL statement into a
query and running it there? If you did you should have gotten a
parameter prompt for the unknown item.

Try the following

strSql = "SELECT TOP 10 " & vbCrLf & _
" Format([Date],""mmm"") AS [Month] " & vbCrLf & _
" , Format([Date],""yyyy"") AS [Year] " & vbCrLf & _
" , Model " & vbCrLf & _
" , Sum([" & Me.CboRejectItem & _
"]) AS [SumOf" & Me.CboRejectItem & "]" & vbCrLf & _
" INTO [The Worst 10 by Reject Item tbl] " & vbCrLf & _
" FROM [DC Fan Information Centre] " & vbCrLf & _
" WHERE Format([Date],""mmm"")=""" & Me.[cboMonth] & """" & vbCrLf & _
" AND Format([Date],""yyyy"")=""" & Me.[cboYear] & """ " & vbCrLf & _
" GROUP BY Format([Date],""mmm"") " & vbCrLf & _
" , Format([Date],""yyyy"") " & vbCrLf & _
" , Model " & vbCrLf & _
" ORDER BY Sum([" & Me.CboRejectItem & "]) DESC " & vbCrLf & _
" WITH OWNERACCESS OPTION;"



'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

The code works fine untill debugging line.But now access
sent:
Run time Error 3061
Too few parameters.Expected 1.

What should I add in anymore.


If Frame7 = 1 Then
Me.Visible = False

'Comment out the line for now
'DoCmd.SetWarnings False

'Comment out this line, it does nothing DoCmd.RunSQL ""

Dim strSQL as String 'Always declare your variables.


strSql = "SELECT TOP 10 " & vbCrLf & _
" Format([Date],""mmm"") AS [Month] " & vbCrLf & _
" , Format([Date],""yyyy"") AS [Year] " & vbCrLf & _
" , Model " & vbCrLf & _
" , Sum([" & Me.CboRejectItem & _
"]) AS [SumOf" & Me.CboRejectItem & "]" & vbCrLf & _
" INTO [The Worst 10 by Reject Item tbl] " & vbCrLf & _
" FROM [DC Fan Information Centre] " & vbCrLf & _
" WHERE Format([Date],""mmm"")=""" & Me.[cboMonth] & """" & vbCrLf & _
" AND Format([Date],""yyyy"")=""" & Me.[cboYear] & """ " & vbCrLf & _
" GROUP BY Format([Date],""mmm"") " & vbCrLf & _
" , Format([Date],""yyyy"") " & vbCrLf & _
" , Model " & vbCrLf & _
" ORDER BY Sum([Vibration]) DESC " & vbCrLf & _
" WITH OWNERACCESS OPTION;"

'Add the following to debug things.
Debug.Print StrSQL: STOP
'Check the sql string and see if it is valid.

CurrentDb().Execute strSql, dbFailOnError

DoCmd.SetWarnings True
DoCmd.OpenTable " The Worst 10 by Reject Item tbl", acNormal

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================


shiro wrote:
I had modified the code like below

Private Sub CmdOK_Click()
If Frame7 = 1 Then
Me.Visible = False
DoCmd.SetWarnings False
DoCmd.RunSQL ""

strSql = "SELECT TOP 10 " & vbCrLf & _
" Format([Date],""mmm"") AS [Month] " & vbCrLf & _
" , Format([Date],""yyyy"") AS [Year] " & vbCrLf & _
" , Model " & vbCrLf & _
" , Sum([" & Me.CboRejectItem & _
"]) AS [SumOf" & Me.CboRejectItem & "]" & vbCrLf & _
" INTO [The Worst 10 by Reject Item tbl] " & vbCrLf & _
" FROM [DC Fan Information Centre] " & vbCrLf & _
" WHERE Format([Date],""mmm"")=""" & Me.[cboMonth] & """" & vbCrLf &
_
" AND Format([Date],""yyyy"")=""" & Me.[cboYear] & """ " & vbCrLf & _
" GROUP BY Format([Date],""mmm"") " & vbCrLf & _
" , Format([Date],""yyyy"") " & vbCrLf & _
" , Model " & vbCrLf & _
" ORDER BY Sum([Vibration]) DESC " & vbCrLf & _
" WITH OWNERACCESS OPTION;"

CurrentDb().Execute strSql, dbFailOnError

DoCmd.SetWarnings True
DoCmd.OpenTable " The Worst 10 by Reject Item tbl", acNormal

But,VB told me
Compile Error:
Variable not defined !

What's wrong John?


Probably the best way to do this would be to build the SQL statement on
the
fly.

And then use the button to build the SQL Statement and execute it..
The
following VERY ROUGH UNTESTED code snippet should get you started.

strSql = "SELECT TOP 10 " & vbCrLf & _
" Format([Date],""mmm"") AS [Month] " & vbCrLf & _
" , Format([Date],""yyyy"") AS [Year] " & vbCrLf & _
" , Model " & vbCrLf & _
" , Sum([" & Me.cboFieldName & _
"]) AS [SumOf" & Me.cboFieldName & "]" & vbCrLf & _
" INTO [The Worst 10 by Reject Item tbl] " & vbCrLf & _
" FROM [DC Fan Information Centre] " & vbCrLf & _
" WHERE Format([Date],""mmm"")=""" & Me.[cboMonth]) & """" & vbCrLf &
_
" AND Format([Date],""yyyy"")=""" & me.[cboYear] & """ " & vbCrLf &
_
" GROUP BY Format([Date],""mmm"") " & vbCrLf & _
" , Format([Date],""yyyy"") " & vbCrLf & _
" , Model " & vbCrLf & _
" ORDER BY Sum([Vibration]) DESC " & vbCrLf & _
" WITH OWNERACCESS OPTION;"

Currentdb().Execute strSQL, dbFailOnError

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
.

Hi All,
I have a SQL like below;

SELECT TOP 10 Format([Date],"mmm") AS [Month],
Format([Date],"yyyy")
AS
[Year], [DC Fan Information Centre].Model, Sum([DC Fan Information
Centre].Vibration) AS SumOfVibration INTO [The Worst 10 by Reject Item
tbl]
FROM [DC Fan Information Centre]
GROUP BY Format([Date],"mmm"), Format([Date],"yyyy"), [DC Fan
Information
Centre].Model
HAVING (((Format([Date],"mmm"))=[Forms]![fltr Reject Rank By Reject
Item]![cboMonth]) AND ((Format([Date],"yyyy"))=[Forms]![fltr Reject
Rank
By
Reject Item]![cboYear]))
ORDER BY Sum([DC Fan Information Centre].Vibration) DESC
WITH OWNERACCESS OPTION;

I want the field 'Vibration' can be replaced with one of another 10
fields
through
a combo box ( cbo box contains all of the fields name ) in form 'fltr
Reject Rank By Reject Item'
and then execute the SQL with a command button.Is it possible?

Hope somebody would like to advice.
Click to expand...
Click to expand...
Click to expand...
 
S

shiro

Aha...you're right John,
the combobox.It has two bound column?.
Now it works fine,it just can create table
" The worst time by reject item" once.
How to write the code so that it can replace
the existing table with the new table.


John Spencer said:
What you posted has an extra space and bracket that are being included in
the SQL string.

What is the field Name? What is in the combobox?
It looks as if you may have more than one column in the combobox and are
referencing the wrong column. The combobox should be bound to the column
that is displaying the field names.

Beyond that I am stuck on what is causing your problem and therefore unable
to help you with any other suggestions.
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
.

shiro said:
John,
It seems the code unable to pull the field defined from
cboRejectItem from the table.

Now it send
Run-time error '3075' :
Syntax error in query expression 'Sum ([1] ])'.

Is it still possible to achieve with this methode?
Or any better idea?


John Spencer said:
That error means that there is something wrong with the sql statement.
Probably the Order by clause since I formgot to replace Vibration there;

When you got the error did you try copying the SQL statement into a
query and running it there? If you did you should have gotten a
parameter prompt for the unknown item.

Try the following

strSql = "SELECT TOP 10 " & vbCrLf & _
" Format([Date],""mmm"") AS [Month] " & vbCrLf & _
" , Format([Date],""yyyy"") AS [Year] " & vbCrLf & _
" , Model " & vbCrLf & _
" , Sum([" & Me.CboRejectItem & _
"]) AS [SumOf" & Me.CboRejectItem & "]" & vbCrLf & _
" INTO [The Worst 10 by Reject Item tbl] " & vbCrLf & _
" FROM [DC Fan Information Centre] " & vbCrLf & _
" WHERE Format([Date],""mmm"")=""" & Me.[cboMonth] & """" & vbCrLf & _
" AND Format([Date],""yyyy"")=""" & Me.[cboYear] & """ " & vbCrLf & _
" GROUP BY Format([Date],""mmm"") " & vbCrLf & _
" , Format([Date],""yyyy"") " & vbCrLf & _
" , Model " & vbCrLf & _
" ORDER BY Sum([" & Me.CboRejectItem & "]) DESC " & vbCrLf & _
" WITH OWNERACCESS OPTION;"



'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================


shiro wrote:
The code works fine untill debugging line.But now access
sent:
Run time Error 3061
Too few parameters.Expected 1.

What should I add in anymore.


If Frame7 = 1 Then
Me.Visible = False

'Comment out the line for now
'DoCmd.SetWarnings False

'Comment out this line, it does nothing DoCmd.RunSQL ""

Dim strSQL as String 'Always declare your variables.


strSql = "SELECT TOP 10 " & vbCrLf & _
" Format([Date],""mmm"") AS [Month] " & vbCrLf & _
" , Format([Date],""yyyy"") AS [Year] " & vbCrLf & _
" , Model " & vbCrLf & _
" , Sum([" & Me.CboRejectItem & _
"]) AS [SumOf" & Me.CboRejectItem & "]" & vbCrLf & _
" INTO [The Worst 10 by Reject Item tbl] " & vbCrLf & _
" FROM [DC Fan Information Centre] " & vbCrLf & _
" WHERE Format([Date],""mmm"")=""" & Me.[cboMonth] & """" & vbCrLf & _
" AND Format([Date],""yyyy"")=""" & Me.[cboYear] & """ " & vbCrLf & _
" GROUP BY Format([Date],""mmm"") " & vbCrLf & _
" , Format([Date],""yyyy"") " & vbCrLf & _
" , Model " & vbCrLf & _
" ORDER BY Sum([Vibration]) DESC " & vbCrLf & _
" WITH OWNERACCESS OPTION;"

'Add the following to debug things.
Debug.Print StrSQL: STOP
'Check the sql string and see if it is valid.

CurrentDb().Execute strSql, dbFailOnError

DoCmd.SetWarnings True
DoCmd.OpenTable " The Worst 10 by Reject Item tbl", acNormal

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================


shiro wrote:
I had modified the code like below

Private Sub CmdOK_Click()
If Frame7 = 1 Then
Me.Visible = False
DoCmd.SetWarnings False
DoCmd.RunSQL ""

strSql = "SELECT TOP 10 " & vbCrLf & _
" Format([Date],""mmm"") AS [Month] " & vbCrLf & _
" , Format([Date],""yyyy"") AS [Year] " & vbCrLf & _
" , Model " & vbCrLf & _
" , Sum([" & Me.CboRejectItem & _
"]) AS [SumOf" & Me.CboRejectItem & "]" & vbCrLf & _
" INTO [The Worst 10 by Reject Item tbl] " & vbCrLf & _
" FROM [DC Fan Information Centre] " & vbCrLf & _
" WHERE Format([Date],""mmm"")=""" & Me.[cboMonth] & """" & vbCrLf &
_
" AND Format([Date],""yyyy"")=""" & Me.[cboYear] & """ " & vbCrLf & _
" GROUP BY Format([Date],""mmm"") " & vbCrLf & _
" , Format([Date],""yyyy"") " & vbCrLf & _
" , Model " & vbCrLf & _
" ORDER BY Sum([Vibration]) DESC " & vbCrLf & _
" WITH OWNERACCESS OPTION;"

CurrentDb().Execute strSql, dbFailOnError

DoCmd.SetWarnings True
DoCmd.OpenTable " The Worst 10 by Reject Item tbl", acNormal

But,VB told me
Compile Error:
Variable not defined !

What's wrong John?


Probably the best way to do this would be to build the SQL
Click to expand...
statement
on
the
fly.

And then use the button to build the SQL Statement and execute it..
The
following VERY ROUGH UNTESTED code snippet should get you started.

strSql = "SELECT TOP 10 " & vbCrLf & _
" Format([Date],""mmm"") AS [Month] " & vbCrLf & _
" , Format([Date],""yyyy"") AS [Year] " & vbCrLf & _
" , Model " & vbCrLf & _
" , Sum([" & Me.cboFieldName & _
"]) AS [SumOf" & Me.cboFieldName & "]" & vbCrLf & _
" INTO [The Worst 10 by Reject Item tbl] " & vbCrLf & _
" FROM [DC Fan Information Centre] " & vbCrLf & _
" WHERE Format([Date],""mmm"")=""" & Me.[cboMonth]) & """" &
Click to expand...
vbCrLf
&
_
" AND Format([Date],""yyyy"")=""" & me.[cboYear] & """ " & vbCrLf &
_
" GROUP BY Format([Date],""mmm"") " & vbCrLf & _
" , Format([Date],""yyyy"") " & vbCrLf & _
" , Model " & vbCrLf & _
" ORDER BY Sum([Vibration]) DESC " & vbCrLf & _
" WITH OWNERACCESS OPTION;"

Currentdb().Execute strSQL, dbFailOnError

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
.

Hi All,
I have a SQL like below;

SELECT TOP 10 Format([Date],"mmm") AS [Month],
Format([Date],"yyyy")
AS
[Year], [DC Fan Information Centre].Model, Sum([DC Fan Information
Centre].Vibration) AS SumOfVibration INTO [The Worst 10 by Reject Item
tbl]
FROM [DC Fan Information Centre]
GROUP BY Format([Date],"mmm"), Format([Date],"yyyy"), [DC Fan
Information
Centre].Model
HAVING (((Format([Date],"mmm"))=[Forms]![fltr Reject Rank By Reject
Item]![cboMonth]) AND ((Format([Date],"yyyy"))=[Forms]![fltr Reject
Rank
By
Reject Item]![cboYear]))
ORDER BY Sum([DC Fan Information Centre].Vibration) DESC
WITH OWNERACCESS OPTION;

I want the field 'Vibration' can be replaced with one of another 10
fields
through
a combo box ( cbo box contains all of the fields name ) in form 'fltr
Reject Rank By Reject Item'
and then execute the SQL with a command button.Is it possible?

Hope somebody would like to advice.
Click to expand...
Click to expand...
Click to expand...
 
S

shiro

I just write
DoCmd.DeleteObject acTable," The Worst 10 by Reject Item tbl"

It works bu is it good.Cause I'm not familiar about code.Thank's
for advanced

shiro said:
Aha...you're right John,
the combobox.It has two bound column?.
Now it works fine,it just can create table
" The worst time by reject item" once.
How to write the code so that it can replace
the existing table with the new table.


John Spencer said:
What you posted has an extra space and bracket that are being included in
the SQL string.

What is the field Name? What is in the combobox?
It looks as if you may have more than one column in the combobox and are
referencing the wrong column. The combobox should be bound to the column
that is displaying the field names.

Beyond that I am stuck on what is causing your problem and therefore unable
to help you with any other suggestions.
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
.

shiro said:
John,
It seems the code unable to pull the field defined from
cboRejectItem from the table.

Now it send
Run-time error '3075' :
Syntax error in query expression 'Sum ([1] ])'.

Is it still possible to achieve with this methode?
Or any better idea?


That error means that there is something wrong with the sql statement.
Probably the Order by clause since I formgot to replace Vibration there;

When you got the error did you try copying the SQL statement into a
query and running it there? If you did you should have gotten a
parameter prompt for the unknown item.

Try the following

strSql = "SELECT TOP 10 " & vbCrLf & _
" Format([Date],""mmm"") AS [Month] " & vbCrLf & _
" , Format([Date],""yyyy"") AS [Year] " & vbCrLf & _
" , Model " & vbCrLf & _
" , Sum([" & Me.CboRejectItem & _
"]) AS [SumOf" & Me.CboRejectItem & "]" & vbCrLf & _
" INTO [The Worst 10 by Reject Item tbl] " & vbCrLf & _
" FROM [DC Fan Information Centre] " & vbCrLf & _
" WHERE Format([Date],""mmm"")=""" & Me.[cboMonth] & """" & vbCrLf
Click to expand...
Click to expand...
&
_
" AND Format([Date],""yyyy"")=""" & Me.[cboYear] & """ " & vbCrLf & _
" GROUP BY Format([Date],""mmm"") " & vbCrLf & _
" , Format([Date],""yyyy"") " & vbCrLf & _
" , Model " & vbCrLf & _
" ORDER BY Sum([" & Me.CboRejectItem & "]) DESC " & vbCrLf & _
" WITH OWNERACCESS OPTION;"



'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================


shiro wrote:
The code works fine untill debugging line.But now access
sent:
Run time Error 3061
Too few parameters.Expected 1.

What should I add in anymore.


If Frame7 = 1 Then
Me.Visible = False

'Comment out the line for now
'DoCmd.SetWarnings False

'Comment out this line, it does nothing DoCmd.RunSQL ""

Dim strSQL as String 'Always declare your variables.


strSql = "SELECT TOP 10 " & vbCrLf & _
" Format([Date],""mmm"") AS [Month] " & vbCrLf & _
" , Format([Date],""yyyy"") AS [Year] " & vbCrLf & _
" , Model " & vbCrLf & _
" , Sum([" & Me.CboRejectItem & _
"]) AS [SumOf" & Me.CboRejectItem & "]" & vbCrLf & _
" INTO [The Worst 10 by Reject Item tbl] " & vbCrLf & _
" FROM [DC Fan Information Centre] " & vbCrLf & _
" WHERE Format([Date],""mmm"")=""" & Me.[cboMonth] & """" & vbCrLf
Click to expand...
Click to expand...
&
_
" AND Format([Date],""yyyy"")=""" & Me.[cboYear] & """ " & vbCrLf
Click to expand...
Click to expand...
&
_
" GROUP BY Format([Date],""mmm"") " & vbCrLf & _
" , Format([Date],""yyyy"") " & vbCrLf & _
" , Model " & vbCrLf & _
" ORDER BY Sum([Vibration]) DESC " & vbCrLf & _
" WITH OWNERACCESS OPTION;"

'Add the following to debug things.
Debug.Print StrSQL: STOP
'Check the sql string and see if it is valid.

CurrentDb().Execute strSql, dbFailOnError

DoCmd.SetWarnings True
DoCmd.OpenTable " The Worst 10 by Reject Item tbl", acNormal

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================


shiro wrote:
I had modified the code like below

Private Sub CmdOK_Click()
If Frame7 = 1 Then
Me.Visible = False
DoCmd.SetWarnings False
DoCmd.RunSQL ""

strSql = "SELECT TOP 10 " & vbCrLf & _
" Format([Date],""mmm"") AS [Month] " & vbCrLf & _
" , Format([Date],""yyyy"") AS [Year] " & vbCrLf & _
" , Model " & vbCrLf & _
" , Sum([" & Me.CboRejectItem & _
"]) AS [SumOf" & Me.CboRejectItem & "]" & vbCrLf & _
" INTO [The Worst 10 by Reject Item tbl] " & vbCrLf & _
" FROM [DC Fan Information Centre] " & vbCrLf & _
" WHERE Format([Date],""mmm"")=""" & Me.[cboMonth] & """" &
Click to expand...
Click to expand...
vbCrLf
&
_
" AND Format([Date],""yyyy"")=""" & Me.[cboYear] & """ " & vbCrLf
Click to expand...
Click to expand...
&
_
" GROUP BY Format([Date],""mmm"") " & vbCrLf & _
" , Format([Date],""yyyy"") " & vbCrLf & _
" , Model " & vbCrLf & _
" ORDER BY Sum([Vibration]) DESC " & vbCrLf & _
" WITH OWNERACCESS OPTION;"

CurrentDb().Execute strSql, dbFailOnError

DoCmd.SetWarnings True
DoCmd.OpenTable " The Worst 10 by Reject Item tbl", acNormal

But,VB told me
Compile Error:
Variable not defined !

What's wrong John?


Probably the best way to do this would be to build the SQL statement
on
the
fly.

And then use the button to build the SQL Statement and execute it..
The
following VERY ROUGH UNTESTED code snippet should get you started.

strSql = "SELECT TOP 10 " & vbCrLf & _
" Format([Date],""mmm"") AS [Month] " & vbCrLf & _
" , Format([Date],""yyyy"") AS [Year] " & vbCrLf & _
" , Model " & vbCrLf & _
" , Sum([" & Me.cboFieldName & _
"]) AS [SumOf" & Me.cboFieldName & "]" & vbCrLf & _
" INTO [The Worst 10 by Reject Item tbl] " & vbCrLf & _
" FROM [DC Fan Information Centre] " & vbCrLf & _
" WHERE Format([Date],""mmm"")=""" & Me.[cboMonth]) & """" & vbCrLf
&
_
" AND Format([Date],""yyyy"")=""" & me.[cboYear] & """ " &
Click to expand...
Click to expand...
vbCrLf
&
_
" GROUP BY Format([Date],""mmm"") " & vbCrLf & _
" , Format([Date],""yyyy"") " & vbCrLf & _
" , Model " & vbCrLf & _
" ORDER BY Sum([Vibration]) DESC " & vbCrLf & _
" WITH OWNERACCESS OPTION;"

Currentdb().Execute strSQL, dbFailOnError

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
.

Hi All,
I have a SQL like below;

SELECT TOP 10 Format([Date],"mmm") AS [Month],
Format([Date],"yyyy")
AS
[Year], [DC Fan Information Centre].Model, Sum([DC Fan Information
Centre].Vibration) AS SumOfVibration INTO [The Worst 10 by Reject
Item
tbl]
FROM [DC Fan Information Centre]
GROUP BY Format([Date],"mmm"), Format([Date],"yyyy"), [DC Fan
Information
Centre].Model
HAVING (((Format([Date],"mmm"))=[Forms]![fltr Reject Rank By Reject
Item]![cboMonth]) AND ((Format([Date],"yyyy"))=[Forms]![fltr Reject
Rank
By
Reject Item]![cboYear]))
ORDER BY Sum([DC Fan Information Centre].Vibration) DESC
WITH OWNERACCESS OPTION;

I want the field 'Vibration' can be replaced with one of
Click to expand...
Click to expand...
another
10
Click to expand...
Click to expand...
 
S

shiro

But John,
One more question,

" , Sum([" & Me.CboRejectItem & _
"]) AS [SumOf" & Me.CboRejectItem & "]" & vbCrLf & _

Can we change the field name* AS [SumOf" & Me.CboRejectItem & "]" to
AS [" & Total quantity & "]"


shiro said:
I just write
DoCmd.DeleteObject acTable," The Worst 10 by Reject Item tbl"

It works bu is it good.Cause I'm not familiar about code.Thank's
for advanced

shiro said:
Aha...you're right John,
the combobox.It has two bound column?.
Now it works fine,it just can create table
" The worst time by reject item" once.
How to write the code so that it can replace
the existing table with the new table.


John Spencer said:
What you posted has an extra space and bracket that are being included in
the SQL string.

What is the field Name? What is in the combobox?
It looks as if you may have more than one column in the combobox and are
referencing the wrong column. The combobox should be bound to the column
that is displaying the field names.

Beyond that I am stuck on what is causing your problem and therefore unable
to help you with any other suggestions.
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
.

John,
It seems the code unable to pull the field defined from
cboRejectItem from the table.

Now it send
Run-time error '3075' :
Syntax error in query expression 'Sum ([1] ])'.

Is it still possible to achieve with this methode?
Or any better idea?


That error means that there is something wrong with the sql statement.
Probably the Order by clause since I formgot to replace Vibration there;

When you got the error did you try copying the SQL statement into a
query and running it there? If you did you should have gotten a
parameter prompt for the unknown item.

Try the following

strSql = "SELECT TOP 10 " & vbCrLf & _
" Format([Date],""mmm"") AS [Month] " & vbCrLf & _
" , Format([Date],""yyyy"") AS [Year] " & vbCrLf & _
" , Model " & vbCrLf & _
" , Sum([" & Me.CboRejectItem & _
"]) AS [SumOf" & Me.CboRejectItem & "]" & vbCrLf & _
" INTO [The Worst 10 by Reject Item tbl] " & vbCrLf & _
" FROM [DC Fan Information Centre] " & vbCrLf & _
" WHERE Format([Date],""mmm"")=""" & Me.[cboMonth] & """" &
Click to expand...
Click to expand...
vbCrLf
&
_
" AND Format([Date],""yyyy"")=""" & Me.[cboYear] & """ " & vbCrLf
Click to expand...
Click to expand...
&
_
" GROUP BY Format([Date],""mmm"") " & vbCrLf & _
" , Format([Date],""yyyy"") " & vbCrLf & _
" , Model " & vbCrLf & _
" ORDER BY Sum([" & Me.CboRejectItem & "]) DESC " & vbCrLf & _
" WITH OWNERACCESS OPTION;"



'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================


shiro wrote:
The code works fine untill debugging line.But now access
sent:
Run time Error 3061
Too few parameters.Expected 1.

What should I add in anymore.


If Frame7 = 1 Then
Me.Visible = False

'Comment out the line for now
'DoCmd.SetWarnings False

'Comment out this line, it does nothing DoCmd.RunSQL ""

Dim strSQL as String 'Always declare your variables.


strSql = "SELECT TOP 10 " & vbCrLf & _
" Format([Date],""mmm"") AS [Month] " & vbCrLf & _
" , Format([Date],""yyyy"") AS [Year] " & vbCrLf & _
" , Model " & vbCrLf & _
" , Sum([" & Me.CboRejectItem & _
"]) AS [SumOf" & Me.CboRejectItem & "]" & vbCrLf & _
" INTO [The Worst 10 by Reject Item tbl] " & vbCrLf & _
" FROM [DC Fan Information Centre] " & vbCrLf & _
" WHERE Format([Date],""mmm"")=""" & Me.[cboMonth] & """" &
Click to expand...
Click to expand...
vbCrLf
&
_
" AND Format([Date],""yyyy"")=""" & Me.[cboYear] & """ " &
Click to expand...
Click to expand...
vbCrLf
&
_
" GROUP BY Format([Date],""mmm"") " & vbCrLf & _
" , Format([Date],""yyyy"") " & vbCrLf & _
" , Model " & vbCrLf & _
" ORDER BY Sum([Vibration]) DESC " & vbCrLf & _
" WITH OWNERACCESS OPTION;"

'Add the following to debug things.
Debug.Print StrSQL: STOP
'Check the sql string and see if it is valid.

CurrentDb().Execute strSql, dbFailOnError

DoCmd.SetWarnings True
DoCmd.OpenTable " The Worst 10 by Reject Item tbl", acNormal

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================


shiro wrote:
I had modified the code like below

Private Sub CmdOK_Click()
If Frame7 = 1 Then
Me.Visible = False
DoCmd.SetWarnings False
DoCmd.RunSQL ""

strSql = "SELECT TOP 10 " & vbCrLf & _
" Format([Date],""mmm"") AS [Month] " & vbCrLf & _
" , Format([Date],""yyyy"") AS [Year] " & vbCrLf & _
" , Model " & vbCrLf & _
" , Sum([" & Me.CboRejectItem & _
"]) AS [SumOf" & Me.CboRejectItem & "]" & vbCrLf & _
" INTO [The Worst 10 by Reject Item tbl] " & vbCrLf & _
" FROM [DC Fan Information Centre] " & vbCrLf & _
" WHERE Format([Date],""mmm"")=""" & Me.[cboMonth] & """" &
Click to expand...
vbCrLf
&
_
" AND Format([Date],""yyyy"")=""" & Me.[cboYear] & """ " &
Click to expand...
Click to expand...
vbCrLf
&
_
" GROUP BY Format([Date],""mmm"") " & vbCrLf & _
" , Format([Date],""yyyy"") " & vbCrLf & _
" , Model " & vbCrLf & _
" ORDER BY Sum([Vibration]) DESC " & vbCrLf & _
" WITH OWNERACCESS OPTION;"

CurrentDb().Execute strSql, dbFailOnError

DoCmd.SetWarnings True
DoCmd.OpenTable " The Worst 10 by Reject Item tbl", acNormal

But,VB told me
Compile Error:
Variable not defined !

What's wrong John?


Probably the best way to do this would be to build the SQL statement
on
the
fly.

And then use the button to build the SQL Statement and execute it..
The
following VERY ROUGH UNTESTED code snippet should get you started.

strSql = "SELECT TOP 10 " & vbCrLf & _
" Format([Date],""mmm"") AS [Month] " & vbCrLf & _
" , Format([Date],""yyyy"") AS [Year] " & vbCrLf & _
" , Model " & vbCrLf & _
" , Sum([" & Me.cboFieldName & _
"]) AS [SumOf" & Me.cboFieldName & "]" & vbCrLf & _
" INTO [The Worst 10 by Reject Item tbl] " & vbCrLf & _
" FROM [DC Fan Information Centre] " & vbCrLf & _
" WHERE Format([Date],""mmm"")=""" & Me.[cboMonth]) & """" & vbCrLf
&
_
" AND Format([Date],""yyyy"")=""" & me.[cboYear] & """ " &
Click to expand...
vbCrLf
&
_
" GROUP BY Format([Date],""mmm"") " & vbCrLf & _
" , Format([Date],""yyyy"") " & vbCrLf & _
" , Model " & vbCrLf & _
" ORDER BY Sum([Vibration]) DESC " & vbCrLf & _
" WITH OWNERACCESS OPTION;"

Currentdb().Execute strSQL, dbFailOnError

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
.

Hi All,
I have a SQL like below;

SELECT TOP 10 Format([Date],"mmm") AS [Month],
Format([Date],"yyyy")
AS
[Year], [DC Fan Information Centre].Model, Sum([DC Fan Information
Centre].Vibration) AS SumOfVibration INTO [The Worst 10 by Reject
Item
tbl]
FROM [DC Fan Information Centre]
GROUP BY Format([Date],"mmm"), Format([Date],"yyyy"), [DC Fan
Information
Centre].Model
HAVING (((Format([Date],"mmm"))=[Forms]![fltr Reject Rank By Reject
Item]![cboMonth]) AND ((Format([Date],"yyyy"))=[Forms]![fltr Reject
Rank
By
Reject Item]![cboYear]))
ORDER BY Sum([DC Fan Information Centre].Vibration) DESC
WITH OWNERACCESS OPTION;

I want the field 'Vibration' can be replaced with one of
Click to expand...
another
10
fields
through
a combo box ( cbo box contains all of the fields name ) in form
'fltr
Reject Rank By Reject Item'
and then execute the SQL with a command button.Is it possible?

Hope somebody would like to advice.
Click to expand...
Click to expand...
Click to expand...
 
J

John Spencer

Try the following

" , Sum([" & Me.CboRejectItem & "]) AS [Total Quantity] " & vbCrLf & _

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

shiro said:
But John,
One more question,

" , Sum([" & Me.CboRejectItem & _
"]) AS [SumOf" & Me.CboRejectItem & "]" & vbCrLf & _

Can we change the field name* AS [SumOf" & Me.CboRejectItem & "]" to
AS [" & Total quantity & "]"


shiro said:
I just write
DoCmd.DeleteObject acTable," The Worst 10 by Reject Item tbl"

It works bu is it good.Cause I'm not familiar about code.Thank's
for advanced

shiro said:
Aha...you're right John,
the combobox.It has two bound column?.
Now it works fine,it just can create table
" The worst time by reject item" once.
How to write the code so that it can replace
the existing table with the new table.


What you posted has an extra space and bracket that are being
included in
the SQL string.

What is the field Name? What is in the combobox?
It looks as if you may have more than one column in the combobox and are
referencing the wrong column. The combobox should be bound to the column
that is displaying the field names.

Beyond that I am stuck on what is causing your problem and therefore
unable
to help you with any other suggestions.
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
.

John,
It seems the code unable to pull the field defined from
cboRejectItem from the table.

Now it send
Run-time error '3075' :
Syntax error in query expression 'Sum ([1] ])'.

Is it still possible to achieve with this methode?
Or any better idea?


That error means that there is something wrong with the sql statement.
Probably the Order by clause since I formgot to replace Vibration
there;

When you got the error did you try copying the SQL statement into
a
query and running it there? If you did you should have gotten a
parameter prompt for the unknown item.

Try the following

strSql = "SELECT TOP 10 " & vbCrLf & _
" Format([Date],""mmm"") AS [Month] " & vbCrLf & _
" , Format([Date],""yyyy"") AS [Year] " & vbCrLf & _
" , Model " & vbCrLf & _
" , Sum([" & Me.CboRejectItem & _
"]) AS [SumOf" & Me.CboRejectItem & "]" & vbCrLf & _
" INTO [The Worst 10 by Reject Item tbl] " & vbCrLf & _
" FROM [DC Fan Information Centre] " & vbCrLf & _
" WHERE Format([Date],""mmm"")=""" & Me.[cboMonth] & """" &
Click to expand...
vbCrLf
&
_
" AND Format([Date],""yyyy"")=""" & Me.[cboYear] & """ " &
vbCrLf
Click to expand...
&
_
" GROUP BY Format([Date],""mmm"") " & vbCrLf & _
" , Format([Date],""yyyy"") " & vbCrLf & _
" , Model " & vbCrLf & _
" ORDER BY Sum([" & Me.CboRejectItem & "]) DESC " & vbCrLf & _
" WITH OWNERACCESS OPTION;"



'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================


shiro wrote:
The code works fine untill debugging line.But now access
sent:
Run time Error 3061
Too few parameters.Expected 1.

What should I add in anymore.


If Frame7 = 1 Then
Me.Visible = False

'Comment out the line for now
'DoCmd.SetWarnings False

'Comment out this line, it does nothing DoCmd.RunSQL ""

Dim strSQL as String 'Always declare your variables.


strSql = "SELECT TOP 10 " & vbCrLf & _
" Format([Date],""mmm"") AS [Month] " & vbCrLf & _
" , Format([Date],""yyyy"") AS [Year] " & vbCrLf & _
" , Model " & vbCrLf & _
" , Sum([" & Me.CboRejectItem & _
"]) AS [SumOf" & Me.CboRejectItem & "]" & vbCrLf & _
" INTO [The Worst 10 by Reject Item tbl] " & vbCrLf & _
" FROM [DC Fan Information Centre] " & vbCrLf & _
" WHERE Format([Date],""mmm"")=""" & Me.[cboMonth] & """" &
Click to expand...
vbCrLf
&
_
" AND Format([Date],""yyyy"")=""" & Me.[cboYear] & """ " &
Click to expand...
vbCrLf
&
_
" GROUP BY Format([Date],""mmm"") " & vbCrLf & _
" , Format([Date],""yyyy"") " & vbCrLf & _
" , Model " & vbCrLf & _
" ORDER BY Sum([Vibration]) DESC " & vbCrLf & _
" WITH OWNERACCESS OPTION;"

'Add the following to debug things.
Debug.Print StrSQL: STOP
'Check the sql string and see if it is valid.

CurrentDb().Execute strSql, dbFailOnError

DoCmd.SetWarnings True
DoCmd.OpenTable " The Worst 10 by Reject Item tbl", acNormal

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================


shiro wrote:
I had modified the code like below

Private Sub CmdOK_Click()
If Frame7 = 1 Then
Me.Visible = False
DoCmd.SetWarnings False
DoCmd.RunSQL ""

strSql = "SELECT TOP 10 " & vbCrLf & _
" Format([Date],""mmm"") AS [Month] " & vbCrLf & _
" , Format([Date],""yyyy"") AS [Year] " & vbCrLf & _
" , Model " & vbCrLf & _
" , Sum([" & Me.CboRejectItem & _
"]) AS [SumOf" & Me.CboRejectItem & "]" & vbCrLf & _
" INTO [The Worst 10 by Reject Item tbl] " & vbCrLf & _
" FROM [DC Fan Information Centre] " & vbCrLf & _
" WHERE Format([Date],""mmm"")=""" & Me.[cboMonth] & """" & vbCrLf
&
_
" AND Format([Date],""yyyy"")=""" & Me.[cboYear] & """ " &
Click to expand...
vbCrLf
&
_
" GROUP BY Format([Date],""mmm"") " & vbCrLf & _
" , Format([Date],""yyyy"") " & vbCrLf & _
" , Model " & vbCrLf & _
" ORDER BY Sum([Vibration]) DESC " & vbCrLf & _
" WITH OWNERACCESS OPTION;"

CurrentDb().Execute strSql, dbFailOnError

DoCmd.SetWarnings True
DoCmd.OpenTable " The Worst 10 by Reject Item tbl", acNormal

But,VB told me
Compile Error:
Variable not defined !

What's wrong John?


Probably the best way to do this would be to build the SQL
statement
on
the
fly.

And then use the button to build the SQL Statement and
execute
it..
The
following VERY ROUGH UNTESTED code snippet should get you started.

strSql = "SELECT TOP 10 " & vbCrLf & _
" Format([Date],""mmm"") AS [Month] " & vbCrLf & _
" , Format([Date],""yyyy"") AS [Year] " & vbCrLf & _
" , Model " & vbCrLf & _
" , Sum([" & Me.cboFieldName & _
"]) AS [SumOf" & Me.cboFieldName & "]" & vbCrLf & _
" INTO [The Worst 10 by Reject Item tbl] " & vbCrLf & _
" FROM [DC Fan Information Centre] " & vbCrLf & _
" WHERE Format([Date],""mmm"")=""" & Me.[cboMonth]) & """" &
vbCrLf
&
_
" AND Format([Date],""yyyy"")=""" & me.[cboYear] & """ " & vbCrLf
&
_
" GROUP BY Format([Date],""mmm"") " & vbCrLf & _
" , Format([Date],""yyyy"") " & vbCrLf & _
" , Model " & vbCrLf & _
" ORDER BY Sum([Vibration]) DESC " & vbCrLf & _
" WITH OWNERACCESS OPTION;"

Currentdb().Execute strSQL, dbFailOnError

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
.

Hi All,
I have a SQL like below;

SELECT TOP 10 Format([Date],"mmm") AS [Month],
Format([Date],"yyyy")
AS
[Year], [DC Fan Information Centre].Model, Sum([DC Fan
Information
Centre].Vibration) AS SumOfVibration INTO [The Worst 10 by Reject
Item
tbl]
FROM [DC Fan Information Centre]
GROUP BY Format([Date],"mmm"), Format([Date],"yyyy"), [DC
Fan
Information
Centre].Model
HAVING (((Format([Date],"mmm"))=[Forms]![fltr Reject Rank By
Reject
Item]![cboMonth]) AND ((Format([Date],"yyyy"))=[Forms]![fltr
Reject
Rank
By
Reject Item]![cboYear]))
ORDER BY Sum([DC Fan Information Centre].Vibration) DESC
WITH OWNERACCESS OPTION;

I want the field 'Vibration' can be replaced with one of another
10
fields
through
a combo box ( cbo box contains all of the fields name ) in form
'fltr
Reject Rank By Reject Item'
and then execute the SQL with a command button.Is it possible?

Hope somebody would like to advice.
Click to expand...
Click to expand...
Click to expand...
 
S

shiro

Hi all,
On previous post,John's code work well for me to create a query on the fly.
But now there is a new wishes coming,I would like to be able to create a
field
in my query through my form or a combo box.But,one thing confused,the field
contains expression.I have prepare a new table as below:
1.ID ==============> Primary Key
2.Field Name.========> Text
3.Expression formula ===> Text ( expression string )

But I don't know what to do next.Hope somebody like to help
Thank's


John Spencer said:
That error means that there is something wrong with the sql statement.
Probably the Order by clause since I formgot to replace Vibration there;

When you got the error did you try copying the SQL statement into a
query and running it there? If you did you should have gotten a
parameter prompt for the unknown item.

Try the following

strSql = "SELECT TOP 10 " & vbCrLf & _
" Format([Date],""mmm"") AS [Month] " & vbCrLf & _
" , Format([Date],""yyyy"") AS [Year] " & vbCrLf & _
" , Model " & vbCrLf & _
" , Sum([" & Me.CboRejectItem & _
"]) AS [SumOf" & Me.CboRejectItem & "]" & vbCrLf & _
" INTO [The Worst 10 by Reject Item tbl] " & vbCrLf & _
" FROM [DC Fan Information Centre] " & vbCrLf & _
" WHERE Format([Date],""mmm"")=""" & Me.[cboMonth] & """" & vbCrLf & _
" AND Format([Date],""yyyy"")=""" & Me.[cboYear] & """ " & vbCrLf & _
" GROUP BY Format([Date],""mmm"") " & vbCrLf & _
" , Format([Date],""yyyy"") " & vbCrLf & _
" , Model " & vbCrLf & _
" ORDER BY Sum([" & Me.CboRejectItem & "]) DESC " & vbCrLf & _
" WITH OWNERACCESS OPTION;"



'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

The code works fine untill debugging line.But now access
sent:
Run time Error 3061
Too few parameters.Expected 1.

What should I add in anymore.


John Spencer said:
If Frame7 = 1 Then
Me.Visible = False

'Comment out the line for now
'DoCmd.SetWarnings False

'Comment out this line, it does nothing DoCmd.RunSQL ""

Dim strSQL as String 'Always declare your variables.


strSql = "SELECT TOP 10 " & vbCrLf & _
" Format([Date],""mmm"") AS [Month] " & vbCrLf & _
" , Format([Date],""yyyy"") AS [Year] " & vbCrLf & _
" , Model " & vbCrLf & _
" , Sum([" & Me.CboRejectItem & _
"]) AS [SumOf" & Me.CboRejectItem & "]" & vbCrLf & _
" INTO [The Worst 10 by Reject Item tbl] " & vbCrLf & _
" FROM [DC Fan Information Centre] " & vbCrLf & _
" WHERE Format([Date],""mmm"")=""" & Me.[cboMonth] & """" & vbCrLf & _
" AND Format([Date],""yyyy"")=""" & Me.[cboYear] & """ " & vbCrLf & _
" GROUP BY Format([Date],""mmm"") " & vbCrLf & _
" , Format([Date],""yyyy"") " & vbCrLf & _
" , Model " & vbCrLf & _
" ORDER BY Sum([Vibration]) DESC " & vbCrLf & _
" WITH OWNERACCESS OPTION;"

'Add the following to debug things.
Debug.Print StrSQL: STOP
'Check the sql string and see if it is valid.

CurrentDb().Execute strSql, dbFailOnError

DoCmd.SetWarnings True
DoCmd.OpenTable " The Worst 10 by Reject Item tbl", acNormal

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================


shiro wrote:
I had modified the code like below

Private Sub CmdOK_Click()
If Frame7 = 1 Then
Me.Visible = False
DoCmd.SetWarnings False
DoCmd.RunSQL ""

strSql = "SELECT TOP 10 " & vbCrLf & _
" Format([Date],""mmm"") AS [Month] " & vbCrLf & _
" , Format([Date],""yyyy"") AS [Year] " & vbCrLf & _
" , Model " & vbCrLf & _
" , Sum([" & Me.CboRejectItem & _
"]) AS [SumOf" & Me.CboRejectItem & "]" & vbCrLf & _
" INTO [The Worst 10 by Reject Item tbl] " & vbCrLf & _
" FROM [DC Fan Information Centre] " & vbCrLf & _
" WHERE Format([Date],""mmm"")=""" & Me.[cboMonth] & """" & vbCrLf & _
" AND Format([Date],""yyyy"")=""" & Me.[cboYear] & """ " & vbCrLf & _
" GROUP BY Format([Date],""mmm"") " & vbCrLf & _
" , Format([Date],""yyyy"") " & vbCrLf & _
" , Model " & vbCrLf & _
" ORDER BY Sum([Vibration]) DESC " & vbCrLf & _
" WITH OWNERACCESS OPTION;"

CurrentDb().Execute strSql, dbFailOnError

DoCmd.SetWarnings True
DoCmd.OpenTable " The Worst 10 by Reject Item tbl", acNormal

But,VB told me
Compile Error:
Variable not defined !

What's wrong John?


Probably the best way to do this would be to build the SQL statement on
the
fly.

And then use the button to build the SQL Statement and execute it.. The
following VERY ROUGH UNTESTED code snippet should get you started.

strSql = "SELECT TOP 10 " & vbCrLf & _
" Format([Date],""mmm"") AS [Month] " & vbCrLf & _
" , Format([Date],""yyyy"") AS [Year] " & vbCrLf & _
" , Model " & vbCrLf & _
" , Sum([" & Me.cboFieldName & _
"]) AS [SumOf" & Me.cboFieldName & "]" & vbCrLf & _
" INTO [The Worst 10 by Reject Item tbl] " & vbCrLf & _
" FROM [DC Fan Information Centre] " & vbCrLf & _
" WHERE Format([Date],""mmm"")=""" & Me.[cboMonth]) & """" & vbCrLf
Click to expand...
&
_
" AND Format([Date],""yyyy"")=""" & me.[cboYear] & """ " & vbCrLf & _
" GROUP BY Format([Date],""mmm"") " & vbCrLf & _
" , Format([Date],""yyyy"") " & vbCrLf & _
" , Model " & vbCrLf & _
" ORDER BY Sum([Vibration]) DESC " & vbCrLf & _
" WITH OWNERACCESS OPTION;"

Currentdb().Execute strSQL, dbFailOnError

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
.

Hi All,
I have a SQL like below;

SELECT TOP 10 Format([Date],"mmm") AS [Month], Format([Date],"yyyy") AS
[Year], [DC Fan Information Centre].Model, Sum([DC Fan Information
Centre].Vibration) AS SumOfVibration INTO [The Worst 10 by Reject Item
tbl]
FROM [DC Fan Information Centre]
GROUP BY Format([Date],"mmm"), Format([Date],"yyyy"), [DC Fan
Information
Centre].Model
HAVING (((Format([Date],"mmm"))=[Forms]![fltr Reject Rank By Reject
Item]![cboMonth]) AND ((Format([Date],"yyyy"))=[Forms]![fltr Reject Rank
By
Reject Item]![cboYear]))
ORDER BY Sum([DC Fan Information Centre].Vibration) DESC
WITH OWNERACCESS OPTION;

I want the field 'Vibration' can be replaced with one of another 10
fields
through
a combo box ( cbo box contains all of the fields name ) in form 'fltr
Reject Rank By Reject Item'
and then execute the SQL with a command button.Is it possible?

Hope somebody would like to advice.
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

strSQL ---> "Too few parameter,expected 1" 3
programatically re-open report, re-print it and then close it. 2
Repost : Mission to populate record through a make table query 2
Combo box date format changes when item selected from list 4
Query two subforms from 3 combo boxes 3
change format of dropdown list in combo box 5
SQL query to display values in a Combo box 2
Setting a date field using combo box 3

Top