Queries

  • Thread starter Thread starter TheCaptain
  • Start date Start date
T

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?
 
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.
 
Right, so any ideas? I also should mention that the months are listed in a
combo box.

Douglas J. Steele said:
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!)


TheCaptain said:
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?
Click to expand...
Click to expand...
 
It depends on what's in the table you're trying to search against.


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


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

Douglas J. Steele said:
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!)


TheCaptain said:
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?
Click to expand...
Click to expand...
Click to expand...
 
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
 
Back
Top