Results 1 to 11 of 11
  1. #1
    Join Date
    Sep 2009
    Posts
    6

    Question Unanswered: MS Access 2007: Report showing rows of dates based on a supplied date range

    Hi. I'll try to keep this simple. I have to create a Nuclear Instrumentation Quality Control report (MS Access 2007) that prints multiple rows under the following field headings: Date, Range, Observed, Setting, By. The Observed, Setting and By row values will all be blank (filled in by user after report is printed). The Date has to populate row by row (day by day) based on a date range supplied by the user through a form. This I'm not sure how to do. Based on that row's date the Range value will show a calculated value based on the decay of a certain isotope. I think I can figure this range value out once I understand how to populated the date.

    I know there is vb involved with a likely loop. Can anyone help me with this code to kick me off in the right direction? Thanks in advance. I would classify myself as a beginner by the way. If it helps the report layout would look something like below:

    DATE RANGE OBSERVED SETTING BY
    03/25/08 2782 to 3220 _________ ________ _____
    03/26/08 2780 to 3218 _________ ________ _____
    and so forth.....until the EndDate given by the user on a form.

    Thanks!

  2. #2
    Join Date
    Sep 2002
    Location
    South Wales
    Posts
    580
    OK, Lets assume you have a calendar table populated with dates say:

    tblCalendar (Table name)
    datCalendarDate (field name)

    The date part of the query would just be something like;

    SELECT datCaleandarDate FROM tblCalendar WHERE datCalendarDate >= Forms!YourFormName.yourStartDateControl AND <=Forms!YourFormName.yourStartEndControl

    Lets also assume that you can get a handle on the isotope from this form too, you can add a calculated field which could use a function to return the range based on the <currrentrow> date value say

    SELECT datCaleandarDate, strRange:ReturnRange(Forms!YourFormName.yourStartD ateControl, datCalendarDate) FROM tblCalendar WHERE datCalendarDate >= Forms!YourFormName.yourStartDateControl AND <=Forms!YourFormName.yourStartEndControl

    Then write a function ReturnRange to accept 2 date parameters and return the range as a string - you can use DateDiff to calculate the difference between the 2 dates and calculate your expected values.

    <edit> The ReturnRange would probably have another parameter referencing details of the given isotope (say chosen on the user form) - guessing you might have a table of isotopes and their half-lives etc? (Nuclear physics has never been one of my strong points I'm afraid)...
    Last edited by garethdart; 09-28-09 at 11:57.
    Windows Server 2003-8 / Terminal Services / SQL 2000 / Access 2003 / Office 2003-7 / Exchange 2003-7 / Blackberry Enterprise Server / AutoCAD / Lambert And Butler / Red Bull

  3. #3
    Join Date
    Sep 2009
    Posts
    6
    garethdarth, you seem to be one of the few who understand what I am attempting to do on this report. You lose me at the ReturnRange function - perhaps b\c I am at a loss of how to do this. Here's where I'm at: Currently, (using some other online help) I've created three tables (tblDays, tblMonths, tblYears). I created a report (rptInstrumentQC) based on a query of these three tables. The report initial asks me my start date and end date and then generates these dates down the report page, one after another.

    So I think at this point I've acheived 2/3 of what you mentioned above (that's not to say I won't end up redoing the date generation per your instructions). I'm having two problems:

    1) I also have a table (tblNuclearInstrumentationQC) that collects info (from a form f_ReportMenu) such as the isotope, half-life, activity units, etc... I'm trying to pull some of this information into the Page Header of my report but when I open my report a little window pops up over and over for an input of each field I'm trying to pull in. I have each field linked to the table through the control source in the properties sheet. I would think this should be automatically pulled in from the table.

    2) In the Detail portion of the body, I placed another text box next to the box that generates my dates. The idea is for this box to populate my calculated decayed values next to each day on the report. I guess this is where I'm lost. Your statement, "Then write a function ReturnRange to accept 2 date parameters and return the range as a string - you can use DateDiff to calculate the difference between the 2 dates and calculate your expected values" sounds just like what I need. Sorry, but how do I use DateDiff to do this? Back on my tblNuclearInstrumentationQC table I have a field called CalibrationDate. The dates generated on the report would get compared to this CalibrationDate and that difference would be used as a variable in my decay correction formula. I guess I just don't know where to use DateDiff or where to put my formula to get these values to show up next to my dates.

    Thanks!

    Kent

  4. #4
    Join Date
    Sep 2002
    Location
    South Wales
    Posts
    580
    Hi Kent,

    Are you able to post a .zip version of what you have so far (excluding any sensitive information)?

    If not it's not a problem but it would help in speeding my response.
    Windows Server 2003-8 / Terminal Services / SQL 2000 / Access 2003 / Office 2003-7 / Exchange 2003-7 / Blackberry Enterprise Server / AutoCAD / Lambert And Butler / Red Bull

  5. #5
    Join Date
    Sep 2009
    Posts
    6
    Hi garethdart - thanks for looking at this for me. I deleted all other non-relevant part of the database - not for security reasons but just that it wasn't so big and so much for you to sort through.

    Take a look at the f_ReportMenu form first. You should see some info in the fields being pulled in from the table tblNuclearInstrumentationQC. Enter any Begin Date and End Date and click the phone button underneath. You will have to enter parameter values in the little pop-up windows before the report pulls up. Just enter anything you want in order to get to the report. This is obviously one of my problems.

    The report shows #error down next to all the dates. I was just playing with it to see if I can get any type of formula to work based on the each date. This is my other issue I'm struggling with.

    Additionally - it would be nice if my dates would show up in more than one column per page. Is this possible?

    Thanks again!

    Kent
    Attached Files Attached Files

  6. #6
    Join Date
    Sep 2002
    Location
    South Wales
    Posts
    580
    Thanks Kent,

    There is a little bit to do here - please be patient and I will update an example within about 48hrs.

    In the meantime, can you explain how the calculation should be made for each date? (This can be 'slotted in' at a later stage if requried - I could provide an example with a dummy calculation if needs be).
    Windows Server 2003-8 / Terminal Services / SQL 2000 / Access 2003 / Office 2003-7 / Exchange 2003-7 / Blackberry Enterprise Server / AutoCAD / Lambert And Butler / Red Bull

  7. #7
    Join Date
    Sep 2002
    Location
    South Wales
    Posts
    580
    Kent;

    What date format do you use please DD/MM/YYYY or MM/DD/YYYY?
    Windows Server 2003-8 / Terminal Services / SQL 2000 / Access 2003 / Office 2003-7 / Exchange 2003-7 / Blackberry Enterprise Server / AutoCAD / Lambert And Butler / Red Bull

  8. #8
    Join Date
    Sep 2002
    Location
    South Wales
    Posts
    580

    Question Example

    Kent

    This is a simple example of using an inline function.

    I've added a table called tblDates and populated it with a module basPopulateDates (up until the start of next year).

    I've added a query which references the datStartDate and datEndDate unbound controls on your form (i've renamed them) and a module called ReturnValue.

    Currently ReturnValue accepts 2 parameters, the datStartDate on the form and the 'Current Row Date'. (It can take other parameters if required)

    This function simply multiplies the number of days difference by 1.1234
    ___________________________
    Public Function ReturnValue(datStartDate As Variant, datCurrentDate As Variant)

    'This is where we calculate the value / range for a given date
    Dim intDays As Integer

    intDays = DateDiff("d", datStartDate, datCurrentDate)
    ReturnValue = intDays * 1.234

    End Function
    ____________________________

    Then the report opens and displays these results.

    You will have to explain exactly how to calculate the required results and I can modify the above code for you.

    Hope this helps.
    Attached Files Attached Files
    Windows Server 2003-8 / Terminal Services / SQL 2000 / Access 2003 / Office 2003-7 / Exchange 2003-7 / Blackberry Enterprise Server / AutoCAD / Lambert And Butler / Red Bull

  9. #9
    Join Date
    Sep 2009
    Posts
    6
    Hi - thanks again for looking into this for me. I will look at your updated database when I get back into work tomorrow. Unfortunately I'm home now trying to post this with a notebook (without MS Access) and dial-up. I can answer a couple of your questions though.

    ** What comes into play with the calculation are two variables: 1) the units that the user decides to use (via the combo box on the form). This can be let's say mCi (millicuries), which is a unit of radioactivity. 2) the half-life that also the user supplies via the form. I require the user to enter these in days.

    This is the formula: Current activity = Calibrated Activity * EXP [(-.693 * Time) / Half-Life]

    Where Time is the difference between the Calibrated date that the user gives me and each date that gets generated on the report. Activity and Half-Life is given by the user also (which I think I've mentioned).

    ** It doesn't matter which date format I use - but currently I think I'm using mm/dd/yyyy

    Thanks a lot! I'll look at your example tomorrow.

  10. #10
    Join Date
    Sep 2009
    Posts
    6
    Hi garethdart -

    Thanks for your help with my database. I took your sample file and was able to figure out some things based on what you did. I have my report looking how I need it now, except for one thing. I would like my data to go down the left side of the page and then start again in a second column down the right side of the page. Is there a way to force the data into two columns? Attached is your file you sent to me that I have modified...

    Thanks a lot for your help....

    Kent
    Attached Files Attached Files

  11. #11
    Join Date
    Sep 2002
    Location
    South Wales
    Posts
    580
    You just need to configure:-

    Page Setup > Columns

    I'm sure you can do that yourself
    Last edited by garethdart; 10-07-09 at 06:07.
    Windows Server 2003-8 / Terminal Services / SQL 2000 / Access 2003 / Office 2003-7 / Exchange 2003-7 / Blackberry Enterprise Server / AutoCAD / Lambert And Butler / Red Bull

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •