Prev: Query results where gaps exist Min & Max do not work
Next: Query and date selection
From: ZBB on 12 May 2010 14:42
I'm stuck. I can't change the following statement correctly to show only the
current year in addition to the current month. It pulls the prior years!

SELECT DISTINCTROW [FC Customers].* FROM [FC Customers] WHERE
(((Month([Date]))=Month(Now())));

Help!
From: raskew via AccessMonster.com on 12 May 2010 15:22
Hi -

You need to rename your date field since Date is a reserved word in Access.
Your Where statement would look like:

WHERE (((Year([MyDte]))=Year(Now())) AND ((Month([MypDte]))=Month(Now())));

HTH - Bob
ZBB wrote:
>I'm stuck. I can't change the following statement correctly to show only the
>current year in addition to the current month. It pulls the prior years!
>
>SELECT DISTINCTROW [FC Customers].* FROM [FC Customers] WHERE
>(((Month([Date]))=Month(Now())));
>
>Help!

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/201005/1

From: Jerry Whittle on 12 May 2010 15:33
SELECT DISTINCTROW [FC Customers].*
FROM [FC Customers]
WHERE Month([Date])=Month(Now())
AND Year([Date])=Year(Now());
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

"ZBB" wrote:

> I'm stuck. I can't change the following statement correctly to show only the
> current year in addition to the current month. It pulls the prior years!
>
> SELECT DISTINCTROW [FC Customers].* FROM [FC Customers] WHERE
> (((Month([Date]))=Month(Now())));
>
> Help!
From: John Spencer on 12 May 2010 16:01
SELECT DISTINCTROW [FC Customers].* FROM [FC Customers]
WHERE [Date] >= DateSerial(Year(Now()),Month(Now()),1) AND
[Date] < DateSerial(Year(Now()),Month(Now())+1 ,1)

That will be the most efficient way to do this. Especially if you have a
large volume of records.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

ZBB wrote:
> I'm stuck. I can't change the following statement correctly to show only the
> current year in addition to the current month. It pulls the prior years!
>
> SELECT DISTINCTROW [FC Customers].* FROM [FC Customers] WHERE
> (((Month([Date]))=Month(Now())));
>
> Help!
From: John W. Vinson on 12 May 2010 16:21
On Wed, 12 May 2010 11:42:01 -0700, ZBB <ZBB(a)discussions.microsoft.com> wrote:

>I'm stuck. I can't change the following statement correctly to show only the
>current year in addition to the current month. It pulls the prior years!
>
>SELECT DISTINCTROW [FC Customers].* FROM [FC Customers] WHERE
>(((Month([Date]))=Month(Now())));
>
>Help!

Rather than a (very inefficient) search comparing one calculated field to
another calculated field, I'd suggest searching the (misnamed, it's a reserved
word!!!) [Date] field directly:

SELECT DISTINCTROW [FC Customers].* FROM [FC Customers] WHERE [Date] >=
DateSerial(Year(Date()), Month(Date()), 1) AND [Date] <
DateSerial(Year(Date()), Month(Date()) + 1, 1)

--

John W. Vinson [MVP]
 | 
Pages: 1
Prev: Query results where gaps exist Min & Max do not work
Next: Query and date selection