Using form to enter "query criteria" (between values)

[Mikael Lindqvist]

Good morning everyone,

I want to create a form with two boxes, where first box is lower limit of
the value and the second upper limit. This values should be used in my query
as filter criteria.

Like this (query filter criteria): > "textbox1" AND < "textbox2"

Does anyone know how I can build this in a form (i.e. connect my textboxes
and query criteria)?

Kindly,
Mikael
Sweden

 

[Mikael Lindqvist]

By thinking about for another minute I found the (simple) answer.

In the query criteria I put the following expression:

BETWEEN [forms].[nameofform].[NameofDatefield1] AND
[forms].[nameofform].[NameofDatefield2]

Where 1 and 2 are the names of the textboxes in my form.

//Mikael

 

[John W. Vinson]

By thinking about for another minute I found the (simple) answer.

In the query criteria I put the following expression:

BETWEEN [forms].[nameofform].[NameofDatefield1] AND
[forms].[nameofform].[NameofDatefield2]

Where 1 and 2 are the names of the textboxes in my form.

//Mikael

That will work... if the table field contains only pure dates without a time
component. Otherwise it will miss records on the last day of the range.

It may also cause problems if users enter variant date formats (day-month-year
in a system defaulting to month-day-year for example).

I'd suggest doing two things: include

PARAMETERS [forms].[nameofform].[NameofDatefield1] DateTime,
[forms].[nameofform].[NameofDatefield2] DateTime;

prior to the SQL statement, and use a criterion

= CDate([forms].[nameofform].[NameofDatefield1]) AND < DateAdd("d", 1, CDate([forms].[nameofform].[NameofDatefield2]))

John W. Vinson [MVP]