|
 |
access thread: Query-By-Form SQL builer question - I'm stumped!
Message #1 by "Aaron" <goldenrugs@a...> on Mon, 23 Sep 2002 18:48:51
|
|
Thank you for looking at this message!
I am trying to use QBF to build a dynamic SQL statement to query a
database. I am modeling the example in the Wrox book Beg Access 2000 VBA.
I think there is a problem with the structure of the SQL statement as it
gives me a 'Run-Time Error: '3061' Too few parameters. Expected 1''
error.
I cannot track down the error because the debugger highlights the error on
the line that states: qdfAction.Execute dbFailOnError in
the "BuildResultsTable" function. Both functions are listed below. If you
need any additional code I will be happy to supply it for you.
Thank you in advance for any assistance you can offer as I am a beginner
with both Access and SQL.
Sincerely,
Aaron
--------------------------------------
Function BuildSQLString(sSQL As String)
Dim sSELECT As String
Dim sFROM As String
Dim sWHERE As String
Dim sORDERBY As String
sSELECT = "r.DateSold, r.Design, r.Material, r.Wft, r.Win, r.Lft, r.Lin,
r.Notes, r.SalePrice, r.CountryCode, r.InvNum, r.PurchasePrice,
r.PurchasePriceExt, r.PurchasePrice, r.RetailAdj, c.FName, c.LName,
o.Country, s.SalesPerson"
sFROM = "tblCustomer as c INNER JOIN (SalesPerson as s INNER JOIN (Country
as o INNER JOIN tblRug as r ON o.CountryCode=r.CountryCode) ON
s.SalesPersonID = r.SalesPersonID) ON c.CustomerID = r.CustomerID"
sWHERE = "r.Status = 'sold'"
sORDERBY = "r.DateSold, s.SalesPersonID"
If chkDate = True Then
If Not IsNull(txtDateFrom) Then
sWHERE = sWHERE & " AND r.TransDate >= " & "#" &
Format$(txtDateFrom, "mm/dd/yy") & "#"
End If
If Not IsNull(txtDateTo) Then
sWHERE = sWHERE & " AND r.TransDate <= " & "#" &
Format$(txtDateTo, "mm/dd/yy") & "#"
End If
End If
If chkSalesman = True Then
sWHERE = sWHERE & " AND r.SalesmanID = " & "'" & cboSalesman & "'"
End If
If chkRugSize = True Then
sWHERE = sWHERE & " AND r.Category = " & "'" & cboRugSize & "'"
End If
If chkCustomerLevel = True Then
sWHERE = sWHERE & " AND c.CustomerLevel = " & "'" & cboCustomerLevel
& "'"
End If
If chkOrigin = True Then
sWHERE = sWHERE & " AND r.CountryCode = " & "'" & cboOrigin & "'"
End If
If chkNoCali = True Then
sWHERE = sWHERE & " AND c.State <> 'ca'"
End If
sSQL = "SELECT " & sSELECT & " FROM " & sFROM & " WHERE " & sWHERE & "
ORDER BY " & sORDERBY & ";"
BuildSQLString = True
End Function
--------------------------------------------
Function BuildResultsTable(sSQL As String, _
sTableName As String, _
lRecordsAffected As Long) As Boolean
Dim db As database
Dim qdfAction As QueryDef
Set db = CurrentDb
On Error Resume Next
db.TableDefs.Delete sTableName
On Error GoTo 0
sSQL = Replace(sSQL, " FROM ", " INTO " & sTableName & " FROM ")
Set qdfAction = db.CreateQueryDef("", sSQL)
qdfAction.Execute dbFailOnError
lRecordsAffected = qdfAction.RecordsAffected
qdfAction.Close
BuildResultsTable = True
End Function
Message #2 by "Brian Skelton" <braxis@b...> on Tue, 24 Sep 2002 11:17:27
|
|
Aaron
Not an answer I'm afraid, but a useful tip when dealing with problems like
these
1) Put a breakpoint on the line thats failing, then run your code
2) In the Immediate window type '?sSQL' (without the quotes) this will
show you the text that sSQL holds
3) Have a look at the output - it may be obvious what the problem is.
3) If not, copy the text, go back to the database and create a new, blank,
query. Change the view of the query to SQL and paste the text of sSQL in.
4) Try and change the query view back to design. If sSQL is basically
sound,this will allow you to look at your SQL statement in the familiar
query design grid.
5) If this doesn't work, try executing the query. Hopefully you'll receive
a helpful error message!
Hope this helps
Brian
> Thank you for looking at this message!
I> am trying to use QBF to build a dynamic SQL statement to query a
d> atabase. I am modeling the example in the Wrox book Beg Access 2000 VBA.
I> think there is a problem with the structure of the SQL statement as it
g> ives me a 'Run-Time Error: '3061' Too few parameters. Expected 1''
e> rror.
I> cannot track down the error because the debugger highlights the error
on
t> he line that states: qdfAction.Execute dbFailOnError in
t> he "BuildResultsTable" function. Both functions are listed below. If
you
n> eed any additional code I will be happy to supply it for you.
T> hank you in advance for any assistance you can offer as I am a beginner
w> ith both Access and SQL.
S> incerely,
A> aron
-> -------------------------------------
F> unction BuildSQLString(sSQL As String)
> Dim sSELECT As String
D> im sFROM As String
D> im sWHERE As String
D> im sORDERBY As String
> sSELECT = "r.DateSold, r.Design, r.Material, r.Wft, r.Win, r.Lft, r.Lin,
r> .Notes, r.SalePrice, r.CountryCode, r.InvNum, r.PurchasePrice,
r> .PurchasePriceExt, r.PurchasePrice, r.RetailAdj, c.FName, c.LName,
o> .Country, s.SalesPerson"
s> FROM = "tblCustomer as c INNER JOIN (SalesPerson as s INNER JOIN
(Country
a> s o INNER JOIN tblRug as r ON o.CountryCode=r.CountryCode) ON
s> .SalesPersonID = r.SalesPersonID) ON c.CustomerID = r.CustomerID"
s> WHERE = "r.Status = 'sold'"
s> ORDERBY = "r.DateSold, s.SalesPersonID"
> If chkDate = True Then
> If Not IsNull(txtDateFrom) Then
> sWHERE = sWHERE & " AND r.TransDate >= " & "#" &
F> ormat$(txtDateFrom, "mm/dd/yy") & "#"
> End If
> If Not IsNull(txtDateTo) Then
> sWHERE = sWHERE & " AND r.TransDate <= " & "#" &
F> ormat$(txtDateTo, "mm/dd/yy") & "#"
> End If
E> nd If
> If chkSalesman = True Then
> sWHERE = sWHERE & " AND r.SalesmanID = " & "'" & cboSalesman & "'"
E> nd If
> If chkRugSize = True Then
> sWHERE = sWHERE & " AND r.Category = " & "'" & cboRugSize & "'"
E> nd If
> If chkCustomerLevel = True Then
> sWHERE = sWHERE & " AND c.CustomerLevel = " & "'" & cboCustomerLevel
&> "'"
E> nd If
> If chkOrigin = True Then
> sWHERE = sWHERE & " AND r.CountryCode = " & "'" & cboOrigin & "'"
E> nd If
> If chkNoCali = True Then
> sWHERE = sWHERE & " AND c.State <> 'ca'"
E> nd If
>
s> SQL = "SELECT " & sSELECT & " FROM " & sFROM & " WHERE " & sWHERE & "
O> RDER BY " & sORDERBY & ";"
> BuildSQLString = True
>
E> nd Function
-> -------------------------------------------
F> unction BuildResultsTable(sSQL As String, _
> sTableName As String, _
> lRecordsAffected As Long) As Boolean
> Dim db As database
D> im qdfAction As QueryDef
> Set db = CurrentDb
> On Error Resume Next
d> b.TableDefs.Delete sTableName
O> n Error GoTo 0
> sSQL = Replace(sSQL, " FROM ", " INTO " & sTableName & " FROM ")
S> et qdfAction = db.CreateQueryDef("", sSQL)
q> dfAction.Execute dbFailOnError
l> RecordsAffected = qdfAction.RecordsAffected
q> dfAction.Close
> BuildResultsTable = True
> End Function
Message #3 by "Vladimiro Cerni" <vladimiro.cerni@t...> on Wed, 25 Sep 2002 07:31:46
|
|
> Thank you for looking at this message!
I> am trying to use QBF to build a dynamic SQL statement to query a
d> atabase. I am modeling the example in the Wrox book Beg Access 2000 VBA.
I> think there is a problem with the structure of the SQL statement as it
g> ives me a 'Run-Time Error: '3061' Too few parameters. Expected 1''
e> rror.
I> cannot track down the error because the debugger highlights the error
on
t> he line that states: qdfAction.Execute dbFailOnError in
t> he "BuildResultsTable" function. Both functions are listed below. If
you
n> eed any additional code I will be happy to supply it for you.
T> hank you in advance for any assistance you can offer as I am a beginner
w> ith both Access and SQL.
S> incerely,
A> aron
-> -------------------------------------
F> unction BuildSQLString(sSQL As String)
> Dim sSELECT As String
D> im sFROM As String
D> im sWHERE As String
D> im sORDERBY As String
> sSELECT = "r.DateSold, r.Design, r.Material, r.Wft, r.Win, r.Lft, r.Lin,
r> .Notes, r.SalePrice, r.CountryCode, r.InvNum, r.PurchasePrice,
r> .PurchasePriceExt, r.PurchasePrice, r.RetailAdj, c.FName, c.LName,
o> .Country, s.SalesPerson"
s> FROM = "tblCustomer as c INNER JOIN (SalesPerson as s INNER JOIN
(Country
a> s o INNER JOIN tblRug as r ON o.CountryCode=r.CountryCode) ON
s> .SalesPersonID = r.SalesPersonID) ON c.CustomerID = r.CustomerID"
s> WHERE = "r.Status = 'sold'"
s> ORDERBY = "r.DateSold, s.SalesPersonID"
> If chkDate = True Then
> If Not IsNull(txtDateFrom) Then
> sWHERE = sWHERE & " AND r.TransDate >= " & "#" &
F> ormat$(txtDateFrom, "mm/dd/yy") & "#"
> End If
> If Not IsNull(txtDateTo) Then
> sWHERE = sWHERE & " AND r.TransDate <= " & "#" &
F> ormat$(txtDateTo, "mm/dd/yy") & "#"
> End If
E> nd If
> If chkSalesman = True Then
> sWHERE = sWHERE & " AND r.SalesmanID = " & "'" & cboSalesman & "'"
E> nd If
> If chkRugSize = True Then
> sWHERE = sWHERE & " AND r.Category = " & "'" & cboRugSize & "'"
E> nd If
> If chkCustomerLevel = True Then
> sWHERE = sWHERE & " AND c.CustomerLevel = " & "'" & cboCustomerLevel
&> "'"
E> nd If
> If chkOrigin = True Then
> sWHERE = sWHERE & " AND r.CountryCode = " & "'" & cboOrigin & "'"
E> nd If
> If chkNoCali = True Then
> sWHERE = sWHERE & " AND c.State <> 'ca'"
E> nd If
>
s> SQL = "SELECT " & sSELECT & " FROM " & sFROM & " WHERE " & sWHERE & "
O> RDER BY " & sORDERBY & ";"
> BuildSQLString = True
>
E> nd Function
-> -------------------------------------------
F> unction BuildResultsTable(sSQL As String, _
> sTableName As String, _
> lRecordsAffected As Long) As Boolean
> Dim db As database
D> im qdfAction As QueryDef
> Set db = CurrentDb
> On Error Resume Next
d> b.TableDefs.Delete sTableName
O> n Error GoTo 0
> sSQL = Replace(sSQL, " FROM ", " INTO " & sTableName & " FROM ")
S> et qdfAction = db.CreateQueryDef("", sSQL)
q> dfAction.Execute dbFailOnError
l> RecordsAffected = qdfAction.RecordsAffected
q> dfAction.Close
> BuildResultsTable = True
> End Function
Message #4 by "Aaron" <goldenrugs@a...> on Sat, 28 Sep 2002 09:07:17
|
|
Brian. If you get this late message, thanks for the suggestion to use the
constructed SQL string in the SQL view of the query builder. i am not
very experienced with access but was able to run down the problem. the
sql string was not constructing correctly.
thanks again!
aaron
> Thank you for looking at this message!
I> am trying to use QBF to build a dynamic SQL statement to query a
d> atabase. I am modeling the example in the Wrox book Beg Access 2000
VBA.
I> think there is a problem with the structure of the SQL statement as
it
g> ives me a 'Run-Time Error: '3061' Too few parameters. Expected 1''
e> rror.
I> cannot track down the error because the debugger highlights the error
on
t> he line that states: qdfAction.Execute dbFailOnError in
t> he "BuildResultsTable" function. Both functions are listed below. If
you
n> eed any additional code I will be happy to supply it for you.
T> hank you in advance for any assistance you can offer as I am a
beginner
w> ith both Access and SQL.
S> incerely,
A> aron
-> -------------------------------------
F> unction BuildSQLString(sSQL As String)
> Dim sSELECT As String
D> im sFROM As String
D> im sWHERE As String
D> im sORDERBY As String
> sSELECT = "r.DateSold, r.Design, r.Material, r.Wft, r.Win, r.Lft,
r.Lin,
r> .Notes, r.SalePrice, r.CountryCode, r.InvNum, r.PurchasePrice,
r> .PurchasePriceExt, r.PurchasePrice, r.RetailAdj, c.FName, c.LName,
o> .Country, s.SalesPerson"
s> FROM = "tblCustomer as c INNER JOIN (SalesPerson as s INNER JOIN
(Country
a> s o INNER JOIN tblRug as r ON o.CountryCode=r.CountryCode) ON
s> .SalesPersonID = r.SalesPersonID) ON c.CustomerID = r.CustomerID"
s> WHERE = "r.Status = 'sold'"
s> ORDERBY = "r.DateSold, s.SalesPersonID"
> If chkDate = True Then
> If Not IsNull(txtDateFrom) Then
> sWHERE = sWHERE & " AND r.TransDate >= " & "#" &
F> ormat$(txtDateFrom, "mm/dd/yy") & "#"
> End If
> If Not IsNull(txtDateTo) Then
> sWHERE = sWHERE & " AND r.TransDate <= " & "#" &
F> ormat$(txtDateTo, "mm/dd/yy") & "#"
> End If
E> nd If
> If chkSalesman = True Then
> sWHERE = sWHERE & " AND r.SalesmanID = " & "'" & cboSalesman & "'"
E> nd If
> If chkRugSize = True Then
> sWHERE = sWHERE & " AND r.Category = " & "'" & cboRugSize & "'"
E> nd If
> If chkCustomerLevel = True Then
> sWHERE = sWHERE & " AND c.CustomerLevel = " & "'" &
cboCustomerLevel
&> "'"
E> nd If
> If chkOrigin = True Then
> sWHERE = sWHERE & " AND r.CountryCode = " & "'" & cboOrigin & "'"
E> nd If
> If chkNoCali = True Then
> sWHERE = sWHERE & " AND c.State <> 'ca'"
E> nd If
>
s> SQL = "SELECT " & sSELECT & " FROM " & sFROM & " WHERE " & sWHERE & "
O> RDER BY " & sORDERBY & ";"
> BuildSQLString = True
>
E> nd Function
-> -------------------------------------------
F> unction BuildResultsTable(sSQL As String, _
> sTableName As String, _
> lRecordsAffected As Long) As Boolean
> Dim db As database
D> im qdfAction As QueryDef
> Set db = CurrentDb
> On Error Resume Next
d> b.TableDefs.Delete sTableName
O> n Error GoTo 0
> sSQL = Replace(sSQL, " FROM ", " INTO " & sTableName & " FROM ")
S> et qdfAction = db.CreateQueryDef("", sSQL)
q> dfAction.Execute dbFailOnError
l> RecordsAffected = qdfAction.RecordsAffected
q> dfAction.Close
> BuildResultsTable = True
> End Function
|
|
|
View Cart
