Query Parameter Syntax

  • Thread starter Thread starter croy
  • Start date Start date
C

croy

When I enter,

"Between [Forms]![frmStartExpansion]![txtStart] And
[Forms]![frmStartExpansion]![txtEnd]"

on the criteria line in a certain query (qryA), it runs
fine.

But later on, when I base a crosstab query on qryA, I get an
error message stating that the jet engine doesn't recognize,
'[Forms]![frmStartExpansion]![txtStart]' as a valid field
name or expression.

The built-in Help points me toward specifically stating the
parameter and its data-type.

But when I put,
"Between [Forms]![frmStartExpansion]![txtStart] And
[Forms]![frmStartExpansion]![txtEnd]"
as a parameter, and then try to run the query, an error
comes up stating, "Invalid bracketing of name 'Between
[Forms]![frmStartExpansion]![txtStart] And
[Forms]![frmStartExpansion]![txtEnd]'."

Is there a way to do this that will work?
 
Croy,

When you use a reference to a control on a form, you will frequently need to
define the data type of the value in that control. To do so:

1. Open qryA in design mode
2. Right click in the gray area above the query grid (and not on top of one
of the tables). Then select the Parameters option from the popup menu.
3. Enter your parameters in Parameter column, and the Data Type (date/time)
in the Data Type column. Then hit OK.

Your final query will look something like:

PARAMETERS [Forms]![frmStartExpansion]![txtStart] DateTime,
[Forms]![frmStartExpansion]![txtEnd] DateTime;
SELECT * FROM yourTable
WHERE [SomeField] Between [Forms]![frmStartExpansion]![txtStart]
And [Forms]![frmStartExpansion]![txtEnd]

You may have to put these same parameter statements in the secondary queries.

In many instances, I prefer to create a function which will hold these
values, and can be used in your querys. In order to do this, I use the
AfterUpdate event of each of the date fields to update a function which
contains a static variable (which retains its value between function calls).
Then, I just reference the function in my queries.

Public Sub fnStart(Optional SomeValue as Variant = NULL) as Variant

Static dtStart as Variant

fnStart = NULL
if isnull(SomeValue) = false then
if isdate(SomeValue) = false then
dtStart = NULL
Else
dtStart = SomeValue
endif
fnStart = dtStart

End Function

So, the AfterUpdate event of txtStart might look like:

Private Sub txtStart_AfterUpdate

Call fnStart(me.txtStart)

Endif

And then your query might look like:

SELECT * FROM your Table
WHERE [DateField] BETWEEN fnStart() and fnEnd()
 
Croy,

When you use a reference to a control on a form, you will frequently need to
define the data type of the value in that control. To do so:

1. Open qryA in design mode
2. Right click in the gray area above the query grid (and not on top of one
of the tables). Then select the Parameters option from the popup menu.
3. Enter your parameters in Parameter column, and the Data Type (date/time)
in the Data Type column. Then hit OK.

Your final query will look something like:

PARAMETERS [Forms]![frmStartExpansion]![txtStart] DateTime,
[Forms]![frmStartExpansion]![txtEnd] DateTime;
SELECT * FROM yourTable
WHERE [SomeField] Between [Forms]![frmStartExpansion]![txtStart]
And [Forms]![frmStartExpansion]![txtEnd]

You may have to put these same parameter statements in the secondary queries.

In many instances, I prefer to create a function which will hold these
values, and can be used in your querys. In order to do this, I use the
AfterUpdate event of each of the date fields to update a function which
contains a static variable (which retains its value between function calls).
Then, I just reference the function in my queries.

Public Sub fnStart(Optional SomeValue as Variant = NULL) as Variant

Static dtStart as Variant

fnStart = NULL
if isnull(SomeValue) = false then
if isdate(SomeValue) = false then
dtStart = NULL
Else
dtStart = SomeValue
endif
fnStart = dtStart

End Function

So, the AfterUpdate event of txtStart might look like:

Private Sub txtStart_AfterUpdate

Call fnStart(me.txtStart)

Endif

And then your query might look like:

SELECT * FROM your Table
WHERE [DateField] BETWEEN fnStart() and fnEnd()
Click to expand...

Very good stuff! Thanks Dale.
 
Back
Top