No data displayed

[cjharris]

I have a report that I am trying to complete. It display the desired results if I leave the criteria field blank, but when I try to run the report with something in the criteria field it displays no data, the only way I can get it to work using criteria is if I place a parameter in the fields that have a specified criteria.

Thanks and regards,
Charles

 

[GingerR]

what are you putting in the criteria? Is what you are putting in the criteria exist in the data in the field?

what are you expecting it to display when you put in the criteria, and what is it displaying in error that you need corrected?

 

[cjharris]

What I am putting in the criteria section for the user id column is "UCase([Enter User Name:])", I also place in the criteria section for the date column "Between [Enter begin Entry Date as MM/DD/YYYY:] And [Enter end Entry Date as MM/DD/YYYY:]".

Upon doing the following I successfully retrieve data. But I must enter a value at each criteria I mentioned above. This is fine, but I need to be able to retrieve the records even if the user presses enter and proceeds without entering a user id or date range when the alert ask them to do so.

I no there is data out there, because prior to me placing coding in the criteria field it would query the hold database based on my query. This is what I need it to do even if the user proceeds without filling in a parameter for the user id or date range.

Thanks and regards
Charles

 

[GingerR]

The best thing to do would be to create a form where the users enter or select the values they are looking for. This way they don't have to type them in all of the time. And what if they spell the "user name" wrong. a big pain for them.

So create a form.
Make a drop-down box which displays all of the user name choices (fed from a table you have with the user names in it). Call it cboUserName.

Make a text box called txtDateFrom.
Make another called txtDateTo.

Put a button on the form.
In the button's OnClick event, write a little code that builds your criteria from the controls on the form and then opens the report based on that string. Something like this:
You will have to change the names in square brackets to your own field names.

Dim strWhere As String

If Not IsNull(Me.cboUserName) Then
    strWhere = "[UserName] = '" & Me.cboUserName & "'"
End If

If Not IsNull(Me.txtDateFrom) And Not IsNull(Me.txtDateTo) Then
    If Len(strWhere) = 0 Then
        strWhere = "[HireDate] between #" & Me.txtDateFrom & "# and #" & Me.txtDateTo & "#"
    Else
        strWhere = "[HireDate] between #" & Me.txtDateFrom & "# and #" & Me.txtDateTo & "#"
    End If
End If

DoCmd.OpenReport "DateReport", acViewPreview, , strWhere

then take the criteria out of your query grid.
so if someone leaves the controls on the form blank, it will open with all records.