Hi, I am trying to create a list from a  the query that will be ran
frequently, it is supposed to show employees on the list that are have dates
between now and 30 days ago. I think that I am close with the where clause
but no quite. Please will you help me? Thanks, Misty

Ex. for today, 12/4/07,  all dates between 12/4/07 and 11/5/07.

Select Date, Employee
From EmpRec
Where Date >=DateSerial(Year(Date()),Day(Date()),1) And <DateSerial(Year(Date
()),Day(Date())+1,30)

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

WHERE [Date] BETWEEN DateAdd("d", -30, Date) AND Date

You should change the name of the field Date to something else.  Date is an 
Access reserved word and can very likely cause problems.  Notice I wrapped it 
in brackets, but even doing that is not always an assurance it will work 
correctly.
-- 
Dave Hargis, Microsoft Access MVP


"Mitchell_Collen via AccessMonster.com" wrote:

> Hi, I am trying to create a list from a  the query that will be ran
> frequently, it is supposed to show employees on the list that are have dates
> between now and 30 days ago. I think that I am close with the where clause
> but no quite. Please will you help me? Thanks, Misty
> 
> Ex. for today, 12/4/07,  all dates between 12/4/07 and 11/5/07.
> 
> Select Date, Employee
> From EmpRec
> Where Date >=DateSerial(Year(Date()),Day(Date()),1) And <DateSerial(Year(Date
> ()),Day(Date())+1,30)
> 
> -- 
> Message posted via AccessMonster.com
> http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/200712/1
> 
> 

You're trying too hard.  :-)

  Between Date() - 30 and Now()

I used Now() as it will include records up to the minute.

Oh! You're field named "Date" in the EmpRec table could be a problem. Date() 
is a reserved word in Access. Therefore your Date field could be getting 
confused with the Date function.
-- 
Jerry Whittle, Microsoft Access MVP 
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

"Mitchell_Collen via AccessMonster.com" wrote:

> Hi, I am trying to create a list from a  the query that will be ran
> frequently, it is supposed to show employees on the list that are have dates
> between now and 30 days ago. I think that I am close with the where clause
> but no quite. Please will you help me? Thanks, Misty
> 
> Ex. for today, 12/4/07,  all dates between 12/4/07 and 11/5/07.
> 
> Select Date, Employee
> From EmpRec
> Where Date >=DateSerial(Year(Date()),Day(Date()),1) And <DateSerial(Year(Date
> ()),Day(Date())+1,30)
> 
> -- 
> Message posted via AccessMonster.com
> http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/200712/1
> 
> 

Thanks for helping me. I will try it.
 Misty

Klatuu wrote:
>WHERE [Date] BETWEEN DateAdd("d", -30, Date) AND Date
>
>You should change the name of the field Date to something else.  Date is an 
>Access reserved word and can very likely cause problems.  Notice I wrapped it 
>in brackets, but even doing that is not always an assurance it will work 
>correctly.
>> Hi, I am trying to create a list from a  the query that will be ran
>> frequently, it is supposed to show employees on the list that are have dates
>[quoted text clipped - 7 lines]
>> Where Date >=DateSerial(Year(Date()),Day(Date()),1) And <DateSerial(Year(Date
>> ()),Day(Date())+1,30)

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

Thanks for the help! 
Misty

Jerry Whittle wrote:
>You're trying too hard.  :-)
>
>  Between Date() - 30 and Now()
>
>I used Now() as it will include records up to the minute.
>
>Oh! You're field named "Date" in the EmpRec table could be a problem. Date() 
>is a reserved word in Access. Therefore your Date field could be getting 
>confused with the Date function.
>> Hi, I am trying to create a list from a  the query that will be ran
>> frequently, it is supposed to show employees on the list that are have dates
>[quoted text clipped - 7 lines]
>> Where Date >=DateSerial(Year(Date()),Day(Date()),1) And <DateSerial(Year(Date
>> ()),Day(Date())+1,30)

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