MS Access 2003 - Multiple DoCmd...

[Ryman1986]

Hi All,

I hope you can help...

I have been working on this for a while and my brain has all but melted... it seems simple, but i can't figure out how to make it work...

I have a form, which consists of 4 combo boxes and one button, which are:

• cboBusinessArea
• cboOwner
• cboFrom
• cboTo
• cmdRunReport (Button)

The Form looks something similar to below:

Business Area/Meeting: (cboBusinessArea)
Owner: (cboOwner)
Date Raised: From: (cboFrom) To: (cboTo)

GO
​

I have worked out the code to run each combo box seperately ie select "Financial Compliance" from the cboBusinessArea and click GO produces a report of all information in regards to "Financial Compliance".

But I am looking to be able to run a report based on all 3 areas or 2 if wanted. Below is the code i have to run each area individually:

1 - DoCmd.OpenReport "rptActionLog", acViewPreview, , "[DateRaised] Between #" & Me.cboFrom & "# & #" & Me.cboTo & "#" * "[BusinessArea/Meeting]='" & Me.cboBusinessArea & "'" * "[Owner]='" & Me.cboOwner & "'"
2 - 'DoCmd.OpenReport "rptActionLog", acViewPreview, , "[BusinessArea/Meeting]='" & Me.cboBusinessArea & "'"
3 - 'DoCmd.OpenReport "rptActionLog", acViewPreview, , "[Owner]='" & Me.cboOwner & "'"

I wrongly assumed it would be as simple as adding an "And" in between each cmd...

PLEASE HELP!!!

Thanks

Ryman

 

[boblarson]

Just do this in the click event:

    Dim strWhere As String
 
    If Len(Me.cboFrom & vbNullString) > 0 And Len(cboTo & vbNullString) > 0 Then
        strWhere = "[DateRaised]  Between #" & Me.cboFrom & "# AND #" & Me.cboTo & "# AND "
    End If
 
    If Len(Me.cboFrom & vbNullString) > 0 And Len(cboTo & vbNullString) = 0 Then
        strWhere = "[DateRaised]  >= #" & Me.cboFrom & "# AND "
    End If
 
    If Len(Me.cboFrom & vbNullString) = 0 And Len(cboTo & vbNullString) > 0 Then
        strWhere = "[DateRaised]  <= #" & Me.cboTo & "# AND "
    End If
 
    If Len(Me.cboBusinessArea & vbNullString) > 0 Then
        strWhere = strWhere & "[BusinessArea/Meeting] =" & Chr(34) & Me.cboBusinessArea & Chr(34) & " AND "
    End If
 
    If Len(Me.cboOwner & vbNullString) > 0 Then
        strWhere = strWhere & "[Owner]=" & Me.cboOwner & Chr(34) & " AND "
    End If
 
    strWhere = Left(strWhere, Len(strWhere) - 5)
 
    DoCmd.OpenReport "rptActionLog", acViewPreview, WhereCondition:=strWhere

That is one of the easiest ways because it is broken up into segments which are easy to read. The first three IF's are just getting the right date set up so you can select the from and to, the from, or the to and get the date range. If neither are selected then it doesn't include the date field. The AND is also in the very last one just to simplify the code so it doesn't have to check to see if there is an AND at the end and it just strips off the last 5 characters which would be the two spaces around the AND.

Hope that helps.