Select records from previous Month?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Matt D Francis - 19 May 2005 11:09 GMT

Hi.

I have a table with a date field in it, and I want to be able to pull out
all the records from the previous month, irrespective of when I run the
Query. So any time I run the query in May, I'll get all April records etc. It
seems simple!
I tried adding the SQL:

AND ((Format([EPIS_START],"M"))=Format(Date(),"M")-1))

which worked , but when i added:

AND ((Format([EPIS_START],"YY"))=Format(Date(),"YY"))

I get nothing even though there's definitely data there for this.

What am I doing wrong? Is there a neater way to do this?

Reply to this Message

Matt D Francis - 19 May 2005 11:17 GMT

Sorry! Got it to work, just a typo I think, but I'd still like to know if
there is a more efficent way of doing this, particulalry by using the
Criteria fields in the Design Grid if possible. I have to hand this over to
someone who doesn't understand SQL.

> Hi.
>
[quoted text clipped - 13 lines]
>
> What am I doing wrong? Is there a neater way to do this?

Reply to this Message

Rick Brandt - 19 May 2005 13:04 GMT

> Hi.
>
[quoted text clipped - 13 lines]
>
> What am I doing wrong? Is there a neater way to do this?

Your logic will not work in January and will not be efficient because no index
can be utilized when you apply criteria to an expression rather than a field.
Use...

WHERE [EPIS_START] BETWEEN DateSerial(Year(Date()), Month(Date())-1, 1) AND
DateSerial(Year(Date()), Month(Date()), 0)

Signature

I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com

Reply to this Message