By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,609 Members | 1,530 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,609 IT Pros & Developers. It's quick & easy.

Query Help

P: 22
I have created table called “rptLocation” 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
Attached Files
File Type: zip db1.zip (297.8 KB, 40 views)
Nov 16 '11 #1
Share this Question
Share on Google+
5 Replies


TheSmileyCoder
Expert Mod 100+
P: 2,321
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:

Expand|Select|Wrap|Line Numbers
  1. Dim strFilter as string
  2. 'Check that data was entered into field
  3. If isNull(me.tb_StartDate) Then
  4.   MsgBox "No criteria entered"
  5.   Exit Sub
  6. else
  7.   'Check that data is a valid date
  8.   If not isDate(me.tb_StartDate) then
  9.     Msgbox "The Date you typed as start date is not recognized as a valid Date"
  10.     Exit Sub
  11.   Else
  12.     strFilter="(dt_Date>#" & me.tb_StartDate & "#)"
  13.   End If
  14. End If
  15.  
  16. 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.
Nov 16 '11 #2

P: 22
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
Nov 16 '11 #3

TheSmileyCoder
Expert Mod 100+
P: 2,321
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
Nov 16 '11 #4

P: 22
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?
Expand|Select|Wrap|Line Numbers
  1.     Dim strFilter As String
  2.     'Check that data was entered into field
  3.     If IsNull(Me.StartDate.Value) And IsNull(Me.EndDate.Value) Then
  4.       MsgBox "No criteria entered"
  5.       Exit Sub
  6.     Else
  7.       'Check that data is a valid date
  8.       If Not IsDate(Me.StartDate.Value) And IsNull(Me.EndDate.Value) Then
  9.         MsgBox "The Date you typed as start date is not recognized as a valid Date"
  10.         Exit Sub
  11.       Else
  12.         strFilter = "(hDate=#" & Me.StartDate & "# )"
  13.         strFilter = "(hDate=#" & Me.EndDate & "#)"
  14.       End If
  15.     End If
  16.  
  17.     DoCmd.OpenReport "Report2", acViewPreview, , strFilter, acWindowNormal
Nov 16 '11 #5

TheSmileyCoder
Expert Mod 100+
P: 2,321
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:
Expand|Select|Wrap|Line Numbers
  1. 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:
Expand|Select|Wrap|Line Numbers
  1. If IsNull(Me.StartDate.Value) OR IsNull(Me.EndDate.Value) Then
and again on line 8:
Expand|Select|Wrap|Line Numbers
  1. 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:
Expand|Select|Wrap|Line Numbers
  1. strFilter = "(hDate>#" & Me.StartDate & "#) AND (hDate<#" & me.EndDate & "#)"

I hope this helps you along.
Nov 16 '11 #6

Post your reply

Sign in to post your reply or Sign up for a free account.