access date question

If you wanted to generate a report based on data you put in a form stating that something was valid between say 1-1-09 & 3-30-09, how would you make a query that used " Between [beging date] And [ending date] " and you put in beging date 2-2-09 & ending date 2-6-09. Basically I want to have a report that tells me something is valid on a given date without typing in every day into the form. Thx
JohnTheMedicAsked:
Who is Participating?
 
rockiroadsCommented:
In the design view of the report, go to the recordsource and replace what is there "your query" with the sql
john.mdb
0
 
prachwalCommented:
what database?
0
 
rockiroadsCommented:
prachwal, answer is in the question title "access". Well I would assume so

John, why dont you open the report with the where clause of your dates from the form?

DoCmd.OpenReport "myreport", acViewPreview, , "mydatefield between #" & Me.begindate & "# AND #" & Me.enddate & "#"


0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
JohnTheMedicAuthor Commented:
Your going to have to take me to school man I don't know how to or where to put that in a form.
0
 
rockiroadsCommented:
No worries

Ok, say on your form, you got the two date fields called begindate and enddate
in this form you call the report via a click of a button, say its called cmdRunReport

your report is based on the table where the datefield you want checking is

you use the click event of the button to call your report. In forms design, click on button, look at its properties, go to the Event tab, click on the On Click event, when the button appears, click that and select code builder

private sub cmdRunReport_Click

    dim sWhere as string

'Here you define your filter
'mydatefield refers to the date field in your table
'Im assuming it is date/time but you enter a date
'so just in case, Im wrapping in datevalue to get the date only
'dates need to be wrapped in # hence we build the string with form values wrappd in #
    swhere = "datevalue(mydatefield) between #" & Me.begindate & "# AND #" & Me.enddate & "#"

    DoCmd.OpenReport "NameOfReportGoesHere", acViewPreview, , sWhere
end sub


Im off now, back online tomorrow to continue helping
0
 
JohnTheMedicAuthor Commented:
I've uploaded the file for your review I think it may help. I cleared everything I did.
john.mdb
0
 
rockiroadsCommented:
I see the report is based upon a query which does the prompting

this is my suggestion. for the report change the recordsource from the query name to the sql without the filtering

SELECT Master.ID, Master.Coupons, Master.[Coupons Type], Master.[Coupons Amount], Master.[Date Valid / From], Master.[Date Valid To] FROM Master

If you run this report, it will list all your records. Obviously this isnt what you want but this is just the start. Save your report with these changes and move onto the form.

In this form, create a button called cmdRunReport. If a wizard comes up, just cancel.

Now right click on this button, select Build Event and select code builder. it should take u to vba with auto generated code like this

Private Sub cmdRunReport_Click()

End Sub


Now here we call our report

Private Sub cmdRunReport_Click()

    Dim sReport As String       'Name of report
    Dim sWhere As String        'Criteria for report
   
    sReport = "Master Query"
    sWhere = "[Date Valid / From] BETWEEN #" & Me.Date_Valid___From & "# AND #" & Me.Date_Valid_To & "#"
   
    Debug.Print sWhere
    DoCmd.OpenReport sReport, acViewPreview, , sWhere
End Sub


Now Im assuming no times are stored else we wrap [Date Valid / From] with DateValue
0
 
JohnTheMedicAuthor Commented:
SELECT Master.ID, Master.Coupons, Master.[Coupons Type], Master.[Coupons Amount], Master.[Date Valid / From], Master.[Date Valid To] FROM Master

I'm assume you want me to put this in the report but where?
0
 
JohnTheMedicAuthor Commented:
Thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.