M
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
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