Go to previous date if date doesn't exist

[Kinger43]

I have a large table which is updated everyday. Each record will have a different date but there will be many with the same account number. I have a query which will pull one record from each account number according to a user specified start date, another query which will pull by end date, and a third query which uses both to do a calculation on another field. The problem is, if one of the first two queries does not find a record with that date then the calculation will not be done because there is no record. What I need is a way to go back one day if the specified date does not exist. I have tried using an iif statement in a report with the DateAdd function in the true part. Here is what I have

IIf([blinks_test_end]![Reading_Date]=Null,DateAdd("d",-1,[blinks_test_end]![Reading_Date]),[blinks_test_end]![Reading_Date])

[blinks_test_end]![Reading_Date] is the user specified date. When I run the report it asks for blinks_test_end, I think it doesn't recognize that this is the user specified date.

 

[Jon K]

I have a query which will pull one record from each account number according to a user specified start date,
................
................

[blinks_test_end]![Reading_Date] is the user specified date.

Need some more detail. What is [blinks_test_end]? Is it a form?

Assuming it is a form, you can use a subquery in the query like the following:

SELECT .............
FROM .............
WHERE [TableName].[DateFieldName] = (Select Top 1 [DateFieldName] from [TableName] as S where S.[DateFieldName] <=[forms]![blinks_test_end]![Reading_Date] order by [DateFieldName] Desc)
.

 

[Kinger43]

[blinks_test_end] is the query which pulls the records with the specified end date. [blinks_test_start] is the query which pulls the records with the specified start date.

 

[Jon K]

IIf([blinks_test_end]![Reading_Date]=Null, DateAdd("d",-1,[blinks_test_end]![Reading_Date]), [blinks_test_end]![Reading_Date])

Without seeing your tables and queries and how they are linked, it's hard to see exactly where the problem lies. But as I see it, when [blinks_test_end].[Reading_Date] Is Null, deducting one day from it will still be Null.

Based on how you have described the situation, I believe I have pointed you in the right direction. A subquery of (Select Top 1 [DateFieldName] from [queryName] as S where ..........) is one way to go. Since [blinks_test_end] is another query, you may need to put the subquery in the Select Clause of the query instead of the Where Clause and tweak its criteria in relation to the fields in the query. Subqueries can work on queries as well as tables.

If a subquery doesn't work, try making [blinks_test_end].[Reading_Date] return the correct record before linking it in your query.
.