Query criteria - only show curreny July - July period

[mike_980]

I am looking for some criteria which will only show the current July-July period. For example if the date was today, 28/10/2013 and we ran the query it would fetch results from 1st July 2013 up until 30th June 2014.



Is there any such criteria

[ItsMe]

I was thinking you would need to add the month to something like "SELECT BETWEEN".

Turns out, adding the month function to your query's criteria should do the trick. You should be able to include your field name within the month function.
Month([DateField])


I found this reference on the internet.
http://www.databasedev.co.uk/retreive_date_query.html

[mike_980]

would this work though say for example this time next year? as if the date was now thr 28th October 2015, it would need to fetch records from July 1st 2015 up until 30th June 2016? I'm a little unsure on how this function works, i've read the article you posted and it seems to make sense but I'm not sure how i would make it think that July 1st is the first date of the year instead of displaying January - January results?

[ItsMe]

Try this, to simplify things. In your query, create another field. This field will be a translation of the date field. It will translate a full date value into an integer that represents the month said date falls into. Something like this, in a new field within your query.....

NewMonthField: Month([NameOfFieldWithDate])

If you run this in your SELECT query, you will see that you now have a number that represents the month within each date field. Granted, this is not the final result you are after. But, it will help to illustrate how you are now capable of querying multiple records using an integer as criteria, i.e. Where NewMonthField = 7

Now you can add a year field to your query too.

NewYearField: Year([NameOfFieldWithDate])

With this you can create your Where clause within your form's Class Module.


something like

Where NewMonthField = 7 AND NewYearField = 12 AND NewYearField = 13

After you work that out, there may be a way to bypass adding the fields to your query and just associate controls to the SQL directly.

[JoeM]

Here is one way to do this dynamically (so once you set it up, you do not need to worry about having to updating it at all).

Create two calculated fields in your query that show the low and high date of the date range based on the current date. Those fields will look like:

LowDate: IIf(Month(Date())>=7,DateSerial(Year(Date()),7,1),DateSerial(Year(Date())-1,7,1))

HighDate: IIf(Month(Date())>=7,DateSerial(Year(Date())+1,6,30),DateSerial(Year(Date()),6,30))

Now, let's say the name of your date field is "MyDateField".

In the query, add this criteria under your new "LowDate" calculated field:

<=[MyDateField]

And add this criteria under the new "HighDate" calculated field:

>=[MyDateField]

That should then return the records you want!

[mike_980]

Thankyou! that's exactly what I was after, works perfectly!

Many thanks!

[JoeM]

Your welcome. Glad to help!