Using a value of a previous record in a report

[pltaylor3]

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

 

[Allen Browne]

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]

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.

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.

Reply to group, rather than allenbrowne at mvps dot org.

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

 

[Allen Browne]

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.

Reply to group, rather than allenbrowne at mvps dot org.

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.

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.

Reply to group, rather than allenbrowne at mvps dot org.

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

 

[pltaylor3]

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.

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.

Reply to group, rather than allenbrowne at mvps dot org.

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.

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.

Reply to group, rather than allenbrowne at mvps dot org.

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

 

[Allen Browne]

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.

Reply to group, rather than allenbrowne at mvps dot org.

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.

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" )

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

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