# 13 Month Range?

1. Competent Performer
Windows 7 32bit Access 2003
Join Date
Apr 2014
Posts
410

## 13 Month Range?

Even though my range is for 13 months with the WHERE:
Code:
`PaymentDate = Between IIf(Month(Date())<=5,DateSerial(Year(Date())-1,5,1),DateSerial(Year(Date()),5,1)) And IIf(Month(Date())<=5,DateSerial(Year(Date()),5,30),DateSerial(Year(Date())+1,5,30)))`
When I look at the details I'm only seeing 6/1/2013 to current day.

I know there is data for May 2013.

What is wrong with my criteria?

2. I use two techniques to help me count periods.

A table dedicated to a predetermined range of dates. This table has columns of integer type dedicate to numbers that represent, weekday, month, and year. A column for each.

The second technique is to employ alias' in queries. An alias for weekday, month, and year. All of the alias' are integers.

3. Competent Performer
Windows 7 32bit Access 2003
Join Date
Apr 2014
Posts
410
Ok, but why would this not result in May data?

Code:
`Between IIf(Month(Date())<=5,DateSerial(Year(Date())-1,5,1),DateSerial(Year(Date()),5,1)) And IIf(Month(Date())<=5,DateSerial(Year(Date()),5,30),DateSerial(Year(Date())+1,5,30)))`

4. Competent Performer
Windows 7 64bit Access 2010 64bit
Join Date
Oct 2013
Location
US
Posts
415
The WHERE translate to "5/1/2013" to "5/30/2014".

Between IIf(Month(Date())<=5,DateSerial(Year(Date())-1,5,1),DateSerial(Year(Date()),5,1)) And IIf(Month(Date())<=5,DateSerial(Year(Date()),5,30) ,DateSerial(Year(Date())+1,5,30))

So, if there is data with "PaymentDate" (Date/Time type), it should pick up from "5/1/2013" to "5/30/2014".

Can you upload the small sample of your database for close look?

5. Competent Performer
Windows 7 32bit Access 2003
Join Date
Apr 2014
Posts
410
Exactly!

But for some reason it's showing only from 6/1/13 through May 2014 when there is data for May 2013.

Will do.

thank you

6. Competent Performer
Windows 7 32bit Access 2003
Join Date
Apr 2014
Posts
410
Here it is.

There is one record from May 2013.
It should show BUT it does not.

Mbrshp.zip

7. Competent Performer
Windows 7 64bit Access 2010 64bit
Join Date
Oct 2013
Location
US
Posts
415
It looks like the "EndYear >=Year(Date())" remove all your entry prior to 7/3/2013.

8. Competent Performer
Windows 7 32bit Access 2003
Join Date
Apr 2014
Posts
410
Shoot!

DOH!

Well, yes that makes sense since the May 2013 record also has an EndDate of Sep 2013.

Sorry for the trouble!

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•
Other Forums: Microsoft Office Forums