Query Help

[Aaitaman Tamang]

I have created table called “rptLocati on” and have all incident details. I have incidents that are categorized such as First Aid, Near Miss and etc…..and the records are archived in a table based on occurred date of these incidents.

Now I want to have Query that gives me just the total of those incidents base on my Criteria. I am preferring to have form where user have to type their date selection in the text box such as (StartDate and EndDate and incident type from the drop-down list) I want run bottom to open just the incidents now base on their select of dates and incident type.

The thing is I want this records to open in report view and they can save it somewhere in their computer and send to client. So anyone help me what criteria would be placed in Query. I was not able to do as I wanted to have result. I was able to Group By and give me the entire incident and look good but I want to run this as preview so the user can save it. I have enclosed my file so anyone can have look at it what my goal is.
Any help would be much appreciated

[TheSmileyCoder]

If you have a form, in which the user can specify the details, you can add a button to that form to validate the input (I.e. is input a valid date?) and then open the report in preview mode, with a filter applied.

Say you have a textbox tb_StartDate on your form, and the field in your table/query is called dt_Date. Code could look something like this:

Dim strFilter as string
'Check that data was entered into field
If isNull(me.tb_StartDate) Then
  MsgBox "No criteria entered"
  Exit Sub
else
  'Check that data is a valid date
  If not isDate(me.tb_StartDate) then
    Msgbox "The Date you typed as start date is not recognized as a valid Date"
    Exit Sub
  Else
    strFilter="(dt_Date>#" & me.tb_StartDate & "#)"
  End If
End If

docmd.OpenReport "rptLocation", acViewPreview,,strFilter,acWindowNormal

This information is intended as a guide to get you going, not a complete solution.
Try to apply this to your situation, and if you have trouble, please come back and ask for more help.

[Aaitaman Tamang]

Hi TheSmileyCoder,

Basically i have form where i have two unbound text boxes one with StartDate and EndDate which are name of TextBox also there is Combo box with incident type. Of course there is one table with all this archived details of incidents.

Now i want user to open the form and type the report date such as from 10/15/2008 to 10/25/2010 and select the incident type and just hit the button. When user raise the event i want to see the details of incident in preview mood.
I hope this is a bit more information will help you to help me again. Thanks in advance. looking forward to hear again
Tamang

[TheSmileyCoder]

Hi again

I have allready supplied what I believe to be adequate information for you to proceed in solving your issue.

I would like to see that you try to apply this information yourself, and come back here when you done so (or tried to).

If you have a problem in applying the information, or don't understand, please let me/us know which parts are causing you grief, but I am not ready to simply write a copy+paste solution for you.

Best of Luck

[Aaitaman Tamang]

Thanks for help,
I have tried already and still. I have done a bit of modification using your above code. Seems like cool but did not get any details in report view. I have checked my table to ensure that the table has a record.
May be i am not good in VBA. But i still have to ask you for help. While using above code do i still have to have Query or not? Basically i have Query and have this Criteria (Between [forms]![RunReportF]![StartDate] And [Forms]![RunReportF]![EndDate])


anything wrong with below adjustment?

    Dim strFilter As String
    'Check that data was entered into field
    If IsNull(Me.StartDate.Value) And IsNull(Me.EndDate.Value) Then
      MsgBox "No criteria entered"
      Exit Sub
    Else
      'Check that data is a valid date
      If Not IsDate(Me.StartDate.Value) And IsNull(Me.EndDate.Value) Then
        MsgBox "The Date you typed as start date is not recognized as a valid Date"
        Exit Sub
      Else
        strFilter = "(hDate=#" & Me.StartDate & "# )"
        strFilter = "(hDate=#" & Me.EndDate & "#)"
      End If
    End If
     
    DoCmd.OpenReport "Report2", acViewPreview, , strFilter, acWindowNormal

[TheSmileyCoder]

If you allready in the reports recordsource (as I understand it, in this case its a query) have the criteria:
(Between [forms]![RunReportF]![StartDate] And [Forms]![RunReportF]![EndDate])
then you do not need to add it as a filter string. A filter string is basicly a WHERE clause without the WHERE keyword, and can be used in many differnent circumstances. A good example might be, that you have manually aplied a filter to a form, and want to print a report using the same filter as is applied on the form.

However, In your case I think that you simply need to open your report in preview mode:

DoCmd.OpenReport "Report2", acViewPreview, , , acWindowNormal

but you may or may not want to leave in the checks for valid data.


Im still going to comment the modifications to the code:
From a logic point of view you probably want to use OR instead of AND:

If IsNull(Me.StartDate.Value) OR IsNull(Me.EndDate.Value) Then

and again on line 8:

If Not IsDate(Me.StartDate.Value) And Not isDate(Me.EndDate.Value) Then

where it also seems you used a IsNull instead of an Not isDate.

In line 12 you first write a filter criteria, and then in line 13 you overwrite your filter. You are also using = where I presume you mean to use > or <

Instead:

strFilter = "(hDate>#" & Me.StartDate & "#) AND (hDate<#" & me.EndDate & "#)"

I hope this helps you along.