Queries

[TheCaptain]

Hello,

I am running a simple query and need it search through a range of dates and
years. So for example, something like this:

From: January From: 2005
To: August To: 2007

The search criteria is entered into a form and then you push a"Search"
button and the results are displayed on a form. The SQL coding I am using is
as follows:

Where ([month] Between Forms!CapSearch.mfrom And Forms!CapSearch.mto Or
[month] Between Forms!CapSearch.mfrom And Forms!CapSearch.mto IS NULL) AND
([year] Between Forms!CapSearch.yfrom And Forms!CapSearch.yto Or [year]
Between Forms!CapSearch.yfrom And Forms!CapSearch.yto IS NULL);

mfrom= Starting Month
mto=Finishing Month
yfrom= Starting Year
yto= Finishing Year
CapSearch= my form

I don't know why, but it works fine when I only enter the years. Perhaps I
have to specify that the Months are text?

 

[Douglas J. Steele]

Having Text months is definitely going to be an issue. Note, for instance,
that August is not between June and October, since text is handled
alphabetically.

 

[TheCaptain]

Right, so any ideas? I also should mention that the months are listed in a
combo box.

Having Text months is definitely going to be an issue. Note, for instance,
that August is not between June and October, since text is handled
alphabetically.


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hello,

I am running a simple query and need it search through a range of dates
and
years. So for example, something like this:

From: January From: 2005
To: August To: 2007

The search criteria is entered into a form and then you push a"Search"
button and the results are displayed on a form. The SQL coding I am using
is
as follows:

Where ([month] Between Forms!CapSearch.mfrom And Forms!CapSearch.mto Or
[month] Between Forms!CapSearch.mfrom And Forms!CapSearch.mto IS NULL) AND
([year] Between Forms!CapSearch.yfrom And Forms!CapSearch.yto Or [year]
Between Forms!CapSearch.yfrom And Forms!CapSearch.yto IS NULL);

mfrom= Starting Month
mto=Finishing Month
yfrom= Starting Year
yto= Finishing Year
CapSearch= my form

I don't know why, but it works fine when I only enter the years. Perhaps
I
have to specify that the Months are text?

 

[Douglas J. Steele]

It depends on what's in the table you're trying to search against.


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Right, so any ideas? I also should mention that the months are listed in
a
combo box.

Having Text months is definitely going to be an issue. Note, for
instance,
that August is not between June and October, since text is handled
alphabetically.


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hello,

I am running a simple query and need it search through a range of dates
and
years. So for example, something like this:

From: January From: 2005
To: August To: 2007

The search criteria is entered into a form and then you push a"Search"
button and the results are displayed on a form. The SQL coding I am
using
is
as follows:

Where ([month] Between Forms!CapSearch.mfrom And Forms!CapSearch.mto Or
[month] Between Forms!CapSearch.mfrom And Forms!CapSearch.mto IS NULL)
AND
([year] Between Forms!CapSearch.yfrom And Forms!CapSearch.yto Or [year]
Between Forms!CapSearch.yfrom And Forms!CapSearch.yto IS NULL);

mfrom= Starting Month
mto=Finishing Month
yfrom= Starting Year
yto= Finishing Year
CapSearch= my form

I don't know why, but it works fine when I only enter the years.
Perhaps
I
have to specify that the Months are text?

 

[Ken Sheridan]

If you convert the year/month values to real dates then you can compare the
start and end dates of the range. The year value are fine as they are
already numbers. A month name can be converted to a number by concatenating
an arbitrary year followed by a slash character onto it and then converting
the resultant string to a date (the first of the month is automatically
assumed but this is irrelevant as you are only dealing with years and months).

So the start of the range is:

CDate(Forms!CapSearch.yfrom & "/" & Month("2000/" & Forms!CapSearch.mfrom))

The end of the range is:

CDate(Forms!CapSearch.yto & "/" & Month("2000/" & Forms!CapSearch.mto))

The values in the [month] and [year] columns are converted in a similar way:

CDate([year] & "/" & Month("2000/" & [month]))

I assume that you are testing for NULL to make the parameters optional, in
which case you should be testing for OR <parameter> IS NULL in fact rather
than AND <parameter> IS NULL. However, you can do so by using the Nz
function in the expressions. So the WHERE clause would go like this:

WHERE CDate([year] & "/" & Month("2000/" & [month])) BETWEEN
CDate(Nz(Forms!CapSearch.yfrom,1900) & "/" & Month("2000/" &
Nz(Forms!CapSearch.mfrom,1))) AND CDate(Nz(Forms!CapSearch.yto,2100) & "/" &
Month("2000/" & Nz(Forms!CapSearch.mto,12)))

This makes the default date range January 1900 to December 2100, which I
assume all records will fall within.

Ken Sheridan
Stafford, England