bad query?

[MJ]

Hi,

I have a prompt form that I get a startmonth, startyear and endmonth,
endyear so that I can look through my table and pick out any records
that were created during that time (stored in fields monthdataentered
and yeardataentered).

Here is my query:

SELECT Count(*) AS numclients, [city].[name]
FROM consult INNER JOIN city ON [consult].[city]=[city].[id]
WHERE
([consult].[monthdataentered]>[Forms]![prompt]![startmonth] Or
[consult].[monthdataentered]=[Forms]![prompt]![startmonth])
And
([consult].[monthdataentered]<[Forms]![prompt]![endmonth] Or
[consult].[monthdataentered]=[Forms]![prompt]![endmonth])
And
([consult].[yeardataentered]>[Forms]![prompt]![startyear] Or
[consult].[yeardataentered]=[Forms]![prompt]![startyear])
And
([consult].[yeardataentered]<[Forms]![prompt]![endyear] Or
[consult].[yeardataentered]=[Forms]![prompt]![endyear])

GROUP BY [city].[name];

It isn't picking up all the records. I don't understand it. If I put
in my prompt:
startmonth=2
startyear=2
endmonth=2
endyear=4

It will pick out of the table this record:
monthdataentered = 2
yeardataentered = 2

but not this record:
monthdataentered = 6
yeardataentered = 2

Why not?! Any ideas? Thanks!
MJ

 

[Tom Ellison]

Dear MJ:

There is a difficulty with your logic. I have re-written what you
started with to make it clearer to me:

SELECT Count(*) AS numclients, [city].[name]
FROM consult
INNER JOIN city ON [consult].[city]=[city].[id]
WHERE [consult].[monthdataentered] >= [Forms]![prompt]![startmonth]
AND [consult].[monthdataentered] <= [Forms]![prompt]![endmonth]
AND [consult].[yeardataentered] >= [Forms]![prompt]![startyear]
AND [consult].[yeardataentered] <= [Forms]![prompt]![endyear]
GROUP BY [city].[name];

The comparison operator >= means "greater than or equal to" and thus
exactly replaces your two comparisons with the OR conjunction.

Now, if you want to find all the rows between December 2003 and
January 2005, your logic would require the month number to be >= 12
AND <= 1. There are no month numbers that could ever satisfy this.

Or, in your example, you have a startmonth of 2 and an endmonth of 2.
What month numbers lie between 2 and 2? Only 2!

The trick is, you should only compare the month number when the year
number is either the startyear or the endyear. For years between
(exclusively) these two, you don't care what the month is. For years
outside this, you also don't care what the month is.

In an outline, the logic would be:

WHERE ([consult].[yeardataentered] > [startyear]
AND [consult].[yeardataentered] < [endyear])
OR ([consult].[yeardataentered] = [startyear]
AND [consult].[monthdataentered] >= [startmonth])
OR ([consult].[yeardataentered] = [endyear]
AND [consult].[monthdataentered] <= [endmonth])

Notice that this assumes the starting month/year is before the ending
month/year. If the criteria are entered incorrectly so the ending
month/year is actually prior to the starting month/year, but both
within the same year, then this will malfunction. If that is
important, then put the above inside parens and add:

AND ([startyear] < [endyear] OR ([startyear] = [endyear] AND
[startmonth] <= [endmonth]))

This will force the result to be empty when dates are not entered in a
chronological order.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

Hi,

I have a prompt form that I get a startmonth, startyear and endmonth,
endyear so that I can look through my table and pick out any records
that were created during that time (stored in fields monthdataentered
and yeardataentered).

Here is my query:

SELECT Count(*) AS numclients, [city].[name]
FROM consult INNER JOIN city ON [consult].[city]=[city].[id]
WHERE
([consult].[monthdataentered]>[Forms]![prompt]![startmonth] Or
[consult].[monthdataentered]=[Forms]![prompt]![startmonth])
And
([consult].[monthdataentered]<[Forms]![prompt]![endmonth] Or
[consult].[monthdataentered]=[Forms]![prompt]![endmonth])
And
([consult].[yeardataentered]>[Forms]![prompt]![startyear] Or
[consult].[yeardataentered]=[Forms]![prompt]![startyear])
And
([consult].[yeardataentered]<[Forms]![prompt]![endyear] Or
[consult].[yeardataentered]=[Forms]![prompt]![endyear])

GROUP BY [city].[name];

It isn't picking up all the records. I don't understand it. If I put
in my prompt:
startmonth=2
startyear=2
endmonth=2
endyear=4

It will pick out of the table this record:
monthdataentered = 2
yeardataentered = 2

but not this record:
monthdataentered = 6
yeardataentered = 2

Why not?! Any ideas? Thanks!
MJ

 

[Van T. Dinh]

You are assuming that JET (the database engine used by Access) knows that
you want to select Records in a *DateTime* interval defined by (StartMonth /
StartYear) and (EndMonth / EndYear).

It does not know that you meant a datetime interval. With the criteria you
set, JET simply selects Records according to the criteria you set on 2
*numeric* values without regards to the datetime you meant.

I am not sure how you store the month and year in the Table. I guess month
is store as a value from 1 to 12. However, when you use 2 and for as the
examples for year, do you mean you store 2 for year 2002 and 4 for 2004 or
they are simply the number of years from a *reference* year (which is not
2000).

Perhaps, you can clarify and someone may respond with a suggestion.

 

[MJ]

My month is stored in a month table
1 blank
2 january
3 feb
4 march
and so on...

My year is stored in a year table
1 blank
2 2002
3 2003
4 2004

I originally had the >= logic but couldn't find that syntax in
access... so I thought maybe you couldn't do that. Thanks so much for
your suggestions! Very much appreciated!!!

 

[Van T. Dinh]

What are the blanks doing there?

Did you work it out from Tom's explanation?

 

[Tom Ellison]

My explanation did not contemplate anything like a blank or unknown
month or year. To adapt to that situation there would need to be a
definition of when a blank month or year is to be considered within a
range or not.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts