Filter view on current month (1 Viewer)

I want to create a view that shows all items created in the current month. This is the datasource for a related items control.

I'm able to achieve this by using date functions in the where clause, but this doesn't perform very well. My guess is that it is evaluating this formula for every row in the data source. It performs much faster if I hard code a date.

Is there another way of achieving this? I have a variable in my OnStart macro that is set to the first day of the current month, but I don't think there is a way of using this variable in a query's WHERE criteria.

I see that queries can accept parameters, but not sure how I can set this.

possibly but without knowing your sql code, table structure, relationships and indexing it is impossible to say

As CJ said it is better if we have more information., but I am going to guess that you are using the Month function.

Assume that your field containing first day of month is FofMth

Ensure that the date field in the datasource is indexed and then

Between FofMth And Dateserial(Year(FofMth), Month(FofMth)+1,0)

Although this uses Date functions they are used only once in constructing the criteria not for every record, also the index can be used in the search.

Brian

Hi,

First, let me address the other people commenting on the thread for their own information and learning. The person posting the question is using an Access 2013 web app which is why they posted their question in the Access web forum. Access 2013 web apps are much different than Access desktop applications. For example, when using this environment, relationships are defined through lookup fields, you cannot use any VBA code, you cannot get directly to the SQL of any queries you create (you can only use the design window), and the expressions you can use are different since they are SQL expressions which go against the SQL tables that drive the web app. DateSerial, for example, is not an available expression.

So that is why the poster could not provide some of that information. Also, the new Related Items Control is unique to Access 2013 web apps.

For the poster of the question, have you considered just creating a saved query that will return all the records in the current month? It sounds like you may be using a RequeryRecords macro action at the view level's On Load event to restrict the records to the current month. Is that correct?

To define a query in an Access 2013 web app that will return just records with the current month, use the Criteria line for the date column in question like this:

Year([SomeDate])=Year(Now()) And Month([SomeDate])=Month(Now())

In my example, I'm using the Criteria line for my SomeDate field in the table. When I save the query and run it, I see only records returned where SomeDate falls within the current month. If you switch back to Design View, you'll notice that Access created two hidden output columns to show this.

Will this setup work for you?

--------------------
Jeff Conrad - Access Junkie - MVP Alumnus
Senior Content Developer - Modern Assistance and Support Experience - Microsoft Corporation

Author - Microsoft Access 2013 Inside Out
Author - Microsoft Access 2010 Inside Out
Co-author - Microsoft Office Access 2007 Inside Out
Access 2007/2010/2013 Info: http://www.AccessJunkie.com

----------
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/en-us/legal/Copyright/Default.aspx
----------

@AccessJunkie - thanks for the heads up. I hadn't noticed it was a Web App forum since I didn't respond to it from there. Not the first time I've been caught out

Oops! Like CJ I just replied from the list of new posts and didn't notice the sub forum.

Brian