records made this week

[wort0987654321]

just some quick questions

1. how do i search for records made this week
2. how do i search for records made this month
3. how..................in the last 6 months
4.how....................in the last year


thank you for any help

 

[dcx693]

First, each record must have a time/date stamp saved with it. Access (assuming you're using a Jet database) does not automatically provide this.

 

[wort0987654321]

ok

how do i do this

 

[Mile-O]

1. Between DateAdd("d", -7, Date()) And Date()
2. Between DateSerial(Year(Date()), Month(Date()), 1) And Date()
3. Between DateAdd("m", -6, Date()) And Date()
4. Between DateAdd("yyyy", -1, Date()) And Date()

N.B. In answers 3 & 4 I have responded with the current date minue either 6 or 12 months. So, for example, if you wanted the last year from today (12-Nov-2003) then that expression will give you from 12-Nov-2003 to 12-Nov-2003.

 

[dcx693]

For question (1), did you really mean the current week (meaning the calendar week of the year) or did you mean the past 7 days?

 

[wort0987654321]

i meant the last 7 days.

thanks for the help so far.

 

[wort0987654321]

where you have said i need a date time stamp on my records how do i achieve this

 

[Mile-O]

Make an extra field in your table, call it LastUpdated and, every time you save the record, update the field.

 

[wort0987654321]

i may have been a bit understood here.
i wanted the query to look up records made in the last:

1. 7 dyas
2. 31 days
3. 6 months
4. year

sorry for the confusion

 

[Mile-O]

Okay, you were told that. All you need is ONE field that is a date created field and you can use that field for all the criteria you have been given.

 

[dcx693]

You store the date/time the record was last updated and then calculate the age of the record using the formulas that Mile posted.

 

[wort0987654321]

i have made the date field,

what are the codes that i need to enter for records added in the last

1. 7 days
2. month
3. 6 months
4. year

 

[Mile-O]

Look above...you've been given the criteria already...

 

[wort0987654321]

i have entered the information that i have been given. the search for records in the last 7 days seems to work okay but the records made in the last month doesnt, i wanted the query to search for records that where made in the last 31 days, this code searches for records that where made in the month of the year that we are in at the minute.

could someone please tell me the correct coding so that i can continue with my project

 

[Mile-O]

Well, if this formula given returns the last seen days:

Between DateAdd("d", -7, Date()) And Date()

Then it shouldn't be too hard to fix it for 31 days:

Between DateAdd("d", -31, Date()) And Date()