Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

2 Questions...Please help

Discussion in 'Information Technology' started by Ang, Jul 28, 2006.

  1. Ang

    Ang
    Expand Collapse
    Guest

    These questions may already be here but I can’t find them anywhere.

    Q1- I have a report that I have set to give me start date and end date;
    however, the person I am building this for wants to be able to enter the
    start and end dates in a form to pull the report. I had seen someone post
    this website on another forum:
    http://allenbrowne.com/casu-08.html,

    I went there and tried it that way but I keep getting errors for these lines:

    strWhere = strField & " Between " & Format(Me.txtStartDate, conDateFormat) _
    & " And " & Format(Me.txtEndDate, conDateFormat)

    DoCmd.OpenReport strReport, acViewPreview, , strWhere

    I am not sure what I am doing wrong.

    Q2-I have a table that list how many hours spent on each job, I have a
    report that give me accumulative total per pay period spent on each. Now I
    need a way to just pull each one and see how many hours worked on that job
    for the quarter, week, month, pay period. What would be the best way to do
    this?

    Thank you in advance for your help!!
     
  2. Loading...


  3. Graham Mandeno

    Graham Mandeno
    Expand Collapse
    Guest

    Hi Ang

    Answers inline...

    "Ang" <Ang@discussions.microsoft.com> wrote in message
    news:C170D4B1-EB8A-48C7-9DA6-0260031087A0@microsoft.com...
    > These questions may already be here but I can't find them anywhere.
    >
    > Q1- I have a report that I have set to give me start date and end date;
    > however, the person I am building this for wants to be able to enter the
    > start and end dates in a form to pull the report. I had seen someone post
    > this website on another forum:
    > http://allenbrowne.com/casu-08.html,
    >
    > I went there and tried it that way but I keep getting errors for these
    > lines:
    >
    > strWhere = strField & " Between " & Format(Me.txtStartDate, conDateFormat)
    > _
    > & " And " & Format(Me.txtEndDate, conDateFormat)
    >
    > DoCmd.OpenReport strReport, acViewPreview, , strWhere
    >
    > I am not sure what I am doing wrong.


    Neither am I because you don't say what the error is that you are getting.

    Have you declared the variables strReport, strField and strWhere, and the
    constant conDateFormat?

    Does strReport have a valid report name in it?

    Does your form have two textboxes named txtStartDate and txtEnddate which
    both contain dates?

    It should look something like this:

    Const conDateFormat = "\#mm\/dd\/yyyy\#"
    Dim strReport as String
    Dim strWhere as String
    Dim strField as String
    strReport = "Your report name here"
    strField = "[Your date field name]"
    strWhere = strField & " Between " & Format(Me.txtStartDate, conDateFormat) _
    & " And " & Format(Me.txtEndDate, conDateFormat)
    DoCmd.OpenReport strReport, acViewPreview, , strWhere

    >
    > Q2-I have a table that list how many hours spent on each job, I have a
    > report that give me accumulative total per pay period spent on each. Now I
    > need a way to just pull each one and see how many hours worked on that job
    > for the quarter, week, month, pay period. What would be the best way to do
    > this?


    Assuming you know (or can calculate) the starting and ending dates for each
    of these periods, you can use the technique you have above.

    Create your report grouped by job number, with a section footer and an empty
    Detail section. In the section footer you can put the details of the job
    (number, name, description, etc) and a textbox showing =Sum([HoursWorked]).

    Then open your report with different start and end dates depending on the
    period you want.
    --
    Good Luck!

    Graham Mandeno [Access MVP]
    Auckland, New Zealand

    > Thank you in advance for your help!!
    >
    >
     
  4. Ang

    Ang
    Expand Collapse
    Guest

    Graham, THANK YOU soooooooooo very much, you are a life saver!!!

    "Graham Mandeno" wrote:

    > Hi Ang
    >
    > Answers inline...
    >
    > "Ang" <Ang@discussions.microsoft.com> wrote in message
    > news:C170D4B1-EB8A-48C7-9DA6-0260031087A0@microsoft.com...
    > > These questions may already be here but I can't find them anywhere.
    > >
    > > Q1- I have a report that I have set to give me start date and end date;
    > > however, the person I am building this for wants to be able to enter the
    > > start and end dates in a form to pull the report. I had seen someone post
    > > this website on another forum:
    > > http://allenbrowne.com/casu-08.html,
    > >
    > > I went there and tried it that way but I keep getting errors for these
    > > lines:
    > >
    > > strWhere = strField & " Between " & Format(Me.txtStartDate, conDateFormat)
    > > _
    > > & " And " & Format(Me.txtEndDate, conDateFormat)
    > >
    > > DoCmd.OpenReport strReport, acViewPreview, , strWhere
    > >
    > > I am not sure what I am doing wrong.

    >
    > Neither am I because you don't say what the error is that you are getting.
    >
    > Have you declared the variables strReport, strField and strWhere, and the
    > constant conDateFormat?
    >
    > Does strReport have a valid report name in it?
    >
    > Does your form have two textboxes named txtStartDate and txtEnddate which
    > both contain dates?
    >
    > It should look something like this:
    >
    > Const conDateFormat = "\#mm\/dd\/yyyy\#"
    > Dim strReport as String
    > Dim strWhere as String
    > Dim strField as String
    > strReport = "Your report name here"
    > strField = "[Your date field name]"
    > strWhere = strField & " Between " & Format(Me.txtStartDate, conDateFormat) _
    > & " And " & Format(Me.txtEndDate, conDateFormat)
    > DoCmd.OpenReport strReport, acViewPreview, , strWhere
    >
    > >
    > > Q2-I have a table that list how many hours spent on each job, I have a
    > > report that give me accumulative total per pay period spent on each. Now I
    > > need a way to just pull each one and see how many hours worked on that job
    > > for the quarter, week, month, pay period. What would be the best way to do
    > > this?

    >
    > Assuming you know (or can calculate) the starting and ending dates for each
    > of these periods, you can use the technique you have above.
    >
    > Create your report grouped by job number, with a section footer and an empty
    > Detail section. In the section footer you can put the details of the job
    > (number, name, description, etc) and a textbox showing =Sum([HoursWorked]).
    >
    > Then open your report with different start and end dates depending on the
    > period you want.
    > --
    > Good Luck!
    >
    > Graham Mandeno [Access MVP]
    > Auckland, New Zealand
    >
    > > Thank you in advance for your help!!
    > >
    > >

    >
    >
    >
     

Share This Page