records in month a year ago?

[geoffcox]

Hello

I have a table with a date field in it and would like to be able to find those records which have a date in the same month as now, but 1 year ago.

Is it possible to have a general query which will work regardless of which actual month is the current month?

DateAdd() has been suggested but I'm new to this and am not sure how to use it!

Cheers

Geoff

 

[Galaxiom]

In the criteria box of the datefield in the Query Designer:

BETWEEN DateSerial(Year(Date())-1, Month(Date()),1) AND (DateAdd("m", 1, (DateSerial(Year(Date())-1, Month(Date()),1)) - 1)

 

[Galaxiom]

Before someone posts another common "much simpler" solution, I will get in first.

WHERE Month(datefield) = Month(Date()) AND Year(datefield) = Year(Date()) -1

While this is certainly simpler to write and will return the same results, it is much, much, more work for Access than what I posted above.

In this technique, every record in the database must be processed to extract the Month and the Year which is then compared with the Month and Year of the current Date.

In the original post the first and last date of the desired range is calculated just once and the records in this date range returned using the Index on the field. The difference is imperceptible with just a few records but grows rapidly as the number of records increases.

 

[geoffcox]

In the criteria box of the datefield in the Query Designer:

BETWEEN DateSerial(Year(Date())-1, Month(Date()),1) AND (DateAdd("m", 1, (DateSerial(Year(Date())-1, Month(Date()),1)) - 1)

Thanks for thr above - I am now trying to extend this to get those records in this month a year ago where field1 ="yes" or field2 ="yes" etc,

The sql following does not do this. How do I change it?

Cheers

Geoff

WHERE ((([0-BEST-members-web-subscriptions-and-cds].start1) Between DateSerial(Year(Date())-1,Month(Date()),1) And (DateAdd("m",1,(DateSerial(Year(Date())-1,Month(Date()),1))-1))) AND (([0-BEST-members-web-subscriptions-and-cds].btec_first_unit1)="yes")) OR ((([0-BEST-members-web-subscriptions-and-cds].btec_first_unit2)="yes"));

 

[geoffcox]

I thought the sql following would be right but it isn't !?

Geoff

WHERE ((([0-BEST-members-web-subscriptions-and-cds].start1) Between DateSerial(Year(Date())-1,Month(Date()),1) And (DateAdd("m",1,(DateSerial(Year(Date())-1,Month(Date()),1))-1))) AND ((([0-BEST-members-web-subscriptions-and-cds].btec_first_unit1)="yes")) OR (([0-BEST-members-web-subscriptions-and-cds].btec_first_unit2)="yes")));

 

[Galaxiom]

Whoa, that query (presumably) name is hideous. Does it need to be so long. Also you should avoid using the special characters such as the hyphen (subtraction operator). Most experienced developers use CamelCase.

If you must have names like that, alias them like this. Only use the real name in the From and the alias everywhere else.

SELECT somefield
FROM [0-BEST-members-web-subscriptions-and-cds] AS A
WHERE A.Start1 = whatever

In fact the aliasing is only necessary where the fieldnames are ambiguous due to a join. In a single source query you can leave out the table/query name entirely except in the From clause.

You have a text field holding a value "Yes". It is far more efficient to hold Yes/No values in a Boolean field. They are more compact to store, much faster to index and process and can be included in a query condition (in this case returning the True records) simply as:

WHERE fieldname

Use a code box when you post code on the forum.
http://www.access-programmers.co.uk/forums/showthread.php?goto=newpost&t=200247

This also allows the code to be indented because it is formatted as written rather than formatted as html which trims all whitespace to a single space. You can edit your existing post.

Then we might be able to see what is going on.

 

[geoffcox]

I have changed the table name so now have

WHERE (((best_2011.start1) Between DateSerial(Year(Date())-1,Month(Date()),1) And (DateAdd("m",1,(DateSerial(Year(Date())-1,Month(Date()),1))-1))) AND ((best_2011.btec_first_unit1)="yes")) OR (((best_2011.btec_first_unit2)="yes"));

This is wrong because the results are not restricted to the month a year ago ...

How do I correct this?

I changed to a text field yes/no because it was too easy to accidentally change the value of the other when using the table ...

Cheers

Geoff

 

[Galaxiom]

The problem is the bracketing of the conditions. It looks like the last OR stand by itself while the other test for Yes is combined with the date range.

Unfortunately Access has a penchant for brackets and you will find it easier to read if you take out most of them. Unfortunately Access will put them fight back in again when it saves the query.

 

[geoffcox]

The problem is the bracketing of the conditions. It looks like the last OR stand by itself while the other test for Yes is combined with the date range.

I did think I had the brackets as follows and Access didn't accept it - well it does now and this seems OK!

WHERE (((best_2011.start1) Between DateSerial(Year(Date())-1,Month(Date()),1) And (DateAdd("m",1,(DateSerial(Year(Date())-1,Month(Date()),1))-1))) AND ( (((best_2011.btec_first_unit1)="yes")) OR ((best_2011.btec_first_unit2)="yes")));

Thanks

Geoff

 

[Brianwarnock]

Since we were talking performance a few posts ago I would save a function call by replacing this

(DateAdd("m", 1, (DateSerial(Year(Date())-1, Month(Date()),1)) - 1)

with this

DateSerial(Year(Date())-1, Month(Date())+1,0)

Brian

 

[Galaxiom]

Thanks Brian. I definitely had a blind spot there.

For some bizarre reason I was thinking that December would break that expression because it would add to make the 13th month. Yet I have always been perfectly comfortable with the zeroth day.