Parameter Query

[Wayne Morgan]

Nice website, thanks for the link.

Ok, I'm just going to take a guess here.

While you can refer to a control on a form for the parameter, the "value" of
the control will go in as the limiter. The query won't act on the text in
the control if that text is an expression. To do that, you would need to
change the .SQL property of the control to concatenate in the text.

Example:
If the control's value is
Between 'A' And 'C'
and you placed this under a field called [Field1], this wouldn't filter
field1 for records between A and C. Instead, the resulting SQL would look
like:

WHERE [Field1]=[Forms]![MyForm]![MyControl]
which would be equivalent to:
WHERE [Field1]="Between 'A' And 'C'"

In other words, it would be trying to find the string listed as the value of
Field1, not what the string represents.

To work around this, you need to concatenate together the SQL in the code
behind the form then apply that to the .SQL property of the query.

Example:
CurrentDb.QueryDefs("qryMyQuery").SQL = strBuiltSQL

--
Wayne Morgan
MS Access MVP

I followed a suggestion from a post made by an MVP which referenced Michael
Green's article "Customizing Access Parameter Queries" ...
http://www.fontstuff.com/access/index.htm.

My unbound form/dialog has a combo box for the director which points to a
query matching "many" depts....to each director. This functions fine.

The second combo box points to a single column table with three rows, each
row holding an expression as referenced earlier. These expressions, if
pasted into the Criteria: for [Current TB Test Date], individually result
in
a correctly functioning query. When the Criteria: is changed through the
"build" function pointing to my frm...combo box...the query results in an
empty report. Hope this is enough info, details are below. I am baffled.

I also can't express enough appreciation, each struggle with this is a
great
learning experience. Thanks

Unbound form: frmReportMenu: Combo Box Properties

Name: cboReports
Control Source: empty
Format: empty
Row Source Type: Table/Query
Row Source: SELECT [tblReports].[Exp] FROM [tblReports];
Column Count: 1
Column Head: No
Bound Column: 1

OK Button

Private Sub cmdCancel_Click()
DoCmd.Close acForm, "frmReportMenu"
End Sub

Private Sub cmdOK_Click()
DoCmd.OpenReport "1_arptquerytest", acViewPreview, acEdit
DoCmd.Close acForm, "frmReportMenu"
End Sub

tblReports: Single column table

Field Name: Exp
Data Type: Text
Field Size: 255
Default Display Control was Text Box, I also tried Combo Box which didn't
work so it is now Text Box

1_arptQueryTest
Record Source: qryBiannualTests

qryBiannualTests SQL

PARAMETERS [Forms]![frmReportMenu]![cboReports] DateTime;
SELECT [All Employees].Last, [All Employees].First, [Child Query].[Current
TB Test Date], qryDirectorAssignments.[Acct #]
FROM tblReports, (([All Employees] INNER JOIN [Child Query] ON [All
Employees].ID = [Child Query].ParentTable_ID) INNER JOIN
qryDirectorAssignments ON [Child Query].Dept =
qryDirectorAssignments.[Acct
#]) INNER JOIN tblDirectorsList ON qryDirectorAssignments.Name =
tblDirectorsList.Name
WHERE ((([Child Query].[Current TB Test
Date])=[Forms]![frmReportMenu]![cboReports]) AND
((tblDirectorsList.Dir_ID)=[Forms]![frmReportMenu]![cboDirectors]) AND
(([Child Query].Active)=Yes));

 

[Ken Snell [MVP]]

If it's text, then the value being passed to the query is a text string that
will be exactly what you're seeing: likely, a date of "mm/dd/yyyy" or such
type of format.

Using date/time as the data type in the query then likely isn't making the
proper connection. The CDate approach that I'd provided earlier probably is
the way you'll need to go to try to get this working, as you need to convert
the mm/dd/yyyy text string to a double-precision date/time value that the
query needs to use for the criterion.

--

Ken Snell
<MS ACCESS MVP>

Tried this this morning and it wouldn't accept the format...data type of
the
expression in the table is TEXT so I am guessing that's the reason.

Just a thought, but what is the format of the combo box control (design
view
of the form)? If it's not ShortDate, perhaps the PARAMETERS clause isn't
making the correct conversion? Set it to ShortDate and try it.

--

Ken Snell
<MS ACCESS MVP>

I changed the expression to reflect your suggestion and stll get an
empty
report, however if I remove the query and reference to the combo box,
and
insert the new expression into the criteria: the query runs...still. I
finally got all the expressions to work in the Query but now they just
won't
run from the combo box.

I have been struggling with dates for a number of weeks. The work my
database records is all based on the relationship of a date (recorded
as
mm/dd/yyyy) in each record with "Today's Date", whenever today is. In
November and December I built reports which all worked fine but they
returned
nothing on January 2, 2004 (ie. Some functions do not recognize months
less
than 1). You have seen one of my expressions, maybe I should just redo
them
all so they work properly. What I use are as follows:

1. All dates in the month 6 months ago
2. All dates in the month 12 months ago
3. All dates older than 6 months ago this month
4. All dates older than 12 months ago this month

This way I can get one report showing every record due in the current
month.

By chance should this be:
WHERE ...Other Filters (if any)... And
([Current TB Test Date] Between
DateSerial(Year(Date()),Month(Date())-6,1)
And DateSerial(Year(Date()),Month(Date())-5,0)) OR Month([Current TB
Test
Date]) & Year([Current TB Test Date])=Month(Date()) & Year(Date())-1

I assume you're wanting to subtract one year from the current date.

When the expression runs it pulls records with dates from July '04 and
Jan.
'04
I also get a report which pulls Aug '04 and Feb. '04 but then next
month
will pull Sept and march

Thanks again
--
Wayne Morgan
MS Access MVP


The following is the new SQL to my Query:

PARAMETERS [Forms]![frmReportMenu]![cboReports] DateTime;
SELECT [All Employees].Last, [All Employees].First, [Child
Query].[Current
TB Test Date], qryDirectorAssignments.[Acct #]
FROM tblReports, (([All Employees] INNER JOIN [Child Query] ON [All
Employees].ID = [Child Query].ParentTable_ID) INNER JOIN
qryDirectorAssignments ON [Child Query].Dept =
qryDirectorAssignments.[Acct
#]) INNER JOIN tblDirectorsList ON qryDirectorAssignments.Name =
tblDirectorsList.Name
WHERE
(((tblDirectorsList.Dir_ID)=[Forms]![frmReportMenu]![cboDirectors])
AND (([Child Query].Active)=Yes) AND (([Child Query].[Current TB
Test
Date])=[Forms]![frmReportMenu]![cboReports]));

One of my expressions (from a table that cboReports draws from)
follows:

(Between DateSerial(Year(Date()),Month(Date())-6,1) And
DateSerial(Year(Date()),Month(Date())-5,0)) OR Month([Current TB
Test
Date])
& Year([Current TB Test Date])=Month(Date()) & Year(Date())-"1"

This and the other expressions in the table all run in the query
returning
correct results when pasted into the Criteria: under (([Child
Query].[Current
TB Test Date]).

The following is the code from the OK command button in my dialog
box:

Option Compare Database

Private Sub cmdCancel_Click()
DoCmd.Close acForm, "frmReportMenu"
End Sub

Private Sub cmdOK_Click()
DoCmd.OpenReport "1_arptquerytest", acViewPreview, acEdit
DoCmd.Close acForm, "frmReportMenu"
End Sub

I applied the "Parameter" suggestion and get no errors now.but I
also
get
an
empty report. If I remove the parameter having to do with
"cboReports"
(which refers to the above expression) the query runs fine from the
dialog
when choosing from "cboDirectors", and I get all records belonging
to
the
"Director_ID".

Any ideas ? I'll appreciate any help. Thank You.

 

[Guest]

I understand what your saying Ken....I went back to that and again I am
getting the run time error I posted yesterday.....when I place the new
expression (with the Format(Cdate) the way you suggestedn in the "Parameter"
box and set type to date/time....I get a "bracketing error" before the
runtime error. Wayne, your idea is tooo far above my head.

As is often suggested here...there is "ALWAYS" a way. My thought is to
create a query for each date expression I need, and then....by placing a
command button on my dialog for each query I can select the director for whom
I want the report and then click the command button pointing to the correct
query.

This will take me back to the "other" question I have difficulty with....a
correct expression to retreive the dates relating to the records I want.


All dates in the month 6 months ago OR All dates in the month 12 months ago
(Due This Month)

All dates in the month 5 months ago OR All dates in the month 11 months ago
(Due Next Month)

All dates older than 6 months ago this month OR All dates older than 12
months ago this month
(Noncompliant)

Man...I feel like I am sooo close (maybe it's a mirage)

Looking further for the source of the problem.....the [Current TB Test Date]
is arrived at in a Query as follows:

DMax("[ReadOn]","Child","[ParentTable_ID] =" & [ID]) AS [Current TB Test Date]

Could this be the problem?