ExcelBanter - Calculating a date within the OLE DB query

How have you implemented this query? Is it via MSQuery, or hard-coded in VBA
with ADO or DAO, or some other method?

If done in code: You must be creating the SQL as text, either explicitly
(e.g. MyRs.Open "SELECT reqID, reqDate,...") or using a string variable
(SQLStr = "SELECT reqID, reqDate,"; MyRs.Open SQLStr...). In that case
calculate your date first and store it - formatted properly - in a string
variable, and use it in the SQL text; e.g:

MyDate = NOW() - ((WEEKDAY(NOW(),3)) + 21)
SQLStr = "SELECT reqID, reqDate,... AND completeDate '" & MyDate & "' and
requestsTbl.userID = userTbl.userID..."
Note the need to put the single quotes inside the text.

If using MSQuery, calculate your date in a cell and use the cell as a
parameter. Parameters in MSQuery are designated by using a name in [] braces
inside the criteria grid; e.g. [Start Date]. When you are back in Excel you
can right-click on the query result table and select Parameters... to specify
that the value for Start Date comes from the cell where you have entered your
formula. See MSQuery help file for more info.

The other option if using MSQuery is to set up the simple query and then
manually edit the query text. This is found in the Querytables.CommandText
property, and it is read/write, so you can read the text string as prepared
by MSQuery and do a replace function to change the portion you need to change
before you update the query. E.g:

With Worksheets("SheetName").QueryTables(1)
.CommandText = Replace(.CommandText, "10/10/2005", Format(NOW() -
((WEEKDAY(NOW(),3)) + 21),"mm/dd/yyyy"))
.Refresh
End With

--
- K Dales

"Gary Sakaguchi" wrote:

Within my OLE DB query, I would like to use a calculated date for the
selection criteria. I'm using Excel to create a summary report against a
SQL database. Completion date is one of the fields, I only want to select
those records that are completed on/after the Monday three weeks ago, NOW() -
((WEEKDAY(NOW(),3)) + 21). The report shows the prior three weeks, plus the
current week's information.

Copy of the OLE DB query - I would like to replace the "10/10/2005" with the
calculated date.

SELECT reqID, reqDate, fName, lName, inputTbl.userInput, adminsTbl.admFname,
completeDate FROM requestsTbl, userTbl, InputTbl, adminsTbl WHERE
(requestsTbl.reqDate LIKE '%2005%') AND completeDate '10/10/2005' and
requestsTbl.userID = userTbl.userID AND requestsTbl.userInputID =
inputTbl.userInputID AND (requestsTbl.assignedTo LIKE adminsTbl.adminID)
ORDER BY completeDate, admFname, reqID

Thanks,

Gary Sakaguchi