Select Between (Date) AND In txtBox

[SeBasTiaan]

Hello,

I'm trying to make a report query work but with no succes so far. Can anyone please help me?
I've got a multiselect listbox and a txtbox (txtCursisten) where the results from the multiselect listbox appear.
I want to get the results out of a table (tblAbsentie) where I can set a start and end date AND use the people selected in the txtBox. So far I've got a NOT working query, a report based on the query and a button on my form.

The VB code behind the "show report" button is this:

Private Sub cmdAbsentiePerCursistPerPeriode_Click()

On Error GoTo Err_cmdAbsentiePerCursistPerPeriode_Click

Dim db As Database
Dim Q As QueryDef
Dim sql As String



If IsNull("Me.txtCursisten") Or Me.txtCursisten = "" Then
Exit Sub
Else

Set db = currentdb()
Set Q = db.QueryDefs("qryAbsentiePerCursistPerPeriode")
Q.sql = "SELECT tblCursist.Naam, tblAbsentie.Datum, tblAbsentie.Lesuur, tblAbsentie.AantalLesuren, tblAbsentie.Deelkwalificatie, tblAbsentie.Docent, tblAbsentie.Gemotiveerd, tblAbsentie.Reden, tblAbsentie.Status, qryCountLesuren.SumOfAantalLesuren" & _
"FROM (tblCursist INNER JOIN qryCountLesuren ON tblCursist.OVnr=qryCountLesuren.OVnr) INNER JOIN tblAbsentie ON tblCursist.OVnr=tblAbsentie.OVnr" & _
"WHERE (((tblAbsentie.Datum) Between [Voer begindatum in] And [Voer einddatum in]) AND ((tblAbsentie.OVnr) In ("Me!txtCursisten"))); "
Q.Close

DoCmd.OpenReport "rptAbsentiePerCursistPerPeriode", acPreview
End If

On Error GoTo 0
Exit Sub

Exit_cmdAbsentiePerCursistPerPeriode_Click:
Exit Sub

Err_cmdAbsentiePerCursistPerPeriode_Click:
MsgBox "Selecteer klas en cursist(en)."
Resume Exit_cmdAbsentiePerCursistPerPeriode_Click

End Sub

I get a syntax error when pressing the button. Please help...
SeBasTiaan

 

[WayneRyan]

SeBasTiaan,

You need the "#" to delimit the date.

"WHERE tblAbsentie.Datum Between #" & Me.[Voer begindatum in] & "# And #" & Me.[Voer einddatum in] & " AND " & _
"      tblAbsentie.OVnr In (" & Me.txtCursisten & ")"

If OVnr is a number, txtCursisten is something like: 3,5,7,9

If OVnr is a string, txtCursisten is something like: '3','5','7','9'

Also: SumOfAantalLesuren" & "FROM ...

You need a space after SumOfAantalLesuren

Look at (and attach) the following next time using the immediate window:

?q.sql

Wayne

 

[SeBasTiaan]

Thanks Wayne for your comments. It still doesn't work. When I select some options in the listbox, I always get the error message. No matter what I do.
Can you please have another look? Does the recordsource query of the report need to be exactly the same as the query below?

Private Sub cmdAbsentiePerCursistPerPeriode_Click()

  On Error GoTo Err_cmdAbsentiePerCursistPerPeriode_Click
  
Dim db As Database
Dim Q As QueryDef
Dim sql As String

If IsNull("Me.txtCursisten") Or Me.txtCursisten = "" Then
Exit Sub
Else

Set db = currentdb()
Set Q = db.QueryDefs("qryAbsentiePerCursistPerPeriode")
Q.sql = "SELECT tblCursist.Naam, tblAbsentie.Datum, tblAbsentie.Lesuur, tblAbsentie.AantalLesuren, tblAbsentie.Deelkwalificatie, tblAbsentie.Docent, tblAbsentie.Gemotiveerd, tblAbsentie.Reden, tblAbsentie.Status, qryCountLesuren.SumOfAantalLesuren " & _
        "FROM (tblCursist INNER JOIN qryCountLesuren ON tblCursist.OVnr=qryCountLesuren.OVnr) INNER JOIN tblAbsentie ON tblCursist.OVnr=tblAbsentie.OVnr " & _
        "WHERE tblAbsentie.Datum Between #" & Me.[Voer begindatum in] & "# And #" & Me.[Voer einddatum in] & " AND " & _
    "tblAbsentie.OVnr In (" & [Forms]![frmAbsentieInvoeren]![txtCursisten] & ");"
Q.Close

DoCmd.OpenReport "rptAbsentiePerCursistPerPeriode", acPreview
End If

   On Error GoTo 0
   Exit Sub
   
Exit_cmdAbsentiePerCursistPerPeriode_Click:
    Exit Sub

Err_cmdAbsentiePerCursistPerPeriode_Click:
    MsgBox "Selecteer klas en cursist(en)."
    Resume Exit_cmdAbsentiePerCursistPerPeriode_Click

End Sub

 

[SeBasTiaan]

Example

Hello, i've put the example database with my reply so it makes it a bit more clear.

There's one form, and the most bottom right report (Absentie per Cursist per Periode) is the one I'm having trouble with. please have another look. When this is ready, I'm done

Tnx in advanced!!!!