error 3061 in recordset to less parameters

[apestaart]

Hallo,
Again I have problems with setting a recordset depending of a SQL statement.
I am asking for 2 parameters (month and station) to be used in the where part of my SQL statement.
I receive an error 3061 (to less parameters expected at least 2). Who can help me on this?
The code:

    Dim stDocName As String
    Dim rst As Recordset, strSQL As String, strStation As String, KMaand As Date
    
    strStation = InputBox("Welk station?")
    KMaand = InputBox("Welke maand?")
    
   
    
strSQL = "SELECT [tabGewerkte uren].MaandID AS [tabGewerkte uren_MaandID], [tabGewerkte uren].RelID, [tabGewerkte uren].[Totaal uren], TabExtern.StationexID, [tabGewerkte uren].Maand, TabExtern.[Externe uren], TabExtern.MaandID AS TabExtern_MaandID, TabStations.Station, TabStations.StationID, [Naam Query].Naam FROM ((TabRegios INNER JOIN TabStations ON TabRegios.RegioID = TabStations.RegioID) INNER JOIN (Werknemers INNER JOIN [Naam Query] ON Werknemers.RelID = [Naam Query].RelID) ON TabStations.StationID = Werknemers.StationID) INNER JOIN ([tabGewerkte uren] LEFT JOIN TabExtern ON [tabGewerkte uren].MaandID = TabExtern.MaandID) ON Werknemers.RelID = [tabGewerkte uren].RelID WHERE ((([tabGewerkte uren].Maand)=KMaand) AND ((TabStations.Station)=strStation));"
Set rst = CurrentDb.OpenRecordset(strSQL)

regards Apestaart

 

[PHV]

... WHERE [tabGewerkte uren].Maand='" & KMaand & "' AND TabStations.Station='" & strStation & "'"

If either Maand or Station is defined as numeric then get rid of the corresponding single quotes.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[jadams0173]

you may try this. untested

Dates usually need to be enclosed in # signs and strings usually in single quotes ' strVariable '

WHERE ((([tabGewerkte uren].Maand)=#" & KMaand & "#) AND ((TabStations.Station)='" & strStation & "'))"

 

[jadams0173]

PHV,
Since KMaand is declared as date would it need the #?

 

[PHV]

jadams, you're right, I didn't pay attention to the Dim instruction :~/
... WHERE [tabGewerkte uren].Maand=#" & Format(KMaand, "yyyy-mm-dd") & "# AND TabStations.Station='" & strStation & "'"

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[apestaart]

Dear PHV and jadams0173 ,
I tried the code you have given me.
I needed to change it a little bit because I use a date format for Maand "mmmm yyyy"
I solved the "Station" part in the where statement in an other way, so that leaves the code underneith.

Private Sub ReportFooter_Format(Cancel As Integer, FormatCount As Integer)
 Dim stDocName As String, I As Integer, TotExtern As Long, idStation As Long
    Dim rst As Recordset, strSQL As String, strStation As String, KMaand As Date
       KMaand = Format(Me.Maand, "mmmm yyyy")
      idStation = Me.StationID
    strSQL = "SELECT [tabGewerkte uren].MaandID AS [tabGewerkte uren_MaandID], [tabGewerkte uren].RelID, [tabGewerkte uren].[Totaal uren], TabExtern.StationexID, [tabGewerkte uren].Maand, TabExtern.[Externe uren], TabExtern.MaandID AS TabExtern_MaandID, TabStations.Station, TabStations.StationID, [Naam Query].Naam FROM ((TabRegios INNER JOIN TabStations ON TabRegios.RegioID = TabStations.RegioID) INNER JOIN (Werknemers INNER JOIN [Naam Query] ON Werknemers.RelID = [Naam Query].RelID) ON TabStations.StationID = Werknemers.StationID) INNER JOIN ([tabGewerkte uren] LEFT JOIN TabExtern ON [tabGewerkte uren].MaandID = TabExtern.MaandID) ON Werknemers.RelID = [tabGewerkte uren].RelID WHERE [tabGewerkte uren].Maand=#" & Format(KMaand, "mmmm yyyy") & "#"
Set rst = CurrentDb.OpenRecordset(strSQL)

 lngCount = rst.RecordCount

Unfortunately I receive an error 3075
Syntax error in date [tabGewerkte uren].Maand=#maart 2006#

What is going wrong?

regards Apestaart

 

[PHV]

What is the data type of Maand in [tabGewerkte uren] ?

Another way:[tt]
...
WHERE Format([tabGewerkte uren].Maand,'mmmm yyyy')='" & Format(Me!Maand, "mmmm yyyy") & "'"[/tt]


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[apestaart]

Dear PVH,
Your Question : What is the data type of Maand in [tabGewerkte uren] ? Type is Date. Format "mmmm yyyy"
Apestaart

 

[PHV]

Have you tried my suggested WHERE clause ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[apestaart]

The program is in my laptop. I will try it tomorrow morning. Actual time in the Netherland is now 23.30.
Thanks I will answer you.

 

[apestaart]

Dear PHV and jadams0173 ,
I added the format for Maand like you suggested :Format([tabGewerkte uren].Maand,'mmmm yyyy')=
It works !!!
Thank you both for your help!
Apestaart

PS. I have a new problem see "can't repeat export to excel"