Select records from previous Month?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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?
 
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.
 
Matt said:
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?
Click to expand...

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)
 
Back
Top