Date Criteria

[k209310]

Is it possible to have a query collect data that is One year old or newer. Im sure it would be possible but im not too sure on how to create date criteria.

Thanks for reading and for any replies

Chris

 

[Newman]

Probably something like this:
Between DateAdd("yy",-1,Date()) and Date()

 

[k209310]

Is that to be put in to the criteria field of a query in datasheet view?

 

[Mile-O]

Not in datasheet view, but the query design view.

 

[k209310]

Sorrythats what i meant. My fault. Its just i pasted it in to the criteria and changed the date (YY) and i get an invalid proceedure call message.

Is ther anything else i should be doing?

 

[Newman]

The yy should be typed as it is (type this: yy this means that you substract a year mm would meant month and so on)

This code was based on the fact that you are looking for datas one year old or less from today (sysdate) if you want it to be from a date in a field, type this:
Between DateAdd("yy",-1,[DateField]) and [DateField]
Where [DateField] is the name of the field of the date you want as the higher.

Ex: If you want all the records that were one year old or less last xmas, you'd input "25-12-02" in the [DateField] and the criteria would then be:
Between DateAdd("yy",-1,[DateField]) and [DateField]
Meaning Add -1 (substract 1) to the year of the date I entered in [DateField] and look for the records that are between the new date and the one that I entered.

I hope that I am clear. My english is not at it's best I think.

 

[k209310]

Newman Your English is probably better than mine and im English and thanks for all of the replies.

I get what you mean from the first post. I have put it in Exactly as you said in to the criteria field on a query. The field it is referncing is set to date/Time and it is set as a short date.

When the query is run i get an invalid proceedure call error. Do you have any ideas why?

Thanks for the help so far tho.

Chris

 

[Newman]

In the french version, instead of «yy» for «year» it is «aa» for «année». I just did a fast translation. I don't know the exact name of that procedure. Maybe a single «y» would do. I've done it on my system with the «aa» and it works fine for me.
You could also try this one:
Between DateAdd("m",-12,[DateField]) and [DateField]
To retrieve 12 months instead of one year.
Else, you'll have to wait for someone else to help. Sorry!

 

[k209310]

Thanks for all your help. In case you were wondering the criteria that finally worked was

Between DateAdd("y",-1,Date()) And Date()

Only one "Y" was needed. for the year.

I probably should have seen this earlier. But then its always easier in hindsight.

Thanks again Chris