Month to Date

[spcepickle]

Hello - I have a database which keeps track of the projects we do. As we complete the projects we change the status to complete and list a completion date. I now need to create a query that will show me all the projects complete in the current month. I tried using >=DateAdd("m",-1,Date()) but it gives me all the projects completed within the last 30 days. So if it is Feb 11 I only want to see projects completed in Feb not projects completed Jan 28.

Thanks!
Amelia

 

[RuralGuy]

How about:
WHERE Month([YourDateField]) = Month(Date())

 

[raskew]

Try this as the criteria for your date field:

>= date()-day(date())+1

Return will limit records to the current month (assuming, of course, that the date field doesn't include future dates)

 

[m.shakeebahmed]

SELECT t1.date, Month(t1.date) AS Expr1, month(Now()) AS Expr2
FROM t1
WHERE Month(t1.date) = month(Now())

 

[raskew]

Depending on the scope of your table, references to only month will return records based on all years, not just the current month/year. Date is an Access function -- naming a field as Date invites future problems.

Why are you using Now()? Does the problem incorporate time? Lookup the Date() and Now() functions to see the differences.

Bob

 

[RuralGuy]

Depending on the scope of your table, references to only month will return records based on all years, not just the current month. Date is an Access function -- naming a field as Date invites future problems.

Bob

Good point Bob. I had not thought about that. I'm now voting for Bob's solution.

 

[Dennisk]

WHERE Month(t1.date) = month(Now())

this will not work as you have to take into account the year

 

[spcepickle]

Try this as the criteria for your date field:

>= date()-day(date())+1

Return will limit records to the current month.

This worked exactly as I wanted! Thanks so much!

 

[raskew]

Glad it worked for you.

Best wishes - Bob