Finding this month's records

[groleau]

HAVING UNBILLED.age_dt
       Between DateSerial(Year(Date()), Month(Date()), 1)
       And     DateSerial(Year(Date()), Month(Date()) + 1, 0)

Why does this select records throughout this month and last month?

--
Wes Groleau

http://www.Parkview.com/

 

[groleau]

First, always consider what you are really trying to accomplish---to get all accounts this month, you can ask for everything between the first day of this month and the first day of next month minus one.....

OR you can just say WHERE Month(account_date) = Month(Date())


Second, I don't understand it, but

HAVING Month(account_date) = Month(Date())

gets both months and

WHERE Month(account_date) = Month(Date())

just gets the current month.

So, my query now does what I want, but I'd still like to know why the other one (HAVING) didn't.

--
Wes Groleau

http://www.Parkview.com/

 

[lespaul]

because this:

Month(Date()) + 1

means get the month of the date (today) and add one to it. If you add one month to June you get July....

Why not just:

WHERE Month(UNBILLED.age_dt) = Month(Date())

if age_dt is a TRUE date field in the table structure

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for database developers:
The Fundamentals of Relational Database Design
Understanding SQL Joins

 

[groleau]

Yes, that's what I ended up with.

But the query also had a HAVING clause, and with the SAME expression there, it selected two months' data.

???

--
Wes Groleau

http://www.Parkview.com/

 

[dhookom]

The only criteria in the "HAVING..." clause should reference the aggregated value. This would be used like:

HAVING Sum([TotalCost]) >2000

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]