Closest date in a set of tables

[kunguito]

I don't know how to build an SQL query that:

Given a set of tables of the same structure and being a date one of their fields, retrieves the closest date to a parameter input date.

qry = " SELECT TOP 1 Date_stock " & _
"FROM " & _
"(SELECT Date_stock FROM table1 GROUP BY Date_stock; Union " & _
"SELECT Date_stock FROM table2 GROUP BY Date_stock; Union " & _
"SELECT Date_stock FROM table3 GROUP BY Date_stock;) " & _
"ORDER BY Abs(#" & Format(InputDate, ("mm/dd/yyyy")) & "#-CDate(Date_stock)) ASC;"


VBA says that there's a mistake in the FORM clause.
There must be a much simpler way to perform that. For example, instead of sorting the whole thing just retrieving a minimum.

[matthewspatrick]

Just remove all the semicolons.

[kunguito]

Why would I do that? I am passing the query as a string...

[matthewspatrick]

Semicolons signal the end of a statement, so in your case you ought not have them after the queries bundled up into the UNION operator expression.

In Access and SQL Server the semicolons are totally optional, so my advice is to leave them out, since putting them in at the wrong places will cause errors.

[kunguito]

Hi Patrick,

I am assigning the query to a string variable. Actually when I read the value I see no semicolon. I'll try it your way even though I'm quite sure of that part.

But let's just stick to my question (quotes aside).

Given a set of tables of the same structure and being a date one of their fields, retrieves the closest date to a parameter input date.


Thanks Patrick!!