SQL Execution errer

J

Jim S

When I run the following code, I get "Run - Time Error
3061" "Too Few parameters" The code creates the make -
table query, but gives the error when the code tries to
execute the query and create the table. (Running the
query manually creates the table)

I have created many tables with code in then past with
few problems.

Any idea how get this query to execute by using code?

Jim


Sub CreateTb1()

'Create a SQL Make Table Query
'Create Table1 - Table of locations in the selected
records

Dim db As Database
Dim qdf As QueryDef
Dim A As String, B As String, C As String
Dim D As String, E As String, F As String
Dim G As String, H As String, I As String
Dim J As String, K As String, L As String
Dim M As String, N As String, O As String
Dim P As String
Dim QrySQL As String

'Pieces of SQL Statment
A = "SELECT qry_RawData.Location INTO Temp1"
B = "FROM qry_RawData"
C = "GROUP BY qry_RawData.Pedigree, qry_RawData.[Nesting
Group], qry_RawData.[EU Entry Number], qry_RawData.Rep,
qry_RawData.[EU Data Quality], qry_RawData.[EU SPPLOT
(list)], qry_RawData.[Expt Name],"
D = "qry_RawData.Location, qry_RawData.[Location Site
Code], qry_RawData.[EU Inventory ID], qry_RawData.[EU
Source Name], qry_RawData.[Site Short Name]"
E = "HAVING (((qry_RawData.Pedigree) Like "
F = " & [Forms]![frm_Main]![Combo6] & "
G = ") AND ((qry_RawData.[Nesting Group]) Like "
H = " & [Forms]![frm_Main]![Combo1] & "
I = ") AND ((qry_RawData.[EU Entry "
J = "Number])=[Forms]![frm_Main]![Combo16]) AND
((qry_RawData.Rep) Like "
K = " & [Forms]![frm_Main]![Combo18]) AND ((qry_RawData.
[EU Data Quality]) Like "
L = " & [Forms]![frm_Main]![Combo20]) AND ((qry_RawData.
[EU SPPLOT (list)])Like "
M = " & [Forms]![frm_Main]![Combo22]))"
N = "ORDER BY qry_RawData.[Nesting Group], qry_RawData.
[EU Entry Number], qry_RawData.Rep, qry_RawData.Location;"

O = A & Chr(10) & B & Chr(10) & C & D & Chr(10) & E & Chr
(34) & "*" & Chr(34) & F & Chr(34) & "*" & Chr(34) & G &
Chr(34) & "*" & Chr(34) & H & Chr(34) & "*" & Chr(34) & I
P = J & Chr(34) & "*" & Chr(34) & K & Chr(34) & "*" & Chr
(34) & L & Chr(34) & "*" & Chr(34) & M & Chr(10) & N

'Create SQL
QrySQL = O & P

'Create query
Set db = CurrentDb()
Set qdf = db.CreateQueryDef("qry_Temp1", QrySQL)
qdf.Execute
qdf.Close
db.Close
Set qdf = Nothing

'Delete Query "qry_Temp1"
'DoCmd.DeleteObject acQuery, "qry_Temp1"

End Sub
 
A

Allen Browne

Concatenate the value form the control into the string, e.g.:
"WHERE [MyField] = " & Forms!MyForm!MyControl & " AND ..."

Don't forget the delimiters: # for dates, and " for text. So if MyField is a
Text type field, you need:
"WHERE [MyField] = """ & Forms!MyForm!MyControl & """ AND ..."

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

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

Jim S said:
When I run the following code, I get "Run - Time Error
3061" "Too Few parameters" The code creates the make -
table query, but gives the error when the code tries to
execute the query and create the table. (Running the
query manually creates the table)

I have created many tables with code in then past with
few problems.

Any idea how get this query to execute by using code?

Jim


Sub CreateTb1()

'Create a SQL Make Table Query
'Create Table1 - Table of locations in the selected
records

Dim db As Database
Dim qdf As QueryDef
Dim A As String, B As String, C As String
Dim D As String, E As String, F As String
Dim G As String, H As String, I As String
Dim J As String, K As String, L As String
Dim M As String, N As String, O As String
Dim P As String
Dim QrySQL As String

'Pieces of SQL Statment
A = "SELECT qry_RawData.Location INTO Temp1"
B = "FROM qry_RawData"
C = "GROUP BY qry_RawData.Pedigree, qry_RawData.[Nesting
Group], qry_RawData.[EU Entry Number], qry_RawData.Rep,
qry_RawData.[EU Data Quality], qry_RawData.[EU SPPLOT
(list)], qry_RawData.[Expt Name],"
D = "qry_RawData.Location, qry_RawData.[Location Site
Code], qry_RawData.[EU Inventory ID], qry_RawData.[EU
Source Name], qry_RawData.[Site Short Name]"
E = "HAVING (((qry_RawData.Pedigree) Like "
F = " & [Forms]![frm_Main]![Combo6] & "
G = ") AND ((qry_RawData.[Nesting Group]) Like "
H = " & [Forms]![frm_Main]![Combo1] & "
I = ") AND ((qry_RawData.[EU Entry "
J = "Number])=[Forms]![frm_Main]![Combo16]) AND
((qry_RawData.Rep) Like "
K = " & [Forms]![frm_Main]![Combo18]) AND ((qry_RawData.
[EU Data Quality]) Like "
L = " & [Forms]![frm_Main]![Combo20]) AND ((qry_RawData.
[EU SPPLOT (list)])Like "
M = " & [Forms]![frm_Main]![Combo22]))"
N = "ORDER BY qry_RawData.[Nesting Group], qry_RawData.
[EU Entry Number], qry_RawData.Rep, qry_RawData.Location;"

O = A & Chr(10) & B & Chr(10) & C & D & Chr(10) & E & Chr
(34) & "*" & Chr(34) & F & Chr(34) & "*" & Chr(34) & G &
Chr(34) & "*" & Chr(34) & H & Chr(34) & "*" & Chr(34) & I
P = J & Chr(34) & "*" & Chr(34) & K & Chr(34) & "*" & Chr
(34) & L & Chr(34) & "*" & Chr(34) & M & Chr(10) & N

'Create SQL
QrySQL = O & P

'Create query
Set db = CurrentDb()
Set qdf = db.CreateQueryDef("qry_Temp1", QrySQL)
qdf.Execute
qdf.Close
db.Close
Set qdf = Nothing

'Delete Query "qry_Temp1"
'DoCmd.DeleteObject acQuery, "qry_Temp1"

End Sub
Click to expand...
 
J

Jim S.

Allan,

The code below creates a working query. As I mentioned
below, I can run the query that is created by this code.
I get the error when I try to ececute the query using
code.

This has always worked in the past:

Set db = CurrentDb()
Set qdf = db.CreateQueryDef("qry_Temp1", QrySQL)
qdf.Execute
qdf.Close
db.Close
Set qdf = Nothing

Jim
-----Original Message-----
Concatenate the value form the control into the string, e.g.:
"WHERE [MyField] = " & Forms!MyForm!MyControl & " AND ..."

Don't forget the delimiters: # for dates, and " for text. So if MyField is a
Text type field, you need:
"WHERE [MyField] = """ & Forms!MyForm!MyControl & """ AND ..."

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

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

When I run the following code, I get "Run - Time Error
3061" "Too Few parameters" The code creates the make -
table query, but gives the error when the code tries to
execute the query and create the table. (Running the
query manually creates the table)

I have created many tables with code in then past with
few problems.

Any idea how get this query to execute by using code?

Jim


Sub CreateTb1()

'Create a SQL Make Table Query
'Create Table1 - Table of locations in the selected
records

Dim db As Database
Dim qdf As QueryDef
Dim A As String, B As String, C As String
Dim D As String, E As String, F As String
Dim G As String, H As String, I As String
Dim J As String, K As String, L As String
Dim M As String, N As String, O As String
Dim P As String
Dim QrySQL As String

'Pieces of SQL Statment
A = "SELECT qry_RawData.Location INTO Temp1"
B = "FROM qry_RawData"
C = "GROUP BY qry_RawData.Pedigree, qry_RawData. [Nesting
Group], qry_RawData.[EU Entry Number], qry_RawData.Rep,
qry_RawData.[EU Data Quality], qry_RawData.[EU SPPLOT
(list)], qry_RawData.[Expt Name],"
D = "qry_RawData.Location, qry_RawData.[Location Site
Code], qry_RawData.[EU Inventory ID], qry_RawData.[EU
Source Name], qry_RawData.[Site Short Name]"
E = "HAVING (((qry_RawData.Pedigree) Like "
F = " & [Forms]![frm_Main]![Combo6] & "
G = ") AND ((qry_RawData.[Nesting Group]) Like "
H = " & [Forms]![frm_Main]![Combo1] & "
I = ") AND ((qry_RawData.[EU Entry "
J = "Number])=[Forms]![frm_Main]![Combo16]) AND
((qry_RawData.Rep) Like "
K = " & [Forms]![frm_Main]![Combo18]) AND ((qry_RawData.
[EU Data Quality]) Like "
L = " & [Forms]![frm_Main]![Combo20]) AND ((qry_RawData.
[EU SPPLOT (list)])Like "
M = " & [Forms]![frm_Main]![Combo22]))"
N = "ORDER BY qry_RawData.[Nesting Group], qry_RawData.
[EU Entry Number], qry_RawData.Rep, qry_RawData.Location;"

O = A & Chr(10) & B & Chr(10) & C & D & Chr(10) & E & Chr
(34) & "*" & Chr(34) & F & Chr(34) & "*" & Chr(34) & G &
Chr(34) & "*" & Chr(34) & H & Chr(34) & "*" & Chr(34) & I
P = J & Chr(34) & "*" & Chr(34) & K & Chr(34) & "*" & Chr
(34) & L & Chr(34) & "*" & Chr(34) & M & Chr(10) & N

'Create SQL
QrySQL = O & P

'Create query
Set db = CurrentDb()
Set qdf = db.CreateQueryDef("qry_Temp1", QrySQL)
qdf.Execute
qdf.Close
db.Close
Set qdf = Nothing

'Delete Query "qry_Temp1"
'DoCmd.DeleteObject acQuery, "qry_Temp1"

End Sub
Click to expand...


.
Click to expand...
 
A

Allen Browne

That is correct.

In the context of the query itself, the Expression Service is available to
resolve the reference to the form. In the context of the DAO code, the
Expression Service is not available. The reference to the form does not
resolve, and so it is treated as a parameter.

Solve the problem by writing the SQL statement as a string in your code,
instead of relying on the saved query.

An alternative solution is to supply each parameter in your code, e.g.:
Set qdf = db.CreateQueryDef("qry_Temp1", QrySQL)
qdf.Parameters("Forms!MyForm!MyControl") = Forms!MyForm!MyControl
qdf.Parameters("Forms!MyForm!Text1") = Forms!MyForm!Text1
'etc.
qdf.Execute

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

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

Jim S. said:
Allan,

The code below creates a working query. As I mentioned
below, I can run the query that is created by this code.
I get the error when I try to ececute the query using
code.

This has always worked in the past:

Set db = CurrentDb()
Set qdf = db.CreateQueryDef("qry_Temp1", QrySQL)
qdf.Execute
qdf.Close
db.Close
Set qdf = Nothing

Jim
-----Original Message-----
Concatenate the value form the control into the string, e.g.:
"WHERE [MyField] = " & Forms!MyForm!MyControl & " AND ..."

Don't forget the delimiters: # for dates, and " for text. So if MyField is a
Text type field, you need:
"WHERE [MyField] = """ & Forms!MyForm!MyControl & """ AND ..."


When I run the following code, I get "Run - Time Error
3061" "Too Few parameters" The code creates the make -
table query, but gives the error when the code tries to
execute the query and create the table. (Running the
query manually creates the table)

I have created many tables with code in then past with
few problems.

Any idea how get this query to execute by using code?

Jim


Sub CreateTb1()

'Create a SQL Make Table Query
'Create Table1 - Table of locations in the selected
records

Dim db As Database
Dim qdf As QueryDef
Dim A As String, B As String, C As String
Dim D As String, E As String, F As String
Dim G As String, H As String, I As String
Dim J As String, K As String, L As String
Dim M As String, N As String, O As String
Dim P As String
Dim QrySQL As String

'Pieces of SQL Statment
A = "SELECT qry_RawData.Location INTO Temp1"
B = "FROM qry_RawData"
C = "GROUP BY qry_RawData.Pedigree, qry_RawData. [Nesting
Group], qry_RawData.[EU Entry Number], qry_RawData.Rep,
qry_RawData.[EU Data Quality], qry_RawData.[EU SPPLOT
(list)], qry_RawData.[Expt Name],"
D = "qry_RawData.Location, qry_RawData.[Location Site
Code], qry_RawData.[EU Inventory ID], qry_RawData.[EU
Source Name], qry_RawData.[Site Short Name]"
E = "HAVING (((qry_RawData.Pedigree) Like "
F = " & [Forms]![frm_Main]![Combo6] & "
G = ") AND ((qry_RawData.[Nesting Group]) Like "
H = " & [Forms]![frm_Main]![Combo1] & "
I = ") AND ((qry_RawData.[EU Entry "
J = "Number])=[Forms]![frm_Main]![Combo16]) AND
((qry_RawData.Rep) Like "
K = " & [Forms]![frm_Main]![Combo18]) AND ((qry_RawData.
[EU Data Quality]) Like "
L = " & [Forms]![frm_Main]![Combo20]) AND ((qry_RawData.
[EU SPPLOT (list)])Like "
M = " & [Forms]![frm_Main]![Combo22]))"
N = "ORDER BY qry_RawData.[Nesting Group], qry_RawData.
[EU Entry Number], qry_RawData.Rep, qry_RawData.Location;"

O = A & Chr(10) & B & Chr(10) & C & D & Chr(10) & E & Chr
(34) & "*" & Chr(34) & F & Chr(34) & "*" & Chr(34) & G &
Chr(34) & "*" & Chr(34) & H & Chr(34) & "*" & Chr(34) & I
P = J & Chr(34) & "*" & Chr(34) & K & Chr(34) & "*" & Chr
(34) & L & Chr(34) & "*" & Chr(34) & M & Chr(10) & N

'Create SQL
QrySQL = O & P

'Create query
Set db = CurrentDb()
Set qdf = db.CreateQueryDef("qry_Temp1", QrySQL)
qdf.Execute
qdf.Close
db.Close
Set qdf = Nothing

'Delete Query "qry_Temp1"
'DoCmd.DeleteObject acQuery, "qry_Temp1"

End Sub
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

Need help in printing vba code 0
lost in error 3075 8
filtering a form based on the values of a subform 0
CREATE MANUAL HEADER LINE FOR CSV EXPORT 2
Do until Loop - make it STOP! 8
getting right string in table 5
Type mismatch error. 1
Error: Compile Error,ByRef argument type mismatch 0

Top