Sub Query

[Bruce]

I am trying to write a sub query that will evaluate a field and if it is
null, write the previous records value. The data below are actual records.
The third and fourth records do not have a value for [Check] in those fields
where there is no value, I want the query to update with the previous record
(that had a value) in this field. In this instance I am looking to have the
value of 64 updated in records three and four.

EquipmentNo NewDate WOMiles FuelMiles Check Expr1
GS102 5/9/2002 62 0 62 62
GS102 5/10/2002 0 64 64 64
GS102 5/11/2002
GS102 5/12/2002
GS102 5/13/2002 0 219 219 219
GS102 5/14/2002 0 323 323 323
GS102 5/15/2002 0 424 424 424
GS102 5/16/2002 0 535 535 535
GS102 5/17/2002 0 649 649 649

This is the sub query I am using:

Expr1: IIf([Master Mile Table 01].[Check] Is Null,(SELECT TOP 1 Dupe.Check
FROM [Master Mile Table 01] AS Dupe
WHERE (Dupe.EquipmentNo = [Master Mile Table 01].[EquipmentNo] And
Dupe.NewDate=[Master Mile Table 01].[NewDate]) AND Dupe.NewDate < [Master
Mile Table 01].[NewDate] AND [Master Mile Table 01].[Check] Is Not Null
ORDER BY Dupe.NewDate DESC, Dupe.EquipmentNo),[Master Mile Table
01].[Check])

 

[Lord Kelvan]

thst is not going to do anything because your conditions are where the
dates are the same and the dates are greater than the last

use

Expr1: IIf([Master Mile Table 01].[Check] Is Null,(SELECT
dupe.Check
FROM [Master Mile Table 01] AS
Dupe
WHERE Dupe.EquipmentNo = [Master Mile Table 01].[EquipmentNo] And
Dupe.NewDate=dateadd("d",-1,[Master Mile Table 01].[NewDate]) and
[Master Mile Table 01].[Check] Is Not Null)

that will return the next previous value which is not null

hope this helps

Regards
Kelvan

 

[Bruce]

Kelan,

Thank you for your reply. Late last night I realized the error was in the
date alignment. I then replaced the = sign with the < sign and it produced
the results I was seeking. I just got too close and kept making the same
mistake over and over.