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

M

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
 
M

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
 
J

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
Click to expand...

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]))
Click to expand...


John W. Vinson [MVP]
 
Top