query prompt using SQL

[Guest]

I use MS Access occasionally but have coded in QMF. I have a MS passthru
query noted below. I want to require the user to enter the dates. I found
the commands [enter date] but am unsuccessful using this in my sql query.
Are there other commands that will prompt the user to enter the dates? Many
thanks.

SELECT
A.CH_UID,
A.CH_DOC_ID,
A.DOC_SEQ,
A.ACTION_NO,
A.BUSINESS_UNIT,
A.DATE_IDENTIFIED,
A.TITLE,
B.DATA,
A.CORRECTIVE_ACTION,
A.PROC_SUM,
A.PROC_SUM2,
A.REPORTED,
A.FUND_BOARDS,
A.QUARTER_REPORTED,
A.REPORTED_REASON

from
pcms_chdoc_attr_history a,
pcms_chdoc_detail_desc b
where
a.ch_uid = b.ch_uid and
a.ch_doc_id = b.ch_doc_id and
a.product_id = 'LEGAL' and
a.ch_doc_type = 'EXCEPTION' and
a.business_unit = 'FN - Finance' and
(A.DATE_IDENTIFIED > '01-DEC-2006' and
A.DATE_IDENTIFIED < '01-APR-2007') and
a.business_unit = 'FN - Finance' and
a.action_no =
(SELECT MAX(action_no)
FROM pcms_chdoc_attr_history T
WHERE A.CH_UID = T.CH_UID)

ORDER BY
a.date_identified desc

 

[John W. Vinson]

I use MS Access occasionally but have coded in QMF. I have a MS passthru
query noted below. I want to require the user to enter the dates. I found
the commands [enter date] but am unsuccessful using this in my sql query.
Are there other commands that will prompt the user to enter the dates? Many
thanks.

You can't use a parameter with a passthrough query - the SQL server has no way
of ascertaining the value of the parameter.

What you could do is use a Form with a control (textbox typically) to solicit
the parameter, and actually construct the SQL string in code (say behind a
button on the form).

John W. Vinson [MVP]

 

[Guest]

Than you for the the suggestion. I have created text boxes. Can you direct
me as to how to construct the SQL string in the code (any links I can
reference).

Thank you again.

I use MS Access occasionally but have coded in QMF. I have a MS passthru
query noted below. I want to require the user to enter the dates. I found
the commands [enter date] but am unsuccessful using this in my sql query.
Are there other commands that will prompt the user to enter the dates? Many
thanks.

You can't use a parameter with a passthrough query - the SQL server has no way
of ascertaining the value of the parameter.

What you could do is use a Form with a control (textbox typically) to solicit
the parameter, and actually construct the SQL string in code (say behind a
button on the form).

John W. Vinson [MVP]

 

[John W. Vinson]

Than you for the the suggestion. I have created text boxes. Can you direct
me as to how to construct the SQL string in the code (any links I can
reference).

Something like:

Dim strSQL As String
strSQL = "SELECT this, that, theother" _
& " FROM sometable INNER JOIN anothertable" _
& " ON <some join expression>" _
& " WHERE [fieldname] = " & Me!controlname _
& " ORDER BY that, this;"

The details depend, of course, on what you want to do. The exact syntax will
be that used by the database engine, of course (e.g. ' delimiters rather than
" for text, ' rather than # delimiters for dates in SQL/Server).

John W. Vinson [MVP]