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

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Mikael Lindqvist - 18 Dec 2007 09:55 GMT

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

Reply to this Message

Mikael Lindqvist - 18 Dec 2007 10:39 GMT

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

Reply to this Message

John W. Vinson - 18 Dec 2007 18:32 GMT

>By thinking about for another minute I found the (simple) answer.
>
[quoted text clipped - 6 lines]
>
>//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]

Reply to this Message