Multiple conditions in where statement

[Bieke]

Hello,

I want to have multiple conditions on a recordset based on a query.
It works well with only one condition (Forms!frmalarms![M]) but i also want to add the date condition (>[forms]![frmAlarms]![cmbodate] And <[forms]![frmalarms]![DTM2])
It works fine in the query (see below) but not on my recordset.

Set RS = CurrentDb.OpenRecordset("Select * from dbo_DCEREPORT_INTERRUPTOPERATIONS INNER JOIN dbo_DCEREPORT_MACHINES ON dbo_DCEREPORT_INTERRUPTOPERATIONS.PROCESSRESOURCE_OID = dbo_DCEREPORT_MACHINES.OID where dbo_DCEREPORT_MACHINES.NAME = " & " '" & Forms!frmalarms![M] & "'", dbOpenDynaset)

Can somewone tell me how to add an additional condition?

Thanks in advance,

Mieke

 

[Minty]

Put your statement into a string variable and examine it before you try and use it;

    Dim strSQL As String

    strSQL = "Select * from dbo_DCEREPORT_INTERRUPTOPERATIONS INNER JOIN dbo_DCEREPORT_MACHINES ON dbo_DCEREPORT_INTERRUPTOPERATIONS.PROCESSRESOURCE_OID = dbo_DCEREPORT_MACHINES.OID "
    strSQL = strSQL & " WHERE dbo_DCEREPORT_MACHINES.NAME = '" & Forms!frmalarms![M] & "' AND DTSSTART BETWEEN #" & Format(Forms!frmalarms![cmboDate], "yyyy-mm-dd") & "# AND "
    strSQL = strSQL & "#" & Format(Forms!frmalarms![DTMZ], "yyyy-mm-dd") & "#"
    
    Debug.Print strSQL
 
    Set rs = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)

 

[MarkK]

More reliable still is a temporary QueryDef, which handles all your delimiters and date formats for you...

    Const SQL As String = _
        "SELECT ti.* " & _
        "FROM dbo_DCEREPORT_INTERRUPTOPERATIONS As ti INNER JOIN " & _
            "dbo_DCEREPORT_MACHINES As tm ON ti.PROCESSRESOURCE_OID = tm.OID " & _
        "WHERE tm.NAME = p0 " & _
            "AND ti.DTSSTART BETWEEN p1 AND p2 "
            
    Dim rst As DAO.Recordset
    Dim qdf As DAO.QueryDef
    
    Set qdf = CurrentDb.CreateQueryDef("", SQL)
    
    With Forms!frmalarms
        qdf.Parameters(0) = .m
        qdf.Parameters(1) = .cmboDate
        qdf.Parameters(2) = .DTMZ
    End With
    
    Set rst = qdf.OpenRecordset

 

[Bieke]

Put your statement into a string variable and examine it before you try and use it;

    Dim strSQL As String

    strSQL = "Select * from dbo_DCEREPORT_INTERRUPTOPERATIONS INNER JOIN dbo_DCEREPORT_MACHINES ON dbo_DCEREPORT_INTERRUPTOPERATIONS.PROCESSRESOURCE_OID = dbo_DCEREPORT_MACHINES.OID "
    strSQL = strSQL & " WHERE dbo_DCEREPORT_MACHINES.NAME = '" & Forms!frmalarms![M] & "' AND DTSSTART BETWEEN #" & Format(Forms!frmalarms![cmboDate], "yyyy-mm-dd") & "# AND "
    strSQL = strSQL & "#" & Format(Forms!frmalarms![DTMZ], "yyyy-mm-dd") & "#"
   
    Debug.Print strSQL

    Set rs = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)

Thanks Minty, it works !! Probebly easy for you but this saved me a lot of time ...

 

[Bieke]

More reliable still is a temporary QueryDef, which handles all your delimiters and date formats for you...

    Const SQL As String = _
        "SELECT ti.* " & _
        "FROM dbo_DCEREPORT_INTERRUPTOPERATIONS As ti INNER JOIN " & _
            "dbo_DCEREPORT_MACHINES As tm ON ti.PROCESSRESOURCE_OID = tm.OID " & _
        "WHERE tm.NAME = p0 " & _
            "AND ti.DTSSTART BETWEEN p1 AND p2 "
           
    Dim rst As DAO.Recordset
    Dim qdf As DAO.QueryDef
   
    Set qdf = CurrentDb.CreateQueryDef("", SQL)
   
    With Forms!frmalarms
        qdf.Parameters(0) = .m
        qdf.Parameters(1) = .cmboDate
        qdf.Parameters(2) = .DTMZ
    End With
   
    Set rst = qdf.OpenRecordset

Hey MarkK, i never worked with Querydefs, it looks more structured. I will test it. Thanks for the reply.

 

[Minty]

A QuerydDef is a much more structured and robust method, to be honest.
Well worth getting acquainted with that method.