Using prior records in calculations?

B

Bob Martin

I have a project where I need to calculate the difference
between the current record (which is an odometer reading)
and the prior record for 200+ vehicles.

I've tried using the following Dlookup formula:

PREV: DMax("[Odometer]", "[tablename]", "[VehID] = " &
[VehID] & " AND [Odometer] < " & [Odometer])

The DMax formula produces the following information:

Date VehID Odometer PREV
1/1/04 007 12919 12800
2/1/04 007 12941 12919
3/1/04 007 12941 12919
3/15/04 007 13500 12941

The problem is the number we need for the PREV on 3/1/04
is 12941 not 12919.
(I experience the same problem when I used the solution
presented in Microsoft Knowledge Base Article - 120273.)

How can I get the reading to remain the same if the
vehicle has not been used during the period?

The overall result that I am looking for is to calculate
how much each vehicle was used in the period.


Can anyone help me resolve this problem? I am using
Access97.
 
W

Wayne Morgan

PREV: DMax("[Odometer]", "[tablename]", "[VehID] = " &
[VehID] & " AND [Odometer] < " & [Odometer])
Click to expand...

It appears that you are using the < on Odometer to remove the current
reading from the selection criteria. This is fine if the vehicle has been
used, but will cause the problem you mention if the vehicle hasn't been
used. Let's try a different field here and see if that helps.

PREV: DMax("[Odometer]", "[tablename]", "[VehID] = " & [VehID] & " AND
[DateField] < " & [DateField])

This would force the reading to be from an earlier date and remove the
Odometer problem if the vehicle hasn't been used. If your date field is
named Date, I recommend changing it. Date is a reserved word (it returns the
current date) and may cause problems.

--
Wayne Morgan
Microsoft Access MVP


Bob Martin said:
I have a project where I need to calculate the difference
between the current record (which is an odometer reading)
and the prior record for 200+ vehicles.

I've tried using the following Dlookup formula:

PREV: DMax("[Odometer]", "[tablename]", "[VehID] = " &
[VehID] & " AND [Odometer] < " & [Odometer])

The DMax formula produces the following information:

Date VehID Odometer PREV
1/1/04 007 12919 12800
2/1/04 007 12941 12919
3/1/04 007 12941 12919
3/15/04 007 13500 12941

The problem is the number we need for the PREV on 3/1/04
is 12941 not 12919.
(I experience the same problem when I used the solution
presented in Microsoft Knowledge Base Article - 120273.)

How can I get the reading to remain the same if the
vehicle has not been used during the period?

The overall result that I am looking for is to calculate
how much each vehicle was used in the period.


Can anyone help me resolve this problem? I am using
Access97.
Click to expand...
 
B

Bob Martin

I tried your suggestion. I renamed the date field to
DateRead and replaced [Odometer] < " & [Odometer]) with
[DateRead] < " & [DateRead]) and the query results were
blank. Any ideas why?

Bob
-----Original Message-----
PREV: DMax("[Odometer]", "[tablename]", "[VehID] = " &
[VehID] & " AND [Odometer] < " & [Odometer])
Click to expand...

It appears that you are using the < on Odometer to remove the current
reading from the selection criteria. This is fine if the vehicle has been
used, but will cause the problem you mention if the vehicle hasn't been
used. Let's try a different field here and see if that helps.

PREV: DMax("[Odometer]", "[tablename]", "[VehID] = " & [VehID] & " AND
[DateField] < " & [DateField])

This would force the reading to be from an earlier date and remove the
Odometer problem if the vehicle hasn't been used. If your date field is
named Date, I recommend changing it. Date is a reserved word (it returns the
current date) and may cause problems.

--
Wayne Morgan
Microsoft Access MVP


I have a project where I need to calculate the difference
between the current record (which is an odometer reading)
and the prior record for 200+ vehicles.

I've tried using the following Dlookup formula:

PREV: DMax("[Odometer]", "[tablename]", "[VehID] = " &
[VehID] & " AND [Odometer] < " & [Odometer])

The DMax formula produces the following information:

Date VehID Odometer PREV
1/1/04 007 12919 12800
2/1/04 007 12941 12919
3/1/04 007 12941 12919
3/15/04 007 13500 12941

The problem is the number we need for the PREV on 3/1/04
is 12941 not 12919.
(I experience the same problem when I used the solution
presented in Microsoft Knowledge Base Article - 120273.)

How can I get the reading to remain the same if the
vehicle has not been used during the period?

The overall result that I am looking for is to calculate
how much each vehicle was used in the period.


Can anyone help me resolve this problem? I am using
Access97.
Click to expand...


.
Click to expand...
 
W

Wayne Morgan

You may need to enclose the value in date delimiters:

PREV: DMax("[Odometer]", "[tablename]", "[VehID] = " & [VehID] & " AND
[DateRead] < #" & [DateRead] & "#")


--
Wayne Morgan
Microsoft Access MVP


Bob Martin said:
I tried your suggestion. I renamed the date field to
DateRead and replaced [Odometer] < " & [Odometer]) with
[DateRead] < " & [DateRead]) and the query results were
blank. Any ideas why?

Bob
-----Original Message-----
PREV: DMax("[Odometer]", "[tablename]", "[VehID] = " &
[VehID] & " AND [Odometer] < " & [Odometer])
Click to expand...

It appears that you are using the < on Odometer to remove the current
reading from the selection criteria. This is fine if the vehicle has been
used, but will cause the problem you mention if the vehicle hasn't been
used. Let's try a different field here and see if that helps.

PREV: DMax("[Odometer]", "[tablename]", "[VehID] = " & [VehID] & " AND
[DateField] < " & [DateField])

This would force the reading to be from an earlier date and remove the
Odometer problem if the vehicle hasn't been used. If your date field is
named Date, I recommend changing it. Date is a reserved word (it returns the
current date) and may cause problems.

--
Wayne Morgan
Microsoft Access MVP


I have a project where I need to calculate the difference
between the current record (which is an odometer reading)
and the prior record for 200+ vehicles.

I've tried using the following Dlookup formula:

PREV: DMax("[Odometer]", "[tablename]", "[VehID] = " &
[VehID] & " AND [Odometer] < " & [Odometer])

The DMax formula produces the following information:

Date VehID Odometer PREV
1/1/04 007 12919 12800
2/1/04 007 12941 12919
3/1/04 007 12941 12919
3/15/04 007 13500 12941

The problem is the number we need for the PREV on 3/1/04
is 12941 not 12919.
(I experience the same problem when I used the solution
presented in Microsoft Knowledge Base Article - 120273.)

How can I get the reading to remain the same if the
vehicle has not been used during the period?

The overall result that I am looking for is to calculate
how much each vehicle was used in the period.


Can anyone help me resolve this problem? I am using
Access97.
Click to expand...


.
Click to expand...
Click to expand...
 
B

Bob Martin

Thanks Again!!
-----Original Message-----
You may need to enclose the value in date delimiters:

PREV: DMax("[Odometer]", "[tablename]", "[VehID] = " & [VehID] & " AND
[DateRead] < #" & [DateRead] & "#")


--
Wayne Morgan
Microsoft Access MVP


I tried your suggestion. I renamed the date field to
DateRead and replaced [Odometer] < " & [Odometer]) with
[DateRead] < " & [DateRead]) and the query results were
blank. Any ideas why?

Bob
-----Original Message-----
PREV: DMax("[Odometer]", "[tablename]", "[VehID] = " &
[VehID] & " AND [Odometer] < " & [Odometer])

It appears that you are using the < on Odometer to remove the current
reading from the selection criteria. This is fine if
Click to expand...
the
vehicle has been
used, but will cause the problem you mention if the vehicle hasn't been
used. Let's try a different field here and see if that helps.

PREV: DMax("[Odometer]", "[tablename]", "[VehID] = " & [VehID] & " AND
[DateField] < " & [DateField])

This would force the reading to be from an earlier
Click to expand...
date
and remove the
Odometer problem if the vehicle hasn't been used. If your date field is
named Date, I recommend changing it. Date is a
Click to expand...
reserved
word (it returns the
current date) and may cause problems.

--
Wayne Morgan
Microsoft Access MVP


"Bob Martin" <[email protected]>
Click to expand...
wrote
in message
I have a project where I need to calculate the difference
between the current record (which is an odometer reading)
and the prior record for 200+ vehicles.

I've tried using the following Dlookup formula:

PREV: DMax("[Odometer]", "[tablename]", "[VehID] = " &
[VehID] & " AND [Odometer] < " & [Odometer])

The DMax formula produces the following information:

Date VehID Odometer PREV
1/1/04 007 12919 12800
2/1/04 007 12941 12919
3/1/04 007 12941 12919
3/15/04 007 13500 12941

The problem is the number we need for the PREV on 3/1/04
is 12941 not 12919.
(I experience the same problem when I used the solution
presented in Microsoft Knowledge Base Article - 120273.)

How can I get the reading to remain the same if the
vehicle has not been used during the period?

The overall result that I am looking for is to calculate
how much each vehicle was used in the period.


Can anyone help me resolve this problem? I am using
Access97.



.
Click to expand...
Click to expand...


.
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

DLookup Help 4
Comparing a Field to a Field in a Prior Record 3
Find MAX of column from using 2 criterias 3
Populate Field Based on Value of Previous Record 15
Prefilling information in a form based on Vehicle number and date 3
Meter Roll Over 7
//time calculations 4
Asking in a different way for petrol sheet help 1

Top