Multiple conditions in where statement (1 Viewer)

[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.

 

[Pat Hartman]

I've been writing SQL statements for over 40 years. In the mainframe days, we had no GUI tool. There was nothing to do but type the string. And keep compiling until all the typos were gone. I work on multiple applications at one time and so I would have to keep printed schemas or lists of names around and reference them as I was typing. I used to dream about someday having a tool like the QBE.

Granted, the QBE is flawed but I'm not too proud to use it even though I can write SQL Without it's help. So I only build dynamic SQL in VBA such as that needed to support a search form. All static SQL is done with the QBE unless it is too complex. One of the flaws of the QBE is that it rewrites your SQL string to suit itself when the query is saved. You can avoid this by only working in SQL View but I might as well work in VBA since the QBE has no editing tools and at least VBA has some.

Parameters do not make SQL dynamic. Structural changes make the SQL dynamic such as changing the where clause or the number in the Top x.