DLookup Help

[Bob Martin]

I have been trying to create a database that will track
vehicle mileage for multiple vehicles. And I think I'm
close (with help from Microsoft Access MVP staff of
course). The biggest hurdle has been when the vehicle
was not used for the period, I have not been able to
retrieve the correct previous odometer reading. The last
response from the MVP staff suggested that I use the date
in the Dlookup expression rather than the Odometer
reading. I think this will resolve the problem, however,
for some reason, my formula doesn't produce any results.
The Dlookup formula I used was as follows:

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

Any ideas why it doesn't produce any numbers? Any other
suggestions?

I was going to attach the test database to this message
but I couldn't figure out how to do it.

Bob

 

[Wayne Morgan]

Bob,

I answered your reply in the original thread a few minutes ago. Try date
delimiters around the date value and see if that helps.

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

 

[Roger Carlson]

Dates must be delimited with #
PREV: DMax("[Odometer]", "TestTable", "[VehID] = " &
[VehID] & " AND [DateRead] < #" & [DateRead]& "#")

 

[Bob Martin]

Thank You!!

-----Original Message-----
Bob,

I answered your reply in the original thread a few minutes ago. Try date
delimiters around the date value and see if that helps.

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

--
Wayne Morgan
Microsoft Access MVP

I have been trying to create a database that will track
vehicle mileage for multiple vehicles. And I think I'm
close (with help from Microsoft Access MVP staff of
course). The biggest hurdle has been when the vehicle
was not used for the period, I have not been able to
retrieve the correct previous odometer reading. The last
response from the MVP staff suggested that I use the date
in the Dlookup expression rather than the Odometer
reading. I think this will resolve the problem, however,
for some reason, my formula doesn't produce any results.
The Dlookup formula I used was as follows:

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

Any ideas why it doesn't produce any numbers? Any other
suggestions?

I was going to attach the test database to this message
but I couldn't figure out how to do it.

Bob

.

 

[Bob Martin]

Thank You!

-----Original Message-----
Dates must be delimited with #
PREV: DMax("[Odometer]", "TestTable", "[VehID] = " &
[VehID] & " AND [DateRead] < #" & [DateRead]& "#")

I have been trying to create a database that will track
vehicle mileage for multiple vehicles. And I think I'm
close (with help from Microsoft Access MVP staff of
course). The biggest hurdle has been when the vehicle
was not used for the period, I have not been able to
retrieve the correct previous odometer reading. The last
response from the MVP staff suggested that I use the date
in the Dlookup expression rather than the Odometer
reading. I think this will resolve the problem, however,
for some reason, my formula doesn't produce any results.
The Dlookup formula I used was as follows:

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

Any ideas why it doesn't produce any numbers? Any other
suggestions?

I was going to attach the test database to this message
but I couldn't figure out how to do it.

Bob

.