initialize a parameter in MS Access query

Question

In MS Access 2010 I am trying to declare and initialize a variable in a query, then display the contents of that variable. The reason for doing this is to use the parameter in a more complicated query as part of a filter. Please note that for this particular case this task must be done in a query object, not in VBA. Here is the code thus far:

PARAMETERS @Date DATE;
SELECT TOP 1 FORMAT(LastUpdated, "yyyy-mm-dd") AS @Date FROM Table1 GROUP BY FORMAT(LastUpdated, "yyyy-mm-dd") ORDER BY FORMAT(LastUpdated, "yyyy-mm-dd") DESC;
SELECT @Date;

This results in an error message: "Characters found after end of SQL statement."

If this can be modified to work the last line of code will be replaced with the more complex query that needs to use @Date in a filter. The other requirement is that it has to be contained within one query object.

An Access query is not a Stored procedure, it is like a View. You will have to integrate the above statement with the rest into one SELECT statement. — Andre

Brad Brad · Accepted Answer · 2016-07-13T20:30:43

In Access you don't need to use the @ to prefix parameters. Parameters are inferred as any column which cannot be resolved. So if your table does not have a date column then using date is sufficient for "declaring" a parameter. However maybe something like p_date can separate it from the data type DATETIME/DATE

I don't know what exactly you are expecting with this

FORMAT(LastUpdated, "yyyy-mm-dd") AS @Date

It seems to be trying to assign an alias to FORMAT(LastUpdated, "yyyy-mm-dd") as the parameter value of @Date?

An Access query cannot return multiple result sets so your SELECT @Date; is ultimately what is causing the "Characters found after end of SQL statement." error message.

initialize a parameter in MS Access query

1 Answers