OpenRecordset with SQL statement conteining JOINS

N

Nacho

I have the following code.

When ever i try to open a recorset with a SQL statement using LEFT JOIN, i
get the 3061 error, "too few parameters. Expected 1"

the code:
----------------------------------------------
Function BaseDST()
Dim DBNew As Database
Dim DBVar As Database
Dim Recset As Recordset
Dim Recset2 As Recordset
Dim sSQL As String
Dim i, n As Integer
Dim vflag, vflag2, vflag3 As Boolean

Set DBNew = CurrentDb
'Set DBVar = CurrentDb
sSQL = "SELECT [Base DST].[Order Number], [Base DST].[RFS] as RFS, [Base DST
versión anterior].[RFS date] AS RFSant,"
sSQL = sSQL & " [Base DST].[Plant Month] As PM, [Base DST versión
anterior].[Plant Month] AS PMant, [Base DST].[Tons] AS tons,"
sSQL = sSQL & " [Base DST versión anterior].[Tons] as tonsant"
sSQL = sSQL & " FROM [Base DST] LEFT JOIN [Base DST versión anterior] ON
[Base DST].[Order Number] = [Base DST versión anterior].[Order Number]"
sSQL = sSQL & " WHERE (([Base DST versión anterior].[Mill] <> Null) AND
([Base DST].[Source Type] = 'PREVISION'))"
'MsgBox sSQL
Set Recset = DBNew.OpenRecordset(sSQL)
Set Recset2 = DBVar.OpenRecordset("Variacion de previsiones")
Recset.MoveFirst
Do While Not Recset.EOF
If Recset.Fields("RFS") <> Recset.Fields("RFSant") Then vflag = True
If Recset.Fields("PM") <> Recset.Fields("PMant") Then vflag2 = True
If Recset.Fields("tons") <> Recset.Fields("tonsant") Then vflag3 = True
If vflag = True Or vflag2 = True Or vflag3 = True Then
Recset2.AddNew
Recset2.Fields("Order Number") = Recset.Fields("Order Number")
Recset2.Fields("RFS") = vflag
Recset2.Fields("Plant Month") = vflag2
Recset2.Fields("Tons") = vflag3
Recset2.Update
End If
vflag = False
vflag2 = False
vflag3 = False
Recset.MoveNext
Loop
Set DBNew = Nothing
Set DBVar = Nothing
End Function
 
D

Dirk Goldgar

Nacho said:
I have the following code.

When ever i try to open a recorset with a SQL statement using LEFT
JOIN, i get the 3061 error, "too few parameters. Expected 1"

the code:
----------------------------------------------
Function BaseDST()
Dim DBNew As Database
Dim DBVar As Database
Dim Recset As Recordset
Dim Recset2 As Recordset
Dim sSQL As String
Dim i, n As Integer
Dim vflag, vflag2, vflag3 As Boolean

Set DBNew = CurrentDb
'Set DBVar = CurrentDb
sSQL = "SELECT [Base DST].[Order Number], [Base DST].[RFS] as RFS,
[Base DST versión anterior].[RFS date] AS RFSant,"
sSQL = sSQL & " [Base DST].[Plant Month] As PM, [Base DST versión
anterior].[Plant Month] AS PMant, [Base DST].[Tons] AS tons,"
sSQL = sSQL & " [Base DST versión anterior].[Tons] as tonsant"
sSQL = sSQL & " FROM [Base DST] LEFT JOIN [Base DST versión anterior]
ON [Base DST].[Order Number] = [Base DST versión anterior].[Order
Number]" sSQL = sSQL & " WHERE (([Base DST versión anterior].[Mill]
<> Null) AND ([Base DST].[Source Type] = 'PREVISION'))"
'MsgBox sSQL
Set Recset = DBNew.OpenRecordset(sSQL)
Set Recset2 = DBVar.OpenRecordset("Variacion de previsiones")
Recset.MoveFirst
Do While Not Recset.EOF
If Recset.Fields("RFS") <> Recset.Fields("RFSant") Then vflag =
True If Recset.Fields("PM") <> Recset.Fields("PMant") Then vflag2
= True If Recset.Fields("tons") <> Recset.Fields("tonsant") Then
vflag3 = True If vflag = True Or vflag2 = True Or vflag3 = True
Then Recset2.AddNew
Recset2.Fields("Order Number") = Recset.Fields("Order Number")
Recset2.Fields("RFS") = vflag
Recset2.Fields("Plant Month") = vflag2
Recset2.Fields("Tons") = vflag3
Recset2.Update
End If
vflag = False
vflag2 = False
vflag3 = False
Recset.MoveNext
Loop
Set DBNew = Nothing
Set DBVar = Nothing
End Function
Click to expand...

Unless you are referring to a form control -- which I don't see -- the
odds are that you have a field name misspelled. Check all the names you
use against the actual field names in the tables.

Note: this criterion in your WHERE clause:

[Base DST versión anterior].[Mill] <> Null

will not work, because comparisons to Null yield a Null result. Use
this instead:

[Base DST versión anterior].[Mill] Is Not Null
 
G

Gerald Stanley

The likeliest explanation is that there is an error in the SQL. You can
follow the following steps to place the code into a new query which you can
then run to verify the syntax.

1. Highlight the lines that produce the SQL and copy to the clipboard.
2. Open a new query in Design View but do not select any tables. The Design
view should then be in SQL mode and you can paste the clipboard into the
Design area.
3. Remove all the non SQL vocabulary (e.g. sSQL = " etc).
4. Click on the Run Icon.
5. The SQL will be validated and any problems highlighted. Otherwise, the
results will be displayed.

Hope This Helps
Gerald Stanley MCSD
 
N

Nacho

Thanks very much ! there was a spelling mistake in SQL expression

Thanks again,
Ignacio

Gerald Stanley said:
The likeliest explanation is that there is an error in the SQL. You can
follow the following steps to place the code into a new query which you can
then run to verify the syntax.

1. Highlight the lines that produce the SQL and copy to the clipboard.
2. Open a new query in Design View but do not select any tables. The Design
view should then be in SQL mode and you can paste the clipboard into the
Design area.
3. Remove all the non SQL vocabulary (e.g. sSQL = " etc).
4. Click on the Run Icon.
5. The SQL will be validated and any problems highlighted. Otherwise, the
results will be displayed.

Hope This Helps
Gerald Stanley MCSD

Nacho said:
I have the following code.

When ever i try to open a recorset with a SQL statement using LEFT JOIN, i
get the 3061 error, "too few parameters. Expected 1"

the code:
----------------------------------------------
Function BaseDST()
Dim DBNew As Database
Dim DBVar As Database
Dim Recset As Recordset
Dim Recset2 As Recordset
Dim sSQL As String
Dim i, n As Integer
Dim vflag, vflag2, vflag3 As Boolean

Set DBNew = CurrentDb
'Set DBVar = CurrentDb
sSQL = "SELECT [Base DST].[Order Number], [Base DST].[RFS] as RFS, [Base DST
versión anterior].[RFS date] AS RFSant,"
sSQL = sSQL & " [Base DST].[Plant Month] As PM, [Base DST versión
anterior].[Plant Month] AS PMant, [Base DST].[Tons] AS tons,"
sSQL = sSQL & " [Base DST versión anterior].[Tons] as tonsant"
sSQL = sSQL & " FROM [Base DST] LEFT JOIN [Base DST versión anterior] ON
[Base DST].[Order Number] = [Base DST versión anterior].[Order Number]"
sSQL = sSQL & " WHERE (([Base DST versión anterior].[Mill] <> Null) AND
([Base DST].[Source Type] = 'PREVISION'))"
'MsgBox sSQL
Set Recset = DBNew.OpenRecordset(sSQL)
Set Recset2 = DBVar.OpenRecordset("Variacion de previsiones")
Recset.MoveFirst
Do While Not Recset.EOF
If Recset.Fields("RFS") <> Recset.Fields("RFSant") Then vflag = True
If Recset.Fields("PM") <> Recset.Fields("PMant") Then vflag2 = True
If Recset.Fields("tons") <> Recset.Fields("tonsant") Then vflag3 = True
If vflag = True Or vflag2 = True Or vflag3 = True Then
Recset2.AddNew
Recset2.Fields("Order Number") = Recset.Fields("Order Number")
Recset2.Fields("RFS") = vflag
Recset2.Fields("Plant Month") = vflag2
Recset2.Fields("Tons") = vflag3
Recset2.Update
End If
vflag = False
vflag2 = False
vflag3 = False
Recset.MoveNext
Loop
Set DBNew = Nothing
Set DBVar = Nothing
End Function
Click to expand...
Click to expand...
 
N

Nacho

you were right, the condition now work, but there was also a spelling mistake,

thanks!!!

Ignacio

Dirk Goldgar said:
Nacho said:
I have the following code.

When ever i try to open a recorset with a SQL statement using LEFT
JOIN, i get the 3061 error, "too few parameters. Expected 1"

the code:
----------------------------------------------
Function BaseDST()
Dim DBNew As Database
Dim DBVar As Database
Dim Recset As Recordset
Dim Recset2 As Recordset
Dim sSQL As String
Dim i, n As Integer
Dim vflag, vflag2, vflag3 As Boolean

Set DBNew = CurrentDb
'Set DBVar = CurrentDb
sSQL = "SELECT [Base DST].[Order Number], [Base DST].[RFS] as RFS,
[Base DST versión anterior].[RFS date] AS RFSant,"
sSQL = sSQL & " [Base DST].[Plant Month] As PM, [Base DST versión
anterior].[Plant Month] AS PMant, [Base DST].[Tons] AS tons,"
sSQL = sSQL & " [Base DST versión anterior].[Tons] as tonsant"
sSQL = sSQL & " FROM [Base DST] LEFT JOIN [Base DST versión anterior]
ON [Base DST].[Order Number] = [Base DST versión anterior].[Order
Number]" sSQL = sSQL & " WHERE (([Base DST versión anterior].[Mill]
<> Null) AND ([Base DST].[Source Type] = 'PREVISION'))"
'MsgBox sSQL
Set Recset = DBNew.OpenRecordset(sSQL)
Set Recset2 = DBVar.OpenRecordset("Variacion de previsiones")
Recset.MoveFirst
Do While Not Recset.EOF
If Recset.Fields("RFS") <> Recset.Fields("RFSant") Then vflag =
True If Recset.Fields("PM") <> Recset.Fields("PMant") Then vflag2
= True If Recset.Fields("tons") <> Recset.Fields("tonsant") Then
vflag3 = True If vflag = True Or vflag2 = True Or vflag3 = True
Then Recset2.AddNew
Recset2.Fields("Order Number") = Recset.Fields("Order Number")
Recset2.Fields("RFS") = vflag
Recset2.Fields("Plant Month") = vflag2
Recset2.Fields("Tons") = vflag3
Recset2.Update
End If
vflag = False
vflag2 = False
vflag3 = False
Recset.MoveNext
Loop
Set DBNew = Nothing
Set DBVar = Nothing
End Function
Click to expand...

Unless you are referring to a form control -- which I don't see -- the
odds are that you have a field name misspelled. Check all the names you
use against the actual field names in the tables.

Note: this criterion in your WHERE clause:

[Base DST versión anterior].[Mill] <> Null

will not work, because comparisons to Null yield a Null result. Use
this instead:

[Base DST versión anterior].[Mill] Is Not Null

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
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

Using SQL Server Application Roles with Linked Tables 6
Problems with the Close statement not releasing the database fastenough. 4
SQL statement works in a query, but not in VBA 5
Stubborn Array won't Populate from single ADODB.Connection Query 4
Problem with Excel VBA 0
Object Variable or With Block Variable Not Set 3
Need assistance with secondary axis in Excel 2007 0
Help ..Update subform control2 with results from SELECT SQL based on subform control1 2

Top