[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 199
  • Last Modified:

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
0
JohnTheMedic
Asked:
JohnTheMedic
  • 4
  • 4
1 Solution
 
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
 
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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
 
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
 
JohnTheMedicAuthor Commented:
Thanks
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now