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.