Welcome to SPN

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

Sign Up Now!

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 :).
    >
    >
     

Share This Page