error 3061; to few parameters. the expected number = 2

[Phoenix]

Hi,

running this code i keep running into this error.

Here is the code (part of it):

strSQL = "SELECT * FROM qrydeelnemersnaarexcel WHERE
tblActiviteiten.act_project = '201040' AND tblDeelnemers.dn_actief = Yes;"

Set d = CurrentDb

Set rs = d.OpenRecordset(strSQL, dbOpenDynaset)

intMaxCol = rs.Fields.Count

If rs.RecordCount > 0 Then

MsgBox ("GEEN RECORDS AANWEZIG!")

rs.MoveLast: rs.MoveFirst

intMaxRow = rs.RecordCount

Set objXL = New Excel.Application

With objXL

.Visible = True

Set objWkb = .Workbooks.Add

Set objSht = objWkb.Worksheets(1)

For iCols = 0 To rs.Fields.Count - 1

.Cells(1, iCols + 1).Value = rs.Fields(iCols).Name

Next

.Range(.Cells(1, 1), .Cells(1, rs.Fields.Count)).Font.Bold =
True

With objSht

.Range("A2", .Cells(intMaxRow, intMaxCol)).CopyFromRecordset
rs

.Columns.AutoFit

.Columns.AutoFilter

End With

End With

objWkb.SaveAs ("F:\Administratie 2003\deelnemers\" & ProjectFileNaam
& ".xls")

objXL.Quit

End If

I tried a lot to get rid of it (if i keep the WHERE part out of strSQL then
it runs but i need to select a projectnumber (201040))



Also tried DAO and ADO but same result.



Pls help me?



Txs in advance, Michael

 

[Ken Reay]

Hi
You have asyntax error in your SQL
is dn_actief a boolean or a string?

if a string you need = 'Yes'

if a boolean try = True

Hope this helps

-----Original Message-----
Hi,

running this code i keep running into this error.

Here is the code (part of it):

strSQL = "SELECT * FROM qrydeelnemersnaarexcel WHERE
tblActiviteiten.act_project = '201040' AND

tblDeelnemers.dn_actief = Yes;"

Set d = CurrentDb

Set rs = d.OpenRecordset(strSQL, dbOpenDynaset)

intMaxCol = rs.Fields.Count

If rs.RecordCount > 0 Then

MsgBox ("GEEN RECORDS AANWEZIG!")

rs.MoveLast: rs.MoveFirst

intMaxRow = rs.RecordCount

Set objXL = New Excel.Application

With objXL

.Visible = True

Set objWkb = .Workbooks.Add

Set objSht = objWkb.Worksheets(1)

For iCols = 0 To rs.Fields.Count - 1

.Cells(1, iCols + 1).Value = rs.Fields (iCols).Name

Next

.Range(.Cells(1, 1), .Cells(1, rs.Fields.Count)).Font.Bold =
True

With objSht

.Range("A2", .Cells(intMaxRow, intMaxCol)).CopyFromRecordset
rs

.Columns.AutoFit

.Columns.AutoFilter

End With

End With

objWkb.SaveAs ("F:\Administratie 2003

\deelnemers\" & ProjectFileNaam

 

[Howard Brody]

You need to include tblActiviteiten and tblDeelnemers in
your FROM statement or change your criteria to apply to
qrydeelnemersnaarexcel.

Hope this helps!

Howard Brody

-----Original Message-----
Hi,

running this code i keep running into this error.

Here is the code (part of it):

strSQL = "SELECT * FROM qrydeelnemersnaarexcel WHERE
tblActiviteiten.act_project = '201040' AND

tblDeelnemers.dn_actief = Yes;"

Set d = CurrentDb

Set rs = d.OpenRecordset(strSQL, dbOpenDynaset)

intMaxCol = rs.Fields.Count

If rs.RecordCount > 0 Then

MsgBox ("GEEN RECORDS AANWEZIG!")

rs.MoveLast: rs.MoveFirst

intMaxRow = rs.RecordCount

Set objXL = New Excel.Application

With objXL

.Visible = True

Set objWkb = .Workbooks.Add

Set objSht = objWkb.Worksheets(1)

For iCols = 0 To rs.Fields.Count - 1

.Cells(1, iCols + 1).Value = rs.Fields (iCols).Name

Next

.Range(.Cells(1, 1), .Cells(1, rs.Fields.Count)).Font.Bold =
True

With objSht

.Range("A2", .Cells(intMaxRow, intMaxCol)).CopyFromRecordset
rs

.Columns.AutoFit

.Columns.AutoFilter

End With

End With

objWkb.SaveAs ("F:\Administratie 2003

\deelnemers\" & ProjectFileNaam

 

[Phoenix]

Yep,

Right-on!!

Txs very much. I was stuck here for quit some time now -)

Ps: 1 thing, do you know the syntax when i want to replace '201040' with a
variable?

Again, txs

 

[PC Datasheet]

Dim ProjectNum As Srring
ProjectNum = "201040"

.............tblActiviteiten.act_project = ProjectNum AND...........


--
PC Datasheet
A Resource for Access, Excel and Word Applications
(e-mail address removed)
www.pcdatasheet.com

· Design and basic development for new applications
· Additions, Modifications and "Fixes" for existing applications
· Mentoring for do-it-yourselfers who want guidance
· Complete application design and development
· Applications Using Palm Pilot To Collect Data And
Synchronize The Data Back To Access Or Excel

 

[Phoenix]

Txs,

But thats exactly the way i have it now and that doesnt work -(

weird but true

I can see it when i type the name and it doesnt change capitals when i leave
the line.

Any other suggestions?

I think it has something to do cause it is in a SQL statement or something.

Michael