Welcome to SPN

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

Sign Up Now!
  1. Guest ji, please consider donating today!
      Become a Supporter    ::   Make a Contribution   
    Monthly Recurring Target: $300 :: Achieved: $95

Open Report adding number and date criteria

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

  1. Garret

    Garret
    Expand Collapse
    Guest

    Hello,

    Here is my problem:
    This line opens up my report:

    DoCmd.OpenReport "rptCalibrationDue", acViewPreview, , _
    "tblGages.LastCalib <= #" & dteCalibDue & "#"

    dteCalibDue is a date entered by the user through an inputbox.
    tblGages.LastCalib is a field that holds a date.

    So all records with a LastCalib date that occurred before the date
    entered show up. However, there is a field called "Interval" on the
    same table. This interval represents a number of months. Gages are
    Calibrated every (Interval #) of months. The interval is different for
    each Gage.
    What I want to do is add that # of months to the field, LastCalib, so
    the report will show the records that have a LastCalib + Interval
    months that are <= (before or on) the user input date.

    I hope this makes sense. I'll try to give an example:

    ABC Gage was LastCalibrated 4/1/2006, DEF Gage was LastCalibrated
    2/1/2006.
    ABC Gage has an Interval of 3 (3 months), DEF has an Interval of 10
    months.
    The user enters a date in the inbox of 8/1/2006.
    ABC WILL be displayed on the report because the total would be
    7/1/2006.
    DEF WILL NOT be displayed on the report because the total would be
    12/1/2006.

    I can answer any questions anyone may have. I really appreciate
    anyone's attempt to understand this :).
     
  2. Loading...


  3. Michael H

    Michael H
    Expand Collapse
    Guest

    Garret,

    I'm not sure if it will work, but try this:
    DoCmd.OpenReport "rptCalibrationDue", acViewPreview, , _
    "DateAdd(""m"",Interval,tblGages.LastCalib) <= #" & dteCalibDue & "#"

    This way, rather than comparing the LastCalib field to dteCalibDue, you are
    using the DateAdd function to add the number of months specified by the
    Interval field to LastCalib, and comparing that value to dteCalibDue.

    The only thing I'm not sure about is the double quotes around the m. If
    that doesn't work, try something like this instead:
    DoCmd.OpenReport "rptCalibrationDue", acViewPreview, , _
    "DateAdd(" & chr(34) & "m" & chr(34) & ",Interval,tblGages.LastCalib) <= #"
    & dteCalibDue & "#"

    -Michael


    "Garret" wrote:

    > Hello,
    >
    > Here is my problem:
    > This line opens up my report:
    >
    > DoCmd.OpenReport "rptCalibrationDue", acViewPreview, , _
    > "tblGages.LastCalib <= #" & dteCalibDue & "#"
    >
    > dteCalibDue is a date entered by the user through an inputbox.
    > tblGages.LastCalib is a field that holds a date.
    >
    > So all records with a LastCalib date that occurred before the date
    > entered show up. However, there is a field called "Interval" on the
    > same table. This interval represents a number of months. Gages are
    > Calibrated every (Interval #) of months. The interval is different for
    > each Gage.
    > What I want to do is add that # of months to the field, LastCalib, so
    > the report will show the records that have a LastCalib + Interval
    > months that are <= (before or on) the user input date.
    >
    > I hope this makes sense. I'll try to give an example:
    >
    > ABC Gage was LastCalibrated 4/1/2006, DEF Gage was LastCalibrated
    > 2/1/2006.
    > ABC Gage has an Interval of 3 (3 months), DEF has an Interval of 10
    > months.
    > The user enters a date in the inbox of 8/1/2006.
    > ABC WILL be displayed on the report because the total would be
    > 7/1/2006.
    > DEF WILL NOT be displayed on the report because the total would be
    > 12/1/2006.
    >
    > I can answer any questions anyone may have. I really appreciate
    > anyone's attempt to understand this :).
    >
    >
     
Since you're here... we have a small favor to ask...

More people are visiting & reading SPN than ever but far fewer are paying to sustain it. Advertising revenues across the online media have fallen fast. So you can see why we need to ask for your help. Donating to SPN's is vote for free speech, for diversity of opinions, for the right of the people to stand up to religious bigotry. Without any affiliation to any organization, this constant struggle takes a lot of hard work to sustain as we entirely depend on the contributions of our esteemed writers/readers. We do it because we believe our perspective matters – because it might well be your perspective, too... Fund our efforts and together we can keep the world informed about the real Sikh Sikhi Sikhism. If everyone who writes or reads our content, who likes it, helps us to pay for it, our future would be much more secure. Every Contribution Matters, Contribute Generously!

    Become a Supporter      ::     Make a Contribution     



Share This Page