date formulas

[Guest]

I need to be able to search a table for any records that are equal to a given
date field and if no records have that date exactly to have it present the
next existing prior date. I tried doing iif(table date<=given date, table
date, null) and then having this be a total query with MAX on this column but
it doesn't do what I'm looking for. How is this criteria or expression
written?

 

[Guest]

I would combine <= in the criteria with TOP 1
SELECT TOP 1 *
FROM tblNoNameGiven
WHERE [Table Date]<=[Given Date]
ORDER BY [Table Date] DESC;

 

[Allen Browne]

This example assumes a table named Table1, with a foreign key field name
SubID that you need to match.

Type an expression like this into a fresh column in the Field row in query
design:

MaxOfDate1: (SELECT Max([Date1]) AS MaxOfDate1
FROM Table1 AS A
WHERE A.SubID = Table1.SubID
AND A.Date1 <= Table1.ID)

If subqueries are new, see:
http://allenbrowne.com/subquery-01.html

Another example:
http://www.mvps.org/access/queries/qry0020.htm

 

[Tom Ellison]

Dear skigal:

I would think what you probably could use would be a subquery returning the
MAX(somedate) WHERE somedate <= given date.

SELECT *
FROM sometable
WHERE somedate = (SELECT MAX(somedate)
FROM sometable
WHERE somedate <= [Enter Date: ])

Perhaps you can flesh this out with the names of your table and date column.

Tom Ellison