Select Range

mtagliaferri

Registered User.
Local time
Today, 19:32
Joined
Jul 16, 2006
Messages
550
I need to attribute a value to records between a certain range, example
All records that the field "Date" is between 01/04/2007 and 31/03/2008 I need to return a value in a field named "Group" 2008 in my table.
Example:
12/02/2003 = 2003
15/03/2003 = 2003
15/04/2003 = 2004
20/08/2003 = 2004
17/12/2003 = 2004
20/02/2004 = 2004
01/04/2004 = 2005
 
querying between dates is done like this:
Code:
BETWEEN #1/4/2007# AND #31/3/2008#
...but only if the "date" field is of that datatype (DATE, that is)
 
Group: Year([Date])-(Month([Date])>3)


Note: Date and Group are reserved words. Better avoid using them as field names.
.
 
Thanks for your help, I think I have not gave the correct explanation, I am attaching the Database, so it might come to be more clear what I need.
on Form1 the user input dates only, once inserted the date I would like to have a result in the filed "Group" so that if the date value drops in between a range from 1st of April to the 31st of March I will have a value returned into my "Table1" I am not really interested wheather that is after update or moving to the following record!
So for a date = 15/07/2006 because this is between 01/04/2006 and 31/03/2007 the result in "Group" should be 2007 if the date is 15/07/2007 than it has to be 2008 because it is between 01/04/2007 and 31/03/2008. So it basically takes as a result the year where the range ends!
Hope I have been more clear now :)
Thank you all.
 

Attachments

The expression that I gave you works in a query. To use it on a form, you can simply replace [Date] with the text box name.

See the database.
.
 

Attachments

Users who are viewing this thread

Back
Top Bottom