Finding Specific Dates

[lumpy2497]

Hello All!

My co-workers and I are trying to run a query to find certain employees that accrue vacation time based on their accrual date.

We currently are using DatePart("m",[accrual date]) with a user prompt to get the month. But we would like the DatePart("d",[accrual date]) to only give us certain days (ie. between the 5th and the 18th). For instance, we would like to pull those who's accrual date is between 4/5 and 4/18, no matter what the year is.

Thanks to all in advance!

Leeann...

 

[raskew]

Hi -

Try this, changing table/query and field names as appropriate:

SELECT Query4.ExpDte, DatePart("m",[ExpDte]) AS Expr2, DatePart("d",[ExpDte]) AS Expr3
FROM Query4
WHERE (((DatePart("m",[ExpDte]))=4) AND ((DatePart("d",[ExpDte])) Between 5 And 18));

HTH - Bob

 

[lumpy2497]

Thanks!!! That worked!!

However, when I amend the date to go from "Between 5 and 18" to "Between [user input] and [user input]", it doesn't work.

I want to eventually have it span across months, (ie. 4/19-5/2, etc), but I'll save that one for down the road.

Leeann...

 

[highandwild]

Is [user input] is supposed to be to enable the user to enter a parameter into the query?

If so if you then you have used [user input] twice and it will only ask for one entry and use that for both.

 

[lumpy2497]

The user inputs are 2 different. I usually use [Start Date] and [End Date].

Leeann...

 

[HGMonaro]

maybe just a syntax issue. What I do in circumstances like that is create a query in query builder so that it works, then view the SQL and copy that into my code.

I tried a more basic example of what you're doing (just using the start and end dates) and it looks similar to yours but did work.

my test

SELECT Attendances.Teacher
FROM Attendances
WHERE (((Attendances.Day)=[Day wanted]) AND ((Attendances.Date) Between [start date] And [end date]));