MS-Query2000 Criteria value = "Today"?

  • Thread starter Thread starter Peter Reefman
  • Start date Start date
P

Peter Reefman

I'm trying to obtain a record from a database table based
on proximity to today's date, and have used NOW() or DATE
() in other queries in other products such as SQL Server
Query Analyser, but have come up short on how to put a
parameter such as Now() into the Value for the criteria on
the Date Field selected in the MS-Query Design process.

I'll be using this to feed back into a cell in an Excel
Spreadsheet Template which will in turn be used in another
data query. I need this single query to replace a vlookup
function on a period end dates Named Range that needs to
be revised each financial year, which probably won't get
done if I'm not there to do it!

Seems simple, but there doesn't seem to be any docco out
there on anything like this.
 
Goose! I found it myself by looking in the Transact-SQL
Help in SQL Server Query Analyser.

The function needed is GETDATE() which returns the current
system Date/Time. Works a treat!

My SQL Statement looks something like:

SELECT Top 1 ACP_PERIOD, ACP_DATEFROM, ACP_DATETO FROM
ACP_ACCPERIOD Where ACP_DATETO < GetDate() ORDER BY
ACP_DATETO DESC

Which returns me the most recently completed Accounting
Period details.

HTH someone else!

Peter R.
 
You can add criteria to the query:

1. Select a cell in the extract range
2. Choose Data>Import External Data>Edit Query
3. Go to the last step of the Query wizard, and choose
'View data or edit query', click Finish
4. In MS Query, choose View>Criteria
5. Select a criteria field, and in the cell below, type
the criteria, e.g. Date()
You can include an operator, if required, e.g. <Date()
6. Choose File>Return Data to Microsoft Excel
 
Back
Top