Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

date range

Posted on 2014-02-14
18
257 Views
Last Modified: 2014-02-21
Hi,

this is VB access database

I can open the report but it display all the data right now but what I want is to prompt the user with a date range. so, the report will display only the date range that the user entered.


DoCmd.OpenReport "Report_1_MTD_Daily_Containment", acViewPreview, , ""



Thanks,
Lulu
0
Comment
Question by:lulu50
  • 7
  • 5
  • 4
  • +1
18 Comments
 
LVL 120

Assisted Solution

by:Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1) earned 250 total points
ID: 39858879
try this

DoCmd.OpenReport "Report_1_MTD_Daily_Containment", acViewPreview, , "[date field Name]=[Enter date]"


or change the record source of your report to a parameter query, where you can prompt for the date range
0
 
LVL 35

Accepted Solution

by:
PatHartman earned 250 total points
ID: 39858883
Adding a prompt to the query when it is used in a report will be annoying because you will get prompted at least twice.  Once when you open for preview and again when you print.  Add a control to your form to capture the information.  Then use the Where argument to pass it into the report.

DoCmd.OpenReport "Report_1_MTD_Daily_Containment", acViewPreview, , "YourField = " & Me.YourField

If yourfield is a string, you need to encase it in quotes.

DoCmd.OpenReport "Report_1_MTD_Daily_Containment", acViewPreview, , "YourField = '" & Me.YourField & "'"

If it is a date, you need to encase it in pound signs.

DoCmd.OpenReport "Report_1_MTD_Daily_Containment", acViewPreview, , "YourDate = #" & Me.YourDate & "#"
0
 

Author Comment

by:lulu50
ID: 39858934
I think it is better to prompt the user to enter a date range when they click on the button that is in the form.

once they enter the date range the report should take the value and generate the report based on the input date from the user.

something like this but I don't know access much

Dim DateRange As Date: DateRange = InputBox("Please enter a date range!", "From", Date - 1)

DoCmd.OpenReport "Report_1_MTD_Daily_Containment", acViewPreview, , ""
0
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 
LVL 35

Expert Comment

by:PatHartman
ID: 39859002
lulu,
Prompting isn't enough.  You also have to include a where clause in the OpenReport method.
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 39859007
pass the value entered in the input box


Dim DateRange As Date: DateRange = InputBox("Please enter a date range!", "From", Date - 1)

if Daterange & ""<>"" then

DoCmd.OpenReport "Report_1_MTD_Daily_Containment", acViewPreview, , "[DateField]=#" & dateRange & "#"

end if
0
 
LVL 61

Expert Comment

by:mbizup
ID: 39859023
<< something like this but I don't know access much >>

Which is why Pat is attempting to show you the 'best practices' for this, to make things easier on you as the developer and to provide a better user interface.

The form interface she is describing is pretty standard.

The date range input you are asking for is not.  What you are asking for would either involve two parameter prompts for the start and end of the range (annoying from a user interface perspective), more complex code to parse a date range with a single prompt as you are describing, or would limit the range to only one entry (ie: your 'fixed' date()-1)

The form interface is a simple design, with simple code and is an easy to follow standard for most users.
0
 

Author Comment

by:lulu50
ID: 39859108
Rey,

Thank you all for your help.

I entered 2/10/2014  than I entered 2/13/2014,  I did  not get any data but I do have data in that date range.

Dim DateRange As Date: DateRange = InputBox("Please enter a date range!", "From", Date - 1)

If DateRange & "" <> "" Then

DoCmd.OpenReport "Report_1_MTD_Daily_Containment", acViewPreview, , "[DateField]=#" & DateRange & "#"

End If
0
 
LVL 35

Expert Comment

by:PatHartman
ID: 39859124
Let me expand my example to include a range:

If you define the form controls as dates, Access will restrict them to valid dates so you only have to verify their presence.  If you use unformatted text boxes, they could be anything and so you would need to verify that both dates are valid.

If you use prompts, you have to take it on faith that the user entered a valid date.  Prompts are NOT the way to go.  If the user doesn't enter a valid date range, THEN you would prompt.

If IsDate(Me.StartDate) AND IsDate(Me.EndDate) Then
    If Me.EndDate > Me.StartDate Then
    Else
        Msgbox "End Date must be greater than start date.",vbOKOnly
        Me.EndDate.SetFocus
        Exit Sub
    End If
Else
   Msgbox "A valid date range is required to run this report.",vbOKOnly
    Me.StartDate.SetFocus
    Exit Sub
End If

DoCmd.OpenReport "Report_1_MTD_Daily_Containment", acViewPreview, , "[DateField] Between #" & Me.StartDate & "# AND #" & Me.EndDate & "#"

Open in new window

0
 

Author Comment

by:lulu50
ID: 39859191
PartHartman

Compile error
It says Method or data member not found
0
 

Author Comment

by:lulu50
ID: 39859197
it doesnt know what Me.StartDate
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 39859209
@lulu

<I entered 2/10/2014  than I entered 2/13/2014,  I did  not get any data but I do have data in that date >

if you don't want to use a form for the date entry range,

you need two inputs

Dim DateFrom As Date, DateTo As Date
DateFrom = InputBox("Please enter a date range!", "From")
DateTo = InputBox("Please enter a date range!", "To")

If DateFrom & "" <> "" And DateTo & "" <> "" Then

DoCmd.OpenReport "Report_1_MTD_Daily_Containment", acViewPreview, , "[DateField]>=#" & DateFrom & "# And  [DateField]<=#" & DateTo & "#"

End If
0
 
LVL 35

Expert Comment

by:PatHartman
ID: 39859215
it doesnt know what Me.StartDate
Did you define a control on the form and name it StartDate?  Don't forget to specify a date format.  Short Date is what most people use.
0
 

Author Comment

by:lulu50
ID: 39859397
This is what I want, but I get three input box for the date I just need two.

Dim DateFrom As Date, DateTo As Date
DateFrom = InputBox("Please enter a date range!", "From")
DateTo = InputBox("Please enter a date range!", "To")

If IsDate(DateFrom) And IsDate(DateTo) Then
    If DateTo > DateFrom Then
    Else
        MsgBox "End Date must be greater than start date.", vbOKOnly
        DateTo.SetFocus  //this generate error
        Exit Sub
    End If
Else
   MsgBox "A valid date range is required to run this report.", vbOKOnly
   'DateFrom.SetFocus   //this generate error
    Exit Sub
End If

DoCmd.OpenReport "Report_1_MTD_Daily_Containment", acViewPreview, , "[DateField] Between #" & DateFrom & "# AND #" & DateTo & "#"
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 39859470
not sure if you will like this, but try

Dim DateFrom As Date, DateTo As Date
Do
DateFrom = InputBox("Please enter a date range!", "From")
DateTo = InputBox("Please enter a date range!", "To")

Loop Until IsDate(DateFrom) And IsDate(DateTo) And DateTo > DateFrom


DoCmd.OpenReport "Report_1_MTD_Daily_Containment", acViewPreview, , "[DateField] Between #" & DateFrom & "# AND #" & DateTo & "#"
0
 
LVL 35

Expert Comment

by:PatHartman
ID: 39859544
I'm done.
0
 
LVL 61

Expert Comment

by:mbizup
ID: 39860047
Lulu,

No points for this comment please.

What you are asking for is certainly possible, but it is NOT optimal, as you can see with the multiple parameter prompts.

Pat Hartman has gone to great lengths to show you the *best* way to accomplish this both from a user's perspective and a developer's perspective.

This sample, a 5-minute effort, includes that form-based approach, using the code she posted.  It is hands-down the best way to accomplish what you are asking for, and is the approach that almost all of the regular Experts here (including Rey) would probably use in their own clients' databases.
TestDateRange.mdb
0
 

Author Comment

by:lulu50
ID: 39877984
I've requested that this question be closed as follows:

Accepted answer: 250 points for PatHartman's comment #a39859124
Assisted answer: 0 points for lulu50's comment #a39859108
Assisted answer: 250 points for capricorn1's comment #a39859470

for the following reason:

I am sorry, for all this confusion.

Thank you all for all your help.
0
 

Author Closing Comment

by:lulu50
ID: 39877985
Thank you
0

Featured Post

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

790 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question