Date and Time Criteria from a Control on a Form [Access 2003 VBA Language Reference]

  • Article

If you want to change the criteria argument for an operation based on a user's decision, you can specify that the criteria comes from a control on a form. For example, you could specify that the criteria argument comes from a list box containing order dates from an Orders table.

To specify date and time criteria that comes from a control on a form, you include in the criteria argument an expression that references the control on the form. This expression should be separate from the string expression, so that Microsoft Access will evaluate the control expression first and concatenate it with the rest of the string expression before performing the appropriate operation.

In addition to enclosing the entire string expression in double quotation marks ("), you must also ensure that the date or time criteria within the string expression is enclosed in number signs (#). The number signs must be included in the strings flanking the expression that references the control on the form.

Note  The number signs indicate to Microsoft Access that the criteria argument contains a date or time within a string.

The following examples set a form's Filter or ServerFilter property based on criteria that comes from a control named HireDate that's on the form. Note the placement of the number signs.

Forms!Employees.Filter = "[HireDate] >= #" _
 &     Forms!Employees!HireDate & "#"
Forms!Employees.FilterOn = True

– or –

Forms!Employees.ServerFilter = "[HireDate] >= #" _
 &     Forms!Employees!HireDate & "#"
Forms!Employees.FilterOn = True

If the current value of the HireDate control is 5-1-92, the Filter or ServerFilter property will have the following criteria argument:

"[HireDate] >= #5-1-92#"

Tip

criteria

You can also include a variable representing a date or time in the criteria argument. The variable should be separate from the string expression, so that Microsoft Access will evaluate the variable first and then concatenate it with the rest of the string expression. The date or time criteria must be enclosed in number signs.

The following example shows how to construct a criteria argument that includes a variable representing a date or time:

Dim datHireDate As Date
datHireDate = #5-1-92#
Forms!Employees.Filter = "[HireDate] >= #" _
 &     datHireDate & "#"

See Also | Date and Time Criteria Expressions | Domain Aggregate Functions | Multiple Fields in Criteria Expressions | Numeric Criteria Expressions | Numeric Criteria from a Control on a Form | Restrict Data to a Subset of Records | Textual Criteria Expressions | Textual Criteria from a Control on a Form