Re: Refining query

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance


"SMT" wrote:
I am using this query to export directly to an excel file. The original
query worked perfectly until I had an additional requirement to limit the
projects to those that had an actual_complete_date of no more than 14 days
past the reporting date that was chosen in Forms!frmRptQuery!Combo0.


The user choses reporting date from Forms!frmRptQuery!Combo0.
The query shows the latest data for projects on or before the date chosen
in
the Forms!frmRptQuery!Combo0.
One exception - If a project has an actual_complete_date greater than 14
days after the date selected in Forms!frmRptQuery!Combo0, I no longer want
to
show that project in my query (export file).

Hi SMT,

There may be a better way, but..

with "comments":
{you'll need to delete them of course, but
if I get this wrong, maybe you'll see it}

WHERE
( 'start WHERE
( 'start first AND
F.DateID =
( 'start first subquery
SELECT Max(X.DateID)
FROM funding_history AS X
Where
X.DateID <= Forms!frmRptQuery!Combo0
AND
X.proj_exec_id=F.proj_exec_id
) 'end first subquery
) 'end first AND
AND
( 'start second AND
( 'start EQUAL stmt
F.proj_exec_id =
( 'start second subquery
SELECT Max(NZ(T.proj_exec_id, 0))
FROM funding_history AS T
WHERE
T.actual_complete_date> Forms!frmRptQuery!Combo0 + 14
AND
T.proj_exec_id = F.proj_exec_id
) 'end second subquery
) 'end EQUAL stmt
= False
) 'end second AND
); 'end WHERE

====================

Why not just use?
{I'm 99.9% sure this would not be a good idea}

....
AND
F.proj_exec_id NOT IN
(SELECT T.proj_exec_id
FROM funding_history AS T
WHERE
T.actual_complete_date> Forms!frmRptQuery!Combo0 + 14)


short answer -- NULL

For example, what if there are no projects
w/completedate 15+ days out when you run query,
so you have equivalent of

....
AND
F.proj_exec_id NOT IN NULL

I'm pretty sure that would evaluate to "False"
so your query would not return any records.

=========================

I do not use "form dates" in queries, so the following
piece from above may be wrong

T.actual_complete_date> Forms!frmRptQuery!Combo0 + 14

it may have to be

T.actual_complete_date> CDate(Forms!frmRptQuery!Combo0) + 14

or

T.actual_complete_date> DateAdd("d",14,Forms!frmRptQuery!Combo0)

or

T.actual_complete_date> DateAdd("d",14,CDate(Forms!frmRptQuery!Combo0))

I don't know for sure because I always test if I
have valid form data, then assign to a variable(s),
then recreate SQL using variable(s),
then assign new SQL to stored query,
then use that stored query.

==============================
Are you running a report, then saving the report
to an Excel file? You can use SQL to create
a new Excel "table"

{from Jamie}


To create a new Excel table:

SELECT
MyCol1 AS ColA, MyCol2 AS ColB
INTO
[Excel 8.0;database=C:\Tempo\db.xls;].MyNewTable
FROM MyTable



Or create new *** in existing xls:

SELECT
KeyCol AS MyKeyColumn,ValueCol AS MyDataColumn
INTO
[Excel 8.0;database=C:\MyWorkbook.xls].MyNew***
FROM MyTable

would add a new work*** "MyNew***" to existing
"C:\MyWorkbook.xls" (if it not open).




.

Quantcast