Hi, I am having a problem getting criteria to function as I would like when running a report.

The report is based on a query.

I have a form to select criteria which has 2 combo boxes.
1. Sales Person and the 2. Status of the entry.
and a print button to run the report with the selected criteria, containing the following code.

Private Sub cmdPrintQuote_Click()
On Error GoTo Err_cmdPrintQuote_Click

    Dim stDocName As String
    Dim stSalesRep As String
    Dim stStatus As String
    Dim stLinkCriteria As String

    stDocName = "rptQuotesBySalesPerson"
    stSalesRep = Me!cboSalesPerson
    stStatus = Me!cboStatus


    stLinkCriteria = "[EmployeeName] = " & "'" & stSalesRep & "'" & " AND [Status] = " & "'" & stStatus & "'"

    DoCmd.OpenReport stDocName, acPreview, , stLinkCriteria

Exit_cmdPrintQuote_Click:
    Exit Sub

Err_cmdPrintQuote_Click:
    MsgBox Err.Description
    Resume Exit_cmdPrintQuote_Click

End Sub


The report works fine when I select entries in BOTH of the combo boxes.
When I don't select 1 of the combos, I get an "Invalid use of Null" message.
But I would like to run the report by selecting a. No criteria (all records) b. Records for a particular Sales Person c. Records for a particular Status. Or a combination.

Is there a way I can acheive this ?

I would like to also add date selection to the criteria form, I have looked at the Microsoft Date/Time picker which looks great but I cant seem to extract the highlighted date.

Any help would be greatly appreciated.

The date picker in Access is problematic since it is not always supported on the PCs your app is going out to. I stay away from it. Generally I populate a combo box from the table that has the dates, set Unique values to Yes, and this gives the user a list of actual dates to choose from. If this is for monthly reporting, you can also give them a month and year combo (one for each).

That being said, you can do this with the criteria to allow users to select one or both or none. You may have to add a button to reset the combos in the case where they run the report once with criteria, and then want to run it again without. You can also reset after each report run.

    Dim stDocName As String
    Dim stSalesRep As String
    Dim stStatus As String
    Dim stLinkCriteria As String

    stDocName = "rptQuotesBySalesPerson"

If IsNull(Me.cboSalesPerson) Or Me.cboSalesPerson = "" Then
   stLinkCriteria = ""
Else
   stSalesRep = Me.cboSalesPerson
   stLinkCriteria = = "[EmployeeName] = '" & stSalesRep & "'"
End If

If IsNull(Me.cboStatus) Or Me.cboStatus = "" Then
   stLinkCriteria = stLinkCriteria & ""
Else
   stStatus = Me.cboStatus
   If stLinkCriteria <> "" Then
      stLinkCriteria = stLinkCriteria & " AND [Status] = '" & stStatus & "'"
   Else
      stLinkCriteria = "[Status] = '" & stStatus & "'"
   End If
End If

    'reset combos here.
    DoCmd.OpenReport stDocName, acPreview, , stLinkCriteria


You can continue to add other parameters to this basic structure ad infinitum. If this is a Jet backend, then use # instead of ' around your values for dates, and if this is a SQL back end, then don't use #.

Did that help?

mmcdonal

Yes that did help. Thankyou.
I've added another combo to select the State and it works perfectly.

If IsNull(Me.cboState) Or Me.cboState = "" Then
   stLinkCriteria = stLinkCriteria & ""
Else
   stCustState = Me.cboState
   If stLinkCriteria <> "" Then
      stLinkCriteria = stLinkCriteria & " AND [CustomerState] = '" & stCustState & "'"
   Else
      stLinkCriteria = "[CustomerState] = '" & stCustState & "'"
   End If
End If


Ive also added Start Date and End Date combos, but am having trouble with the Link Criteria. Can't get it to show records BETWEEN the 2 dates. I could use the criteria from the from in the query, but need the report run for -

All Dates or
From Start Date or
To End Date or
Between Dates

That would be:

Dim dtStart As Date
Dim dtEnd As Date

If IsNull(Me.cboStartDate) Or Me.cboStartDate = "" Then
   stLinkCriteria = stLinkCriteria & ""
Else
   dtStart = Me.cboStartDate
End If
If IsNull(Me.cboEndDate) Or Me.cboEndDate = "" Then
   If dtStart <> "" Then
      MsgBox "Please select and End Date."
      Exit Sub
Else
   dtEnd = Me.cboEndDate
End If

If dtStart <> "" And dtEnd <> "" Then
   If stLinkCriteria <> "" Then
      stLinkCriteria = stLinkCriteria & " AND [DateField] BETWEEN #" & dtStart & "# AND #" & dtEnd & "#"
   Else
      stLinkCriteria = "[DateField] BETWEEN #" & dtStart & "# AND #" & dtEnd & "#"
   End If
End If

I think that will work.


mmcdonal

Thankyou again.

I got a Compile Error: Block If without End If.

I added an End If to the bottom of the Date code. ??

If I run the report with no date criteria selected, I get a type mismatch.
The Data type is a Short Date.

If I add dates to the combo boxes the report runs but doesn't select the date range i've specified.

I tried switching # for ' but to no avail.


Dave

Sorry, I left it out here:

Dim dtStart As Date
Dim dtEnd As Date

If IsNull(Me.cboStartDate) Or Me.cboStartDate = "" Then
   stLinkCriteria = stLinkCriteria & ""
Else
   dtStart = Me.cboStartDate
End If
If IsNull(Me.cboEndDate) Or Me.cboEndDate = "" Then
   If dtStart <> "" Then
      MsgBox "Please select and End Date."
      Exit Sub
   End If
Else
   dtEnd = Me.cboEndDate
End If

If dtStart <> "" And dtEnd <> "" Then
   If stLinkCriteria <> "" Then
      stLinkCriteria = stLinkCriteria & " AND [DateField] BETWEEN #" & dtStart & "# AND #" & dtEnd & "#"
   Else
      stLinkCriteria = "[DateField] BETWEEN #" & dtStart & "# AND #" & dtEnd & "#"
   End If
End If

What are the names of the start and end date combo boxes? Did you substitue the names you are using on your form?


mmcdonal

Look it up at: http://wrox.books24x7.com

There is a very good popup date picker routine available from the microsoft press books by Viecas. It doesn't rely on the microsoft date picker as its solely written in vba and uses a form instead.

I'm getting the mismatch still.

Even if I now put dates into the combos.

The Date Combos are cboStartDate and cboEndDate. The date field in the query which is on the report is CreatedDate.

Am I missing something in the Date formatting ?


    Dim stDocName As String
    Dim stSalesRep As String
    Dim stStatus As String
    Dim stLinkCriteria As String
    Dim stCustState As String
    Dim dtStart As Date
    Dim dtEnd As Date

    stDocName = "rptQuotesBySalesPerson"

If IsNull(Me.cboSalesPerson) Or Me.cboSalesPerson = "" Then
        stLinkCriteria = ""
    Else
        stSalesRep = Me.cboSalesPerson
        stLinkCriteria = "[EmployeeName] = '" & stSalesRep & "'"
End If

If IsNull(Me.cboStatus) Or Me.cboStatus = "" Then
        stLinkCriteria = stLinkCriteria & ""
    Else
        stStatus = Me.cboStatus
    If stLinkCriteria <> "" Then
        stLinkCriteria = stLinkCriteria & " AND [Status] = '" & stStatus & "'"
    Else
        stLinkCriteria = "[Status] = '" & stStatus & "'"
   End If
End If

If IsNull(Me.cboState) Or Me.cboState = "" Then
        stLinkCriteria = stLinkCriteria & ""
    Else
        stCustState = Me.cboState
    If stLinkCriteria <> "" Then
        stLinkCriteria = stLinkCriteria & " AND [CustomerState] = '" & stCustState & "'"
    Else
      stLinkCriteria = "[CustomerState] = '" & stCustState & "'"
    End If
End If

'Date Selection
If IsNull(Me.cboStartDate) Or Me.cboStartDate = "" Then
   stLinkCriteria = stLinkCriteria & ""
Else
   dtStart = Me.cboStartDate
End If
If IsNull(Me.cboEndDate) Or Me.cboEndDate = "" Then
   If dtStart <> "" Then
      MsgBox "Please select End Date."
      Exit Sub
   End If
Else
   dtEnd = Me.cboEndDate
End If

If dtStart <> "" And dtEnd <> "" Then
   If stLinkCriteria <> "" Then
      stLinkCriteria = stLinkCriteria & " AND [CreatedDate] BETWEEN #" & dtStart & "# AND #" & dtEnd & "#"
   Else
      stLinkCriteria = "[CreatedDate] BETWEEN #" & dtStart & "# AND #" & dtEnd & "#"
   End If
End If

    'Set combo boxes to Null
    cboStartDate.Value = Null
    cboEndDate.Value = Null
    cboSalesPerson.Value = Null
    cboStatus.Value = Null
    cboState.Value = Null

    DoCmd.OpenReport stDocName, acPreview, , stLinkCriteria

Does it work if you only select dates and no other parameters?

So you are using [CreatedDate] to populate the cboStartDate and cboEndDate fields?



mmcdonal

Look it up at: http://wrox.books24x7.com

Yes to Both, After adding the last code which had the additional End If, I get a type mismatch wether I put dates in or leave them blank.

I am using the CreatedDate field for criteria for both cboStartDate and cboEndDate


Dave