Welcome to SPN

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

Sign Up Now!

Using a value of a previous record in a report

Discussion in 'Information Technology' started by pltaylor3@gmail.com, Jul 28, 2006.

  1. pltaylor3@gmail.com

    pltaylor3@gmail.com
    Expand Collapse
    Guest

    I am trying to use a previous record value in a report in Access 2002.
    The backend db is an SQL server and I am trying to set the recordset
    value to
    =DLookUp([Current Lap],[pitstrategy],[OutingNumber]=[Report]![Pit
    Strategy]![OutingNumber]-1)
    and it is giving me a name error when i run the report. I got this
    code from
    http://support.microsoft.com/default.aspx?scid=kb;en-us;101081 and the
    " everywhere give me a number error. Any insight is greatly
    appreciated.
    thanks
     
  2. Loading...


  3. Allen Browne

    Allen Browne
    Expand Collapse
    Guest

    The 3 arguments for DLookup() are strings.

    The number from the report needs to be concatenated into the 3rd one:
    =DLookUp("Current Lap", "pitstrategy", "OutingNumber = " & [OutingNumber]-1)

    For more info on how to build these 3 strings, see:
    Getting a value from a table: DLookup()
    at:
    http://allenbrowne.com/casu-07.html

    --
    Allen Browne - Microsoft MVP. Perth, Western Australia.
    Tips for Access users - http://allenbrowne.com/tips.html
    Reply to group, rather than allenbrowne at mvps dot org.

    <pltaylor3@gmail.com> wrote in message
    news:1149861768.693153.69590@j55g2000cwa.googlegroups.com...
    >I am trying to use a previous record value in a report in Access 2002.
    > The backend db is an SQL server and I am trying to set the recordset
    > value to
    > =DLookUp([Current Lap],[pitstrategy],[OutingNumber]=[Report]![Pit
    > Strategy]![OutingNumber]-1)
    > and it is giving me a name error when i run the report. I got this
    > code from
    > http://support.microsoft.com/default.aspx?scid=kb;en-us;101081 and the
    > " everywhere give me a number error. Any insight is greatly
    > appreciated.
    > thanks
     
  4. pltaylor3@gmail.com

    pltaylor3@gmail.com
    Expand Collapse
    Guest

    Thanks for your reply. I tried what you suggested and still got
    #Error.
    I am currently using this
    =DLookUp("CurrentLap","pitstrategy","OutingNumber=" & [OutingNumber]-1)

    Where CurrentLap is the variable i want to end up with, pitstrategy is
    the query that i am getting the info for the report from and
    outingnumber is my sequential identifier that is grouped by descending
    order. any more ideas would be appreciated.
    thanks.

    Allen Browne wrote:
    > The 3 arguments for DLookup() are strings.
    >
    > The number from the report needs to be concatenated into the 3rd one:
    > =DLookUp("Current Lap", "pitstrategy", "OutingNumber = " & [OutingNumber]-1)
    >
    > For more info on how to build these 3 strings, see:
    > Getting a value from a table: DLookup()
    > at:
    > http://allenbrowne.com/casu-07.html
    >
    > --
    > Allen Browne - Microsoft MVP. Perth, Western Australia.
    > Tips for Access users - http://allenbrowne.com/tips.html
    > Reply to group, rather than allenbrowne at mvps dot org.
    >
    > <pltaylor3@gmail.com> wrote in message
    > news:1149861768.693153.69590@j55g2000cwa.googlegroups.com...
    > >I am trying to use a previous record value in a report in Access 2002.
    > > The backend db is an SQL server and I am trying to set the recordset
    > > value to
    > > =DLookUp([Current Lap],[pitstrategy],[OutingNumber]=[Report]![Pit
    > > Strategy]![OutingNumber]-1)
    > > and it is giving me a name error when i run the report. I got this
    > > code from
    > > http://support.microsoft.com/default.aspx?scid=kb;en-us;101081 and the
    > > " everywhere give me a number error. Any insight is greatly
    > > appreciated.
    > > thanks
     
  5. Allen Browne

    Allen Browne
    Expand Collapse
    Guest

    What is the Name of this text box?
    It cannot be the same as any of the fields in the report.

    If OutingNumber is not on the report, add a text box with that name (Visible
    = No if you wish.)

    The expression will error if OutingNumber is a null. You might want to try:
    =DLookUp("CurrentLap","pitstrategy","OutingNumber=" &
    Nz([OutingNumber],0)-1)

    To test the expression, open the Immediate Window (Ctrl+G), and enter
    something like:
    ? =DLookUp("CurrentLap","pitstrategy","OutingNumber=6" )

    --
    Allen Browne - Microsoft MVP. Perth, Western Australia.
    Tips for Access users - http://allenbrowne.com/tips.html
    Reply to group, rather than allenbrowne at mvps dot org.

    <pltaylor3@gmail.com> wrote in message
    news:1149867255.241020.149800@f6g2000cwb.googlegroups.com...
    > Thanks for your reply. I tried what you suggested and still got
    > #Error.
    > I am currently using this
    > =DLookUp("CurrentLap","pitstrategy","OutingNumber=" & [OutingNumber]-1)
    >
    > Where CurrentLap is the variable i want to end up with, pitstrategy is
    > the query that i am getting the info for the report from and
    > outingnumber is my sequential identifier that is grouped by descending
    > order. any more ideas would be appreciated.
    > thanks.
    >
    > Allen Browne wrote:
    >> The 3 arguments for DLookup() are strings.
    >>
    >> The number from the report needs to be concatenated into the 3rd one:
    >> =DLookUp("Current Lap", "pitstrategy", "OutingNumber = " &
    >> [OutingNumber]-1)
    >>
    >> For more info on how to build these 3 strings, see:
    >> Getting a value from a table: DLookup()
    >> at:
    >> http://allenbrowne.com/casu-07.html
    >>
    >> --
    >> Allen Browne - Microsoft MVP. Perth, Western Australia.
    >> Tips for Access users - http://allenbrowne.com/tips.html
    >> Reply to group, rather than allenbrowne at mvps dot org.
    >>
    >> <pltaylor3@gmail.com> wrote in message
    >> news:1149861768.693153.69590@j55g2000cwa.googlegroups.com...
    >> >I am trying to use a previous record value in a report in Access 2002.
    >> > The backend db is an SQL server and I am trying to set the recordset
    >> > value to
    >> > =DLookUp([Current Lap],[pitstrategy],[OutingNumber]=[Report]![Pit
    >> > Strategy]![OutingNumber]-1)
    >> > and it is giving me a name error when i run the report. I got this
    >> > code from
    >> > http://support.microsoft.com/default.aspx?scid=kb;en-us;101081 and the
    >> > " everywhere give me a number error. Any insight is greatly
    >> > appreciated.
    >> > thanks

    >
     
  6. pltaylor3@gmail.com

    pltaylor3@gmail.com
    Expand Collapse
    Guest

    I ran the immediate window as you suggested and got a timeout error. I
    then tried setting it to a constant number in the report and still got
    the same #error.
    Allen Browne wrote:
    > What is the Name of this text box?
    > It cannot be the same as any of the fields in the report.
    >
    > If OutingNumber is not on the report, add a text box with that name (Visible
    > = No if you wish.)
    >
    > The expression will error if OutingNumber is a null. You might want to try:
    > =DLookUp("CurrentLap","pitstrategy","OutingNumber=" &
    > Nz([OutingNumber],0)-1)
    >
    > To test the expression, open the Immediate Window (Ctrl+G), and enter
    > something like:
    > ? =DLookUp("CurrentLap","pitstrategy","OutingNumber=6" )
    >
    > --
    > Allen Browne - Microsoft MVP. Perth, Western Australia.
    > Tips for Access users - http://allenbrowne.com/tips.html
    > Reply to group, rather than allenbrowne at mvps dot org.
    >
    > <pltaylor3@gmail.com> wrote in message
    > news:1149867255.241020.149800@f6g2000cwb.googlegroups.com...
    > > Thanks for your reply. I tried what you suggested and still got
    > > #Error.
    > > I am currently using this
    > > =DLookUp("CurrentLap","pitstrategy","OutingNumber=" & [OutingNumber]-1)
    > >
    > > Where CurrentLap is the variable i want to end up with, pitstrategy is
    > > the query that i am getting the info for the report from and
    > > outingnumber is my sequential identifier that is grouped by descending
    > > order. any more ideas would be appreciated.
    > > thanks.
    > >
    > > Allen Browne wrote:
    > >> The 3 arguments for DLookup() are strings.
    > >>
    > >> The number from the report needs to be concatenated into the 3rd one:
    > >> =DLookUp("Current Lap", "pitstrategy", "OutingNumber = " &
    > >> [OutingNumber]-1)
    > >>
    > >> For more info on how to build these 3 strings, see:
    > >> Getting a value from a table: DLookup()
    > >> at:
    > >> http://allenbrowne.com/casu-07.html
    > >>
    > >> --
    > >> Allen Browne - Microsoft MVP. Perth, Western Australia.
    > >> Tips for Access users - http://allenbrowne.com/tips.html
    > >> Reply to group, rather than allenbrowne at mvps dot org.
    > >>
    > >> <pltaylor3@gmail.com> wrote in message
    > >> news:1149861768.693153.69590@j55g2000cwa.googlegroups.com...
    > >> >I am trying to use a previous record value in a report in Access 2002.
    > >> > The backend db is an SQL server and I am trying to set the recordset
    > >> > value to
    > >> > =DLookUp([Current Lap],[pitstrategy],[OutingNumber]=[Report]![Pit
    > >> > Strategy]![OutingNumber]-1)
    > >> > and it is giving me a name error when i run the report. I got this
    > >> > code from
    > >> > http://support.microsoft.com/default.aspx?scid=kb;en-us;101081 and the
    > >> > " everywhere give me a number error. Any insight is greatly
    > >> > appreciated.
    > >> > thanks

    > >
     
  7. Allen Browne

    Allen Browne
    Expand Collapse
    Guest

    Okay, so you have established that the problem is not with the report. It
    could be with the expression, or with the connection to the data.

    A previous reply gave a link for an article on how to build the expression.

    You could test the connnection with a query that uses any literal value to
    retrieve a single field (CurrentLap) from a single table (pitstrategy) with
    a single criterion (OutingNumber). When you get that to work, switch it to
    SQL View (View menu), and use that info in conjunction with the article.

    --
    Allen Browne - Microsoft MVP. Perth, Western Australia.
    Tips for Access users - http://allenbrowne.com/tips.html
    Reply to group, rather than allenbrowne at mvps dot org.

    <pltaylor3@gmail.com> wrote in message
    news:1149874643.027389.29200@i39g2000cwa.googlegroups.com...
    >I ran the immediate window as you suggested and got a timeout error. I
    > then tried setting it to a constant number in the report and still got
    > the same #error.
    > Allen Browne wrote:
    >> What is the Name of this text box?
    >> It cannot be the same as any of the fields in the report.
    >>
    >> If OutingNumber is not on the report, add a text box with that name
    >> (Visible
    >> = No if you wish.)
    >>
    >> The expression will error if OutingNumber is a null. You might want to
    >> try:
    >> =DLookUp("CurrentLap","pitstrategy","OutingNumber=" &
    >> Nz([OutingNumber],0)-1)
    >>
    >> To test the expression, open the Immediate Window (Ctrl+G), and enter
    >> something like:
    >> ? =DLookUp("CurrentLap","pitstrategy","OutingNumber=6" )
    >>
    >> <pltaylor3@gmail.com> wrote in message
    >> news:1149867255.241020.149800@f6g2000cwb.googlegroups.com...
    >> > Thanks for your reply. I tried what you suggested and still got
    >> > #Error.
    >> > I am currently using this
    >> > =DLookUp("CurrentLap","pitstrategy","OutingNumber=" & [OutingNumber]-1)
    >> >
    >> > Where CurrentLap is the variable i want to end up with, pitstrategy is
    >> > the query that i am getting the info for the report from and
    >> > outingnumber is my sequential identifier that is grouped by descending
    >> > order. any more ideas would be appreciated.
    >> > thanks.
    >> >
    >> > Allen Browne wrote:
    >> >> The 3 arguments for DLookup() are strings.
    >> >>
    >> >> The number from the report needs to be concatenated into the 3rd one:
    >> >> =DLookUp("Current Lap", "pitstrategy", "OutingNumber = " &
    >> >> [OutingNumber]-1)
    >> >>
    >> >> For more info on how to build these 3 strings, see:
    >> >> Getting a value from a table: DLookup()
    >> >> at:
    >> >> http://allenbrowne.com/casu-07.html
    >> >>
    >> >> <pltaylor3@gmail.com> wrote in message
    >> >> news:1149861768.693153.69590@j55g2000cwa.googlegroups.com...
    >> >> >I am trying to use a previous record value in a report in Access
    >> >> >2002.
    >> >> > The backend db is an SQL server and I am trying to set the recordset
    >> >> > value to
    >> >> > =DLookUp([Current Lap],[pitstrategy],[OutingNumber]=[Report]![Pit
    >> >> > Strategy]![OutingNumber]-1)
    >> >> > and it is giving me a name error when i run the report. I got this
    >> >> > code from
    >> >> > http://support.microsoft.com/default.aspx?scid=kb;en-us;101081 and
    >> >> > the
    >> >> > " everywhere give me a number error. Any insight is greatly
    >> >> > appreciated.
    >> >> > thanks
     

Share This Page